深入分析MSSQL數(shù)據(jù)庫中事務隔離級別和鎖機制

1、鎖機制NOLOCK 和 READPAST 的區(qū)別。1. 開啟一個事務執(zhí)行插入數(shù)據(jù)的操作。?12345 BEGIN TRAN t INSERT INTO Customer SELECT 'a','a'2. 執(zhí)行一條查詢語句。?1 SELECT * FROM Customer WITH (NOLOCK)結果中顯示” a”和” a”。當1中事務回滾后，那么 a將成為臟數(shù)據(jù)。(注:1中的事務未 提交)。NOLOCK表明沒有對數(shù)據(jù)表添加共享鎖以阻止其它事務對數(shù)據(jù)表數(shù)據(jù)的修改。?1 SELECT * FROM Customer這條語句將一直死鎖，直到排他鎖解除或者鎖超時為止

2、。(注：設置鎖超時SETLOCK_TIMEOUT 1800)?1 SELECT * FROM Customer WITH (READPAST)這條語句將顯示a未提交前的狀態(tài)，但不鎖定整個表。這個提示指明數(shù)據(jù)庫引擎返回結 果時忽略加鎖的行或數(shù)據(jù)頁。3. 執(zhí)行一條插入語句。?1234567 BEGIN TRAN t INSERT INTO Customer SELECT 'b','b' COMMIT TRAN t這個時候，即使步驟 1的事務回滾，那么 a這條數(shù)據(jù)將丟失，而 b繼續(xù)插入數(shù)據(jù)庫中。NOLOCK1. 執(zhí)行如下語句。?1234567 BEGIN TRAN t

3、tt SELECT * FROM Customer WITH (NOLOCK) WAITFOR delay '00:00:20' COMMIT TRAN ttt注：NOLOCK不加任何鎖，可以增刪查改而不鎖定。?1234567 INSERT INTO Customer SELECT 'a','b'-不鎖定 DELETE Customer where ID=1 -不鎖定 SELECT * FROM Customer -不鎖定 UPDATE Customer SET Title='aa' WHERE ID=1 -不鎖定ROWLOCK1

4、. 執(zhí)行一條帶行鎖的查詢語句。?123456789 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -(必須) BEGIN TRAN ttt SELECT * FROM Customer WITH (ROWLOCK) WHERE ID=17 WAITFOR delay '00:00:20' COMMIT TRAN ttt注：在刪除和更新正在查詢的數(shù)據(jù)時，會鎖定數(shù)據(jù)。對其他未查詢的行和增加，查詢數(shù)據(jù)無影響。INSERT INTO Customer SELECT 'a','b'-不等待 DELETE C

5、ustomer where ID=17 -等待 DELETE Customer where ID&It;>17-不等待 SELECT * FROMCustomer 不等待 UPDATE Customer SET Title='aa' WHERE ID=17 等待 UPDATE Customer SET Title='aa' WHERE ID&It ;& gt;17 -不等待HOLDLOCK , TABLOCK 和 TABLOCKX1. 執(zhí)行 HOLDLOCK?1234567 BEGIN TRAN ttt SELECT * F

6、ROM Customer WITH (HOLDLOCK) WAITFOR delay '00:00:10' COMMIT TRAN ttt注：其他事務可以讀取表，但不能更新刪除update Customer set Title='aa'要等待 10 秒中。SELECT * FROM Customer 不需要等待2. 執(zhí)行 TABLOCKX?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (TABLOCKX) WAITFOR delay '00:00:10' COMMIT TRAN ttt注：其

7、他事務不能讀取表，更新和刪除update Customer set Title='aa'要等待 10 秒中。SELECT * FROM Customer 要等待 10 秒中。3. 執(zhí)行 TABLOCK?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (TABLOCK) WAITFOR delay '00:00:10' COMMIT TRAN ttt注：其他事務可以讀取表，但不能更新刪除update Customer set Title='aa'要等待 10 秒中。SELECT * FROM C

8、ustomer 不需要等待 UDPLOCK1. 在A連接中執(zhí)行。?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (UPDLOCK) WAITFOR delay '00:00:10' COMMIT TRAN ttt2. 在其他連接中執(zhí)行。update Customer set Title='aa' where ID=1 要等 10 秒SELECT * FROM Customer -不用等insert into Customer select 'a','b'-不用等注：對于UDP

9、LOCK鎖，只對更新數(shù)據(jù)鎖定。注:使用這些選項將使系統(tǒng)忽略原先在SET語句設定的事務隔離級別(SET TransactionIsolation Level)。事務隔離級別臟讀：READ UNCOMMITTED臟讀就是指當一個事務正在訪問數(shù)據(jù)， 并且對數(shù)據(jù)進行了修改，而這種修改還沒有提交 到數(shù)據(jù)庫中，這時，另外一個事務也訪問這個數(shù)據(jù)，然后使用了這個數(shù)據(jù)。因為這個數(shù)據(jù)是還沒有提交的數(shù)據(jù)，那么另外一個事務讀到的這個數(shù)據(jù)是臟數(shù)據(jù)，依據(jù)臟數(shù)據(jù)所做的操作可能是不正確的。1. 在A連接中執(zhí)行。?123456789 BEGIN TRAN t INSERT INTO Customer SELECT '1

