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

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

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

3、H (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=aaWHERE ID=1不鎖定ROWLOCK1. 執(zhí)行一條帶行鎖的查詢語句。?123456789 SET TRANSACTION ISOLATION LEVEL REPEA TABLE RE

4、AD - (必須 ) BEGIN TRAN ttt SELECT * FROM Customer WITH (ROWLOCK) WHERE ID=17 WAITFORdelay 00:00:20 COMMIT TRAN ttt注：在刪除和更新正在查詢的數(shù)據(jù)時，會鎖定數(shù)據(jù)。 對其他未查詢的行和增加，查詢數(shù)據(jù)無影響。?1234567891011 INSERT INTOCustomer SELECT a,b 不等待DELETECustomerwhere ID=17等待DELETECustomer where ID<>17不等待SELECT * FROMCustomer 不等待UPDATE

5、Customer SET Title=aaWHEREID=17 等待UPDATECustomer SET Title=aa WHERE ID<>17不等待HOLDLOCK ，TABLOCK和 TABLOCKX1. 執(zhí)行 HOLDLOCK?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (HOLDLOCK) WAITFOR delay 00:00:10 COMMIT TRAN ttt注：其他事務(wù)可以讀取表，但不能更新刪除update Customer set Title=aa 要等待10 秒中。SELECT * FROM Cust

6、omer不需要等待2. 執(zhí)行 TABLOCKX?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (TABLOCKX) WAITFOR delay 00:00:10 COMMIT TRAN ttt注：其他事務(wù)不能讀取表,更新和刪除update Customer set Title=aa 要等待10 秒中。SELECT * FROM Customer要等待10 秒中。3. 執(zhí)行 TABLOCK?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (TABLOCK) WAITFOR delay 0

7、0:00:10 COMMIT TRAN ttt注：其他事務(wù)可以讀取表，但不能更新刪除update Customer set Title=aa 要等待10 秒中。SELECT * FROM Customer不需要等待UDPLOCK1. 在 A 連接中執(zhí)行。?1234567 BEGIN TRAN ttt SELECT* FROM Customer WITH (UPDLOCK) WAITFORdelay 00:00:10 COMMIT TRAN ttt2. 在其他連接中執(zhí)行。update Customer set Title=aa where ID=1 要等 10 秒SELECT * FROM Cu

8、stomer不用等insert into Customer select a,b 不用等注：對于 UDPLOCK鎖，只對更新數(shù)據(jù)鎖定。注：使用這些選項將使系統(tǒng)忽略原先在SET 語句設(shè)定的事務(wù)隔離級別（ SET TransactionIsolation Level) 。事務(wù)隔離級別臟讀： READ UNCOMMITTED臟讀就是指當(dāng)一個事務(wù)正在訪問數(shù)據(jù)，并且對數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫中，這時，另外一個事務(wù)也訪問這個數(shù)據(jù)，然后使用了這個數(shù)據(jù)。因為這個數(shù)據(jù)是還沒有提交的數(shù)據(jù)， 那么另外一個事務(wù)讀到的這個數(shù)據(jù)是臟數(shù)據(jù)， 依據(jù)臟數(shù)據(jù)所做的操作可能是不正確的。1. 在 A 連接中執(zhí)行。

9、?123456789 BEGIN TRAN t INSERT INTO Customer SELECT 123,123 WAITFOR delay00:00:20 COMMIT TRAN t2. 在 B 連接中執(zhí)行。?123 SET TRANSACTIONISOLATIONLEVELREADUNCOMMITTEDSELECT*FROM Customer這個時候，未提交的數(shù)據(jù)會123會顯示出來，當(dāng)A 事務(wù)回滾時就導(dǎo)致了臟數(shù)據(jù)。相當(dāng)于(NOLOCK)提交讀： READ COMMITTED1. 在 A 連接中執(zhí)行。?123456789 BEGIN TRAN t INSERT INTO Custome

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

11、次讀到的數(shù)據(jù)可能是不一樣的。 這樣就發(fā)生了在一個事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復(fù)讀。例如：1. 在 A 連接中執(zhí)行如下語句。?1234567891011 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í)行如下語句，而且要在第一個事物的三十秒等待內(nèi)。UPDATE Cu

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

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

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