mysql中更新鎖與排它鎖的示例分析

這篇文章將為大家詳細講解有關(guān)中更新鎖與排它鎖的示例分析，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

一直沒(méi)有認真了解UPDATE操作的鎖，最近在MSDN論壇上看到一個(gè)問(wèn)題，詢(xún)問(wèn)堆表更新的死鎖問(wèn)題，問(wèn)題很簡(jiǎn)單，有類(lèi)似這樣的表及數據：

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, 'b' + RIGHT(10000 + @id, 4), 'c' + RIGHT(100000 + @id, 4) );
END;

在查詢(xún)一中執行更新操作：

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;

在查詢(xún)一執行開(kāi)始后，馬上在查詢(xún)二中執行下面的操作

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

為什么會(huì )出現死鎖，如果條件改為 c1 = 4 則不會(huì )死鎖。

開(kāi)始的時(shí)候想得比較簡(jiǎn)單，死鎖的表現是形成循環(huán)等待(對于兩個(gè)查詢(xún)而言，可以簡(jiǎn)單地認為就是在相互等待對方鎖定資源的釋放)。

對于這個(gè)例子而言，第一個(gè)查詢(xún)更新兩次，會(huì )先更新并鎖定一條記錄，然后等待第二個(gè)更新；但第二個(gè)查詢(xún)只會(huì )更新一條記錄，它要么與第一個(gè)查詢(xún)沖突，無(wú)法獲得鎖，需要等待查詢(xún)一完成，這個(gè)時(shí)候它并沒(méi)有鎖定什么；要么能夠獲得鎖，完成更新。似乎不應該會(huì )出現死鎖，死鎖會(huì )不會(huì )是其他原因導致。

在自己的電腦上簡(jiǎn)單測試了一下，似乎也確實(shí)沒(méi)有死鎖。

但后面通過(guò)Profile跟蹤更新操作的下鎖情況才發(fā)現，自己的分析大錯特錯了。主要原因在于沒(méi)有正確理解更新操作是如何用鎖的。

在聯(lián)機幫助上“鎖模式”中有關(guān)于更新的U（更新鎖）和X（排它鎖）的說(shuō)明

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

不過(guò)說(shuō)得確實(shí)挺模糊的，里面還提到了S鎖，我一直以為是查詢(xún)數據過(guò)程中用的S鎖（也 SELECT 一樣），找到滿(mǎn)足條件的記錄后用U鎖，再轉換為X鎖做更新。

   Profile（事件探查器）跟蹤的結果讓我知道了這是一個(gè)錯誤的理解，在Profile中新建一個(gè)跟蹤，選擇Locks中的Lock:Acquired(加鎖)，Lock:Acquired（釋放鎖）解兩個(gè)事件，在篩選中設置只跟蹤測試用的查詢(xún)窗口對應的spid（可以執行 PRINT @@SPID獲得），然后執行一個(gè)更新語(yǔ)句，比如 UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 3

在Profile中可以看到，對于每條記錄都有加 U 鎖的操作，對于不滿(mǎn)足條件的記錄，會(huì )馬上釋放U鎖；對于滿(mǎn)足條件的記錄，最終轉換為X鎖。如下圖所示。

注意一下，在這個(gè)跟蹤結果里面，并沒(méi)有出現S鎖。

另外學(xué)做了一些測試:

1. 通過(guò)加大記錄量做更新測試，會(huì )發(fā)現數據掃描涉及的記錄都有U鎖，并不限于更新記錄所在的頁(yè)。這從另一個(gè)角度說(shuō)明了大表中Scan 可怕。

2. 當使用索引Scan的時(shí)候，也會(huì )通過(guò)跟蹤發(fā)現所Scan的索引資源有U鎖，如果更新不涉及索引變化，那以只會(huì )對應的記錄有U轉X鎖，索引的U鎖會(huì )釋放；如果影響索引，那么索引的U鎖會(huì )轉X鎖。

3. 刪除操作與更新操作類(lèi)似

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

最后回頭研究一下示例中的死鎖問(wèn)題：

• 對于查詢(xún)一，第一個(gè)更新依次掃描表中所有記錄，對于每條記錄，加 U 鎖，判斷是否符合更新條件，如果符合，轉換為 X 鎖；如果不符合條件，釋放 U 鎖。第一個(gè)更新完成的時(shí)候，查詢(xún)一鎖定了一條記錄(由于事務(wù)未完成，所以鎖一直保持)，然后等待第二個(gè)更新

• 對于查詢(xún)二，依次掃描表中的每條記錄（與前面的更新一樣），如果它更新的記錄在查詢(xún)一更新的記錄前被掃描到，那么這條記錄也會(huì )變成 X 鎖；當繼續并進(jìn)行到查詢(xún)一的X鎖記錄的零點(diǎn)，U 與 X 沖突，無(wú)法繼續，這時(shí)候查詢(xún)二等待查詢(xún)一釋放鎖

• 查詢(xún)一的第二個(gè)更新開(kāi)始執行，依次掃描每條記錄，同一個(gè)事務(wù)內不會(huì )有沖突，所以它不會(huì )與自己之前鎖定的記錄有沖突，但進(jìn)行到查詢(xún)二鎖定的記錄的時(shí)候，它也無(wú)法獲得 U 鎖，它需要等待查詢(xún)二釋放資源。這個(gè)時(shí)候就形成了相互等待，符合死鎖條件

• 如果查詢(xún)二需要更新的記錄在查詢(xún)一的第一個(gè)更新記錄之后，則不會(huì )有死鎖，因為查詢(xún)二在掃描到查詢(xún)一第一個(gè)更新的記錄時(shí)就會(huì )因為鎖沖突等待了，這個(gè)時(shí)候它沒(méi)有對任何記錄設置與查詢(xún)一的操作有沖突的鎖。我自己測試的時(shí)候沒(méi)有死鎖，就是這種情況。

  注意這里面提到的順序，是數據讀取的順序，不一定與存儲順序一樣，磁盤(pán)上記錄的順序也不一定與INSERT的記錄順序一樣，這也是我用同樣條件沒(méi)有測試出死鎖的原因（我的環(huán)境中，恰好讀出的順序與INSERT的順序不一樣）

更新時(shí)，記錄讀取的順序，可以通過(guò)Profile跟蹤的Lock:Acquired (加鎖)事件來(lái)看，涉及大量數據時(shí)，如果支持，還會(huì )有并發(fā)讀取。這也是分析死鎖時(shí)要考慮的因素