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

鎖機(jī)制
NOLOCK和READPAST的區(qū)別。

1.       開啟一個(gè)事務(wù)執(zhí)行插入數(shù)據(jù)的操作。

BEGIN TRAN t

INSERT INTO Customer

SELECT 'a','a'

2.       執(zhí)行一條查詢語(yǔ)句。

SELECT * FROM Customer WITH (NOLOCK)

結(jié)果中顯示”a”和”a”。當(dāng)1中事務(wù)回滾后，那么a將成為臟數(shù)據(jù)。(注:1中的事務(wù)未提交) 。NOLOCK表明沒有對(duì)數(shù)據(jù)表添加共享鎖以阻止其它事務(wù)對(duì)數(shù)據(jù)表數(shù)據(jù)的修改。

SELECT * FROM Customer

這條語(yǔ)句將一直死鎖，直到排他鎖解除或者鎖超時(shí)為止。(注:設(shè)置鎖超時(shí)SET LOCK_TIMEOUT 1800)

SELECT * FROM Customer WITH (READPAST)

這條語(yǔ)句將顯示a未提交前的狀態(tài)，但不鎖定整個(gè)表。這個(gè)提示指明數(shù)據(jù)庫(kù)引擎返回結(jié)果時(shí)忽略加鎖的行或數(shù)據(jù)頁(yè)。

3.       執(zhí)行一條插入語(yǔ)句。

BEGIN TRAN t

INSERT INTO Customer

SELECT 'b','b'

COMMIT TRAN t

這個(gè)時(shí)候，即使步驟1的事務(wù)回滾，那么a這條數(shù)據(jù)將丟失，而b繼續(xù)插入數(shù)據(jù)庫(kù)中。

NOLOCK

1． 執(zhí)行如下語(yǔ)句。

BEGIN TRAN ttt

SELECT * FROM Customer WITH (NOLOCK)

WAITFOR delay '00:00:20'

COMMIT TRAN ttt

注：NOLOCK不加任何鎖，可以增刪查改而不鎖定。

INSERT INTO Customer SELECT 'a','b' –不鎖定

DELETE Customer where ID=1 –不鎖定

SELECT * FROM Customer –不鎖定

UPDATE Customer SET Title='aa' WHERE ID=1 –不鎖定

ROWLOCK

1.       執(zhí)行一條帶行鎖的查詢語(yǔ)句。

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í)，會(huì)鎖定數(shù)據(jù)。對(duì)其他未查詢的行和增加，查詢數(shù)據(jù)無(wú)影響。

INSERT INTO Customer SELECT 'a','b' –不等待

DELETE Customer where ID=17 –等待

DELETE Customer where ID>17 –不等待

SELECT * FROM Customer –不等待

UPDATE Customer SET Title='aa' WHERE ID=17–等待

UPDATE Customer SET Title='aa' WHERE ID>17–不等待

HOLDLOCK，TABLOCK和TABLOCKX

1.       執(zhí)行HOLDLOCK

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 Customer —不需要等待

2.       執(zhí)行TABLOCKX

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

BEGIN TRAN ttt

SELECT * FROM Customer WITH (TABLOCK)

WAITFOR delay '00:00:10'

COMMIT TRAN ttt

注：其他事務(wù)可以讀取表，但不能更新刪除 

update Customer set Title='aa' —要等待10秒中。

SELECT * FROM Customer —不需要等待

UDPLOCK

1.       在A連接中執(zhí)行。

BEGIN TRAN ttt

SELECT * FROM Customer WITH (UPDLOCK)

WAITFOR delay '00:00:10'

COMMIT TRAN ttt

2.       在其他連接中執(zhí)行。

update Customer set Title='aa' where ID=1—要等10秒

SELECT * FROM Customer –不用等

insert into Customer select 'a','b'–不用等

注：對(duì)于UDPLOCK鎖，只對(duì)更新數(shù)據(jù)鎖定。

注：使用這些選項(xiàng)將使系統(tǒng)忽略原先在SET語(yǔ)句設(shè)定的事務(wù)隔離級(jí)別（SET Transaction Isolation Level)。

事務(wù)隔離級(jí)別

臟讀：READ UNCOMMITTED

臟讀就是指當(dāng)一個(gè)事務(wù)正在訪問(wèn)數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫(kù)中，這時(shí)，另外一個(gè)事務(wù)也訪問(wèn)這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。因?yàn)檫@個(gè)數(shù)據(jù)是還沒有提交的數(shù)據(jù)，那么另外一個(gè)事務(wù)讀到的這個(gè)數(shù)據(jù)是臟數(shù)據(jù)，依據(jù)臟數(shù)據(jù)所做的操作可能是不正確的。

1.       在A連接中執(zhí)行。

BEGIN TRAN t

INSERT INTO Customer

SELECT '123','123'

WAITFOR delay '00:00:20'

COMMIT TRAN t

2.       在B連接中執(zhí)行。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM Customer

這個(gè)時(shí)候，未提交的數(shù)據(jù)會(huì)'123'會(huì)顯示出來(lái)，當(dāng)A事務(wù)回滾時(shí)就導(dǎo)致了臟數(shù)據(jù)。相當(dāng)于(NOLOCK)

提交讀：READ COMMITTED

1.       在A連接中執(zhí)行。

BEGIN TRAN t

INSERT INTO Customer

SELECT '123','123'

WAITFOR delay '00:00:20'