10、23','123' WAITFOR delay '00:00:20' COMMIT TRAN t2. 在B連接中執(zhí)行。?123 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM Customer這個時候，未提交的數(shù)據(jù)會123'會顯示出來，當 A事務回滾時就導致了臟數(shù)據(jù)。相當 于(NOLOCK)提交讀：READ COMMITTED1. 在A連接中執(zhí)行。?123456789 BEGIN TRAN t INSERT INTO Customer SELECT '123&#

11、39;,'123' WAITFOR delay '00:00:20' COMMIT TRAN t2. 在B連接中執(zhí)行。?123 SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROMCustomer這個時候，未提交的數(shù)據(jù)會123'不會顯示出來，當 A事務提交以后 B中才能讀取到數(shù) 據(jù)。避免了臟讀。不可重復讀：REPEATABLE READ不可重復讀是指在一個事務內，多次讀同一數(shù)據(jù)。在這個事務還沒有結束時，另外一個事務也訪問該同一數(shù)據(jù)。那么，在第一個事務中的兩次讀數(shù)據(jù)之間，由于第二個事務的修

12、改，那么第一個事務兩次讀到的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個事務內兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復讀。例如：1. 在A連接中執(zhí)行如下語句。SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN ttt SELECT * FROM Customer WHERE ID=17 WAITFOR delay '00:00:30' SELECT * FROM Customer WHERE ID=17 COMMIT TRAN ttt2. 在B連接中執(zhí)行如下語句，而且要在第一個事物的三十秒等待內。UPDATE Cu

13、stomer SET Title='d' WHERE ID=17這個時候，此連接將鎖住不能執(zhí)行，一直等到A連接結束為止。而且 A連接中兩次讀取到的數(shù)據(jù)相同，不受 B連接干擾。注，對于Read Committed和Read Un Committed情況下，B連接不會鎖住， 等到A連接 執(zhí)行完以后，兩條查詢語句結果不同，即第二條查詢的Title變成了 d。序列化讀：SERIALIZABLE1. 在A連接中執(zhí)行。?123456789 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN tUPDATE Customer SET

14、 Title='111' WAITFOR delay '00:00:20' COMMIT TRAN t2. 在B連接中執(zhí)行，并且要在 A執(zhí)行后的20秒內。?1234567 BEGIN TRAN tt INSERT INTO Customer SELECT '2','2' COMMIT TRAN tt在A連接的事務提交之前，B連接無法插入數(shù)據(jù)到表中，這就避免了幻覺讀。注：幻覺讀是指當事務不是獨立執(zhí)行時發(fā)生的一種現(xiàn)象，例如第一個事務對一個表中的數(shù)據(jù)進行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時，第二個事務也修改這個表中的數(shù)據(jù)，這種修

15、改是向表中插入一行新數(shù)據(jù)。那么，以后就會發(fā)生操作第一個事務的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好像發(fā)生了幻覺一樣。共享鎖共享鎖（S鎖）允許并發(fā)事務在封閉式并發(fā)控制（請參閱并發(fā)控制的類型）下讀?。⊿ELECT）資源。資源上存在共享鎖（S鎖）時，任何其他事務都不能修改數(shù)據(jù)。讀取操作 一完成，就立即釋放資源上的共享鎖（ S鎖），除非將事務隔離級別設置為可重復讀或更高 級別，或者在事務持續(xù)時間內用鎖定提示保留共享鎖（S鎖）。更新鎖更新鎖（U鎖）可以防止常見的死鎖。在可重復讀或可序列化事務中，此事務讀取數(shù)據(jù)獲取資源（頁或行）的共享鎖（S鎖），然后修改數(shù)據(jù) 此操作要求鎖轉換為排他鎖（X鎖）。如果兩個事務獲得了資源上的共享模式鎖，然后試圖同時更新數(shù)據(jù)，則一個事 務嘗試將鎖轉換為排他鎖（X鎖）。共享模式到排他鎖的轉換必須等待一段時間，因為一個 事務的排他鎖與其他事務的共享模式鎖不兼容；發(fā)生鎖等待。第二個事務試圖獲取排他鎖（X鎖）以進行更新。由于兩個事務都要轉換為排他鎖（ X鎖），并且每個事務都等待另一個事 務釋放共享模式鎖，因此發(fā)生死鎖。若要避免這種潛在的死鎖問題，請使用更新鎖（U鎖）。一次只有一個事務可以獲得資源的更新鎖（U鎖）。如果事務修改資源，則更新鎖（ U鎖）轉換為排他鎖（X鎖）。 排他鎖排他鎖（X鎖）可以防止并發(fā)事務對資源