標籤:
在上一篇中忘記了一個細節。Range T-K 到底代表了什嗎?Range T-K Lock 代表了在 SERIALIZABLE 隔離等級中,為了保護範圍內的資料不被並發的事務影響而使用的一類鎖模式(避免幻讀)。它由兩個部分構成:
第一個部分代表了他鎖定了一個索引範圍,在這個範圍內,所有索引使用 T 鎖進行鎖定;
第二個部分是而這個範圍內已經命中的Key,這些 Key 將使用 K 鎖進行鎖定。
合并在一起我們說在這個範圍內,索引範圍和特定的row的鎖定模式為 Range T-K。
舉上一篇的一個例子吧:
SELECT [data] FROM [MyTable] WHERE [index_column]>=20 AND [index_column]<=40
的鎖的使用方式是:
實際上,上述語句產生的鎖有兩個部分,第一個是 Range S 鎖,範圍是 20-40 的索引範圍,第二是 Key 上使用的 S 鎖,在圖中可以看到有三個 Key 被命中了,分別是“無限遠”,“25”對應的索引以及“30”對應的索引。其 Mode 為 Range S-S,其 Type 為 KEY,也就是,他們的範圍鎖為 Range S,Key 鎖為 S 鎖。
更新和插入操作涉及的鎖
涉及的鎖主要是兩種,一種是 Range S-U 鎖,另一種是 Range X-X 鎖。
Range S-U,這個選定索引範圍會獲得 S 鎖而命中的 Key 使用 U 鎖鎖定,以便將來轉換為 X 鎖。而在更新時,則徹底成為 X 鎖,這個範圍內的鎖模式也就成了 Range X-X。由於更新的資料列不同(有可能是索引列,有可能不是),使用的索引也不同(聚集,非聚集,唯一,等),因此其情況就不容易像 Range S-S 鎖那麼容易得出規律了。總的來說有幾種情況還是一致的,這裡就不再逐個實驗了(這裡強烈推薦閱讀 SQL Server 2008 Internals 這本書關於鎖的章節,講述的很清楚):
首先,在相等判斷(例如“=”),且索引為唯一索引的情況下。如果該索引命中,不會有 Range T-K 鎖鎖定記錄範圍,而相應的記錄直接獲得 U 鎖或者 X 鎖;
其次,在相等判斷,不論索引是否為唯一索引,如果該索引沒有命中記錄,則 Range T-K 鎖鎖定 “下一個”記錄。(關於“下一個”的解釋請參見上一篇);
第三,在範圍條件(>、<、BETWEEN),不論索引是否唯一,如果該索引命中,不但該範圍會獲得 Range T-K 鎖,而該範圍的“下一個”記錄也會獲得 Range T-K 鎖。
為什麼 Serializable 隔離等級更容易死結
我們從第一篇的圖可以看到,SERIALIZABLE 層級能夠保證最嚴格的資料一致性,但是這些保衛的手段只要稍稍變化就可以發展為死結。事實上,在各種隔離等級中,資料一致性越高,則越容易發生死結;資料一致性越低,則發生死結的機率就越小。
在這些隔離等級中,SERIALIZABLE 是最容易死結的,這得益於 Range T-K 鎖使鎖定的範圍不僅僅限於現有資料,還有未來資料;不僅僅限定現有的若干資料頁,而是一個廣大的範圍。
這其中,最恐怖的問題莫過於“下一個”資料的鎖定。這非常容易造成大範圍死結。我們以第一篇的例子來說明:
1234567891011121314 |
SELECT @findCount= COUNT (id) FROM MyTable WHERE [fk_related_id][email protected] IF (@findCount > 0) BEGIN ROLLBACK TRANSACTION RETURN ERROR_CODE END INSERT INTO MyTable ([fk_related_id],…) VALUES (@Argument,…) COMMIT TRANSACTION RETURN SUCCESS_CODE |
在這個例子中,表 MyTable 的列 fk_related_id 是一個唯一索引(非聚集),交易隔離等級為 SERIALIZABLE。不同的預存程序執行會傳入不同的 @Argument,表面看來,這不會有任何的問題,但是由於“下一個”資料的鎖定,在稍高水平的並發上,就出現了大約 80% 的失敗情況,這些失敗都來源於死結。我們挑選了其中的一次:
我們試圖以每秒鐘 15 個的壓力在 @Argument 屬於 [1, 1000] 的範圍內進行預存程序調用。在這個過程中,有一個 @Argument 為 115 的記錄首先成功的插入了進去!
id |
fk_related_id |
data |
1 |
115 |
… |
接下來有一個 @Argument 為 74 的記錄獲得了機會,我們假設它的 Session Id 為 A。它執行了 SELECT 語句:
id |
fk_related_id |
data |
1 |
115 (A 獲得了Range S-S Lock) |
… |
接下來有一個 @Argument 為 4 的記錄獲得了機會,我們假設它的 Session Id 為 B。它執行了 SELECT 語句:
id |
fk_related_id |
data |
|
115 (A 、B獲得了Range S-S Lock) |
… |
接下來,Session A 執行到了 INSERT 語句,那麼 Range S-S 鎖會試圖進行一個轉換測試(Range I-N 鎖),但這顯然是行不通的,因為 Session B 也獲得了 Range S-S Lock,因此 Session A 陷入了等待;
而 Session B 也執行到了 INSERT 語句,相同的,它也陷入了等待;這樣,Session A 等待 Session B 放棄 Range 鎖,Session B 等待 Session A 放棄鎖,這是一個死結了。
而更糟糕的事情是,凡是 @Argument 小於 115 的記錄,他都會試圖令下一個記錄獲得新的 Range S-S 鎖,從而進入無限的等待中,至少,1-115 號記錄死結,並且最終 114 個需要放棄,1個成功。這就是為什麼 SERIALIZABLE 隔離等級不但會發生死結,而且在某些時候,是大面積死結。
總之:在 SERIALIZABLE 隔離等級下,只要有類似同一索引為條件先讀後寫的狀況的,在較大並發下發生死結的機率很高,而且如果碰巧既有的記錄索引按照定序在非常靠後的位置,則很可能發生大面積死結。
那麼如何解決這個問題呢,呃,降低隔離等級當然是一個方法,例如,如果你能接受幻讀,那麼 REPEATABLE READ 是一個不錯的選擇。但是我突然在某篇部落格中看到了使用 SELECT WITH UPDLOCK 的方法。事實上,這種東西讓死結更容易了。
例如,一個預存程序 SELECT B,而後 SELECT A;而另外的預存程序先 SELECT A,再 SELECT B,那麼由於順序不同,獨佔鎖定僅僅是 Read 的情況就可能發生死結了。
那麼為什麼 REPEATABLE READ 會好得多呢?因為 REPEATABLE READ 緊緊鎖定現有記錄,而不會使用 Range 鎖。我們仍然以上述預存程序為例,這樣,只有兩個被鎖定的行資料在同一個頁上(因為預設情況下使用頁級鎖),或者說挨得足夠近,才有可能死結,並且這個死結僅僅限於這個資料頁上的記錄而不會影響其他記錄,因此死結的機率大大降低了。
我們實際測試中,在相同的測試條件下,並發提高到 100 的情況下時才有不到 0.1% 的死結失敗幾率。當然我們付出了允許幻讀的代價。
(轉)SQL Server 中的事務和鎖(三)-Range S-U,X-X 以及死結