COMMIT TRAN t

2.       在B連接中執(zhí)行。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM Customer

這個(gè)時(shí)候，未提交的數(shù)據(jù)會(huì)'123'不會(huì)顯示出來(lái)，當(dāng)A事務(wù)提交以后B中才能讀取到數(shù)據(jù)。避免了臟讀。

不可重復(fù)讀：REPEATABLE READ

不可重復(fù)讀是指在一個(gè)事務(wù)內(nèi)，多次讀同一數(shù)據(jù)。在這個(gè)事務(wù)還沒有結(jié)束時(shí)，另外一個(gè)事務(wù)也訪問(wèn)該同一數(shù)據(jù)。那么，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改，那么第一個(gè)事務(wù)兩次讀到的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復(fù)讀。

例如：

1.       在A連接中執(zhí)行如下語(yǔ)句。

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 ttt

2.       在B連接中執(zhí)行如下語(yǔ)句，而且要在第一個(gè)事物的三十秒等待內(nèi)。

UPDATE Customer SET Title='d' WHERE ID=17

這個(gè)時(shí)候，此連接將鎖住不能執(zhí)行，一直等到A連接結(jié)束為止。而且A連接中兩次讀取到的數(shù)據(jù)相同，不受B連接干擾。

注，對(duì)于Read Committed和Read UnCommitted情況下，B連接不會(huì)鎖住，等到A連接執(zhí)行完以后，兩條查詢語(yǔ)句結(jié)果不同，即第二條查詢的Title變成了d。

序列化讀：SERIALIZABLE

1.       在A連接中執(zhí)行。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN t

UPDATE Customer SET Title='111'

WAITFOR delay '00:00:20'

COMMIT TRAN t

2. 在B連接中執(zhí)行，并且要在A執(zhí)行后的20秒內(nèi)。

BEGIN TRAN tt

INSERT INTO Customer

SELECT '2','2'

COMMIT TRAN tt

在A連接的事務(wù)提交之前，B連接無(wú)法插入數(shù)據(jù)到表中，這就避免了幻覺讀。

注：幻覺讀是指當(dāng)事務(wù)不是獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象，例如 第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時(shí)，第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，以后就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好像發(fā)生了幻覺一樣。

共享鎖

共享鎖（S 鎖）允許并發(fā)事務(wù)在封閉式并發(fā)控制（請(qǐng)參閱并發(fā)控制的類型）下讀取 (SELECT) 資源。資源上存在共享鎖（S 鎖）時(shí)，任何其他事務(wù)都不能修改數(shù)據(jù)。讀取操作一完成，就立即釋放資源上的共享鎖（S 鎖），除非將事務(wù)隔離級(jí)別設(shè)置為可重復(fù)讀或更高級(jí)別，或者在事務(wù)持續(xù)時(shí)間內(nèi)用鎖定提示保留共享鎖（S 鎖）。

更新鎖

更新鎖（U 鎖）可以防止常見的死鎖。在可重復(fù)讀或可序列化事務(wù)中，此事務(wù)讀取數(shù)據(jù) [獲取資源（頁(yè)或行）的共享鎖（S 鎖）]，然后修改數(shù)據(jù) [此操作要求鎖轉(zhuǎn)換為排他鎖（X 鎖）]。如果兩個(gè)事務(wù)獲得了資源上的共享模式鎖，然后試圖同時(shí)更新數(shù)據(jù)，則一個(gè)事務(wù)嘗試將鎖轉(zhuǎn)換為排他鎖（X 鎖）。共享模式到排他鎖的轉(zhuǎn)換必須等待一段時(shí)間，因?yàn)橐粋€(gè)事務(wù)的排他鎖與其他事務(wù)的共享模式鎖不兼容；發(fā)生鎖等待。第二個(gè)事務(wù)試圖獲取排他鎖（X 鎖）以進(jìn)行更新。由于兩個(gè)事務(wù)都要轉(zhuǎn)換為排他鎖（X 鎖），并且每個(gè)事務(wù)都等待另一個(gè)事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。

若要避免這種潛在的死鎖問(wèn)題，請(qǐng)使用更新鎖（U 鎖）。一次只有一個(gè)事務(wù)可以獲得資源的更新鎖（U 鎖）。如果事務(wù)修改資源，則更新鎖（U 鎖）轉(zhuǎn)換為排他鎖（X 鎖）。

排他鎖

排他鎖（X 鎖）可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪問(wèn)。使用排他鎖（X 鎖）時(shí)，任何其他事務(wù)都無(wú)法修改數(shù)據(jù)；僅在使用 NOLOCK 提示或未提交讀隔離級(jí)別時(shí)才會(huì)進(jìn)行讀取操作。

數(shù)據(jù)修改語(yǔ)句（如 INSERT、UPDATE 和 DELETE）合并了修改和讀取操作。語(yǔ)句在執(zhí)行所需的修改操作之前首先執(zhí)行讀取操作以獲取數(shù)據(jù)。因此，數(shù)據(jù)修改語(yǔ)句通常請(qǐng)求共享鎖和排他鎖。例如，UPDATE 語(yǔ)句可能根據(jù)與一個(gè)表的聯(lián)接修改另一個(gè)表中的行。在此情況下，除了請(qǐng)求更新行上的排他鎖之外，UPDATE 語(yǔ)句還將請(qǐng)求在聯(lián)接表中讀取的行上的共享鎖。