講解更新鎖（U）與排它鎖（X）的相關知識

一直沒有認真了解UPDATE操作的鎖，最近在MSDN論壇#上看到一個問題，詢問堆表更新的死鎖問題，問題很簡單，有類似這樣的表及資料：

CREATE TABLE dbo.tb(
     c1 int,
     c2 char(10),
     c3 varchar(10)
);
GO
DECLARE @id int;
SET @id = 0;
WHILE @id <5
BEGIN;
     SET @id = @id + 1;
     INSERT dbo.tb VALUES( @id, &#39;b&#39; + RIGHT(10000 + @id, 4), &#39;c&#39; + RIGHT(100000 + @id, 4) );
END;

在查詢一執行更新操作：

BEGIN TRAN
UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 2;
WAITFOR DELAY '00:00:30';
UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 5;
ROLLBACK;

在查詢一執行開始後，馬上在查詢二中執行下面的動作

#

BEGIN TRAN
UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 1;
ROLLBACK;

為什麼會出現死鎖，如果條件改為c1 = 4 則不會死鎖。

開始的時候想得比較簡單，死鎖的表現是形成循環等待(對於兩個查詢而言，可以簡單地認為就是在相互等待對方鎖定資源的釋放)。

對於這個範例而言，第一個查詢更新兩次，會先更新並鎖定一筆記錄，然後等待第二個更新；但第二個查詢只會更新一筆記錄，它要麼與第一個查詢衝突，無法取得鎖，需要等待查詢一完成，這個時候它並沒有鎖定什麼；要麼能夠獲得鎖，完成更新。似乎不應該會出現死鎖，死鎖會不會是其他原因導致。

在自己的電腦上簡單測試了一下，似乎也確實沒有死鎖。

但後面透過Profile追蹤更新操作的下鎖情況才發現，自己的分析大錯特錯了。主要原因在於沒有正確理解更新操作是如何用鎖的。

在線上說明上「鎖定模式」中有關於更新的U（更新鎖定）和X（排它鎖定）的說明

http://msdn.microsoft.com/zh-cn /library/ms175519(v=sql.105).aspx

不過說得確實挺模糊的，裡面還提到了S鎖，我一直以為是查詢資料過程中使用的S鎖（也是SELECT 一樣），找到符合條件的記錄後用U鎖，再轉換成X鎖做更新。

#

    Profile（事件探查器）追蹤的結果讓我知道了這是一個錯誤的理解，在Profile中新建一個跟踪，選擇Locks中的Lock:Acquired(加上鎖定)，Lock:Acquired#（釋放鎖定）解兩個事件，在篩選中設定只追蹤測試用的查詢視窗對應的spid（可以執行PRINT @@SPID取得），然後執行一個更新語句，例如UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 3

#在Profile中可以看到，每個記錄都有加U鎖的操作，對於不符合條件的記錄，會馬上釋放U鎖；對於滿足條件的記錄，最終轉換為X鎖。如下圖所示。

#注意一下，在這個追蹤結果裡面，並沒有出現S鎖。

另外學做了一些測試:

#透過加大記錄量做更新測試，會發現資料掃描所涉及的記錄都有U鎖，不限於更新記錄所在的頁。這從另一個角度說明了大表中Scan 可怕。
當使用索引Scan的時候，也會透過追蹤發現所Scan的索引資源有U鎖，如果更新不涉及索引變化，那就只會對應的記錄有U轉X鎖，索引的U鎖會釋放；如果影響索引，那麼索引的U鎖會轉X鎖。

3. ###刪除操作與更新操作類似#######

4. 使用UPDATE aSET c2 = 'xx' FROM dbo.tb AS# a WITH(NOLOCK) WHERE c1 = 3  的加鎖情況是一樣的， 並不會因為NOLOCK的提示而不加U 或X 鎖定

最後回頭研究範例中的死鎖問題：

• 對於查詢一，第一個更新依序掃描表中所有記錄，對於每筆記錄，加U 鎖，判斷是否符合更新條件，如果符合，轉換為X 鎖；如果不符合條件，釋放U 鎖。第一個更新完成的時候，查詢一鎖定了一筆記錄(由於交易未完成，所以鎖一直保持)，然後等待第二個更新

• 對於查詢二，依序掃描表中的每筆記錄（與前面的更新一樣），如果它更新的記錄在查詢一更新的記錄前被掃描到，那麼這條記錄也會變成X 鎖；當繼續並進行到查詢一的X鎖記錄的零點，U 與X 衝突，無法繼續，這時候查詢二等待查詢一釋放鎖

• 查詢一的第二個更新開始執行，依序掃描每筆記錄，同一個事務內不會有衝突，所以它不會與自己之前鎖定的記錄有衝突，但進行到查詢二鎖定的記錄的時候，它也無法獲得U鎖，它需要等待查詢二釋放資源。這時候就形成了相互等待，符合死鎖條件

• 如果查詢二需要更新的記錄在查詢一的第一個更新記錄之後，則不會有死鎖，因為查詢二在掃描到查詢一第一個更新的記錄時就會因為鎖衝突等待了，這個時候它沒有對任何記錄設定與查詢一的操作有衝突的鎖。我自己測試的時候沒有死鎖，就是這種情況。

  注意這裡面提到的順序，是資料讀取的順序，不一定與儲存順序一樣，磁碟上記錄的順序也不一定與INSERT的記錄順序一樣，這也是我用同樣條件沒有測試出死鎖的原因（我的環境中，剛好讀出的順序與INSERT的順序不一樣）

更新時，記錄讀取的順序，可以透過Profile追蹤的Lock:Acquired (加鎖)事件來看，涉及大量資料時，如果伺服器支持，還會有並發讀取。這也是分析死鎖時要考慮的因素

本文講解了#講解更新鎖（U）與排它鎖（X）的相關知識，更多相關內容請留意php中文網。

相關推薦：

SQL Server 2008 處理隱含資料型別轉換在執​​行計畫中的增強功能

如何讓MySQL中單句實作無限層次父子關係查詢

有進度的SQL Server FileStream如何存取



#

以上是講解更新鎖（U）與排它鎖（X）的相關知識的詳細內容。更多資訊請關注PHP中文網其他相關文章！