SQL鎖機制和事務隔離級别（轉）

鎖機制

NOLOCK和READPAST的差別。

1.開啟一個事務執行插入資料的操作。

BEGIN TRAN t

INSERT INTO Customer

SELECT 'a','a'

2.執行一條查詢語句。

SELECT * FROM Customer WITH (NOLOCK)

結果中顯示”a”和”a”。當1中事務復原後，那麼a将成為髒資料。(注:1中的事務未送出) 。NOLOCK表明沒有對資料表添加共享鎖以阻止其它事務對資料表資料的修改。

SELECT * FROM Customer

這條語句将一直死鎖，直到排他鎖解除或者鎖逾時為止。(注:設定鎖逾時SET LOCK_TIMEOUT 1800)

SELECT * FROM Customer WITH (READPAST)

這條語句将顯示a未送出前的狀态，但不鎖定整個表。這個提示指明資料庫引擎傳回結果時忽略加鎖的行或資料頁。

3.執行一條插入語句。

SELECT 'b','b'

COMMIT TRAN t

這個時候，即使步驟1的事務復原，那麼a這條資料将丢失，而b繼續插入資料庫中。 

NOLOCK

1． 執行如下語句。

BEGIN TRAN ttt

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.執行一條帶行鎖的查詢語句。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- (必須)

SELECT * FROM Customer WITH (ROWLOCK) WHERE ID=17

注：在删除和更新正在查詢的資料時，會鎖定資料。對其他未查詢的行和增加，查詢資料無影響。

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.執行HOLDLOCK

SELECT * FROM Customer WITH (HOLDLOCK)

WAITFOR delay '00:00:10'

注：其他事務可以讀取表，但不能更新删除  

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

SELECT * FROM Customer —不需要等待

2.執行TABLOCKX

SELECT * FROM Customer WITH (TABLOCKX)

注：其他事務不能讀取表,更新和删除

SELECT * FROM Customer —要等待10秒中。

3. 執行TABLOCK

SELECT * FROM Customer WITH (TABLOCK)

UDPLOCK

1.在A連接配接中執行。

SELECT * FROM Customer WITH (UPDLOCK)

2.在其他連接配接中執行。

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

SELECT * FROM Customer –不用等

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

注：對于UDPLOCK鎖，隻對更新資料鎖定。

注：使用這些選項将使系統忽略原先在SET語句設定的事務隔離級别（SET Transaction Isolation Level)。

事務隔離級别

髒讀：READ UNCOMMITTED

髒讀就是指當一個事務正在通路資料，并且對資料進行了修改，而這種修改還沒有送出到資料庫中，這時，另外一個事務也通路這個資料，然後使用了這個資料。因為這個資料是還沒有送出的資料，那麼另外一個事務讀到的這個資料是髒資料，依據髒資料所做的操作可能是不正确的。

SELECT '123','123'

2.在B連接配接中執行。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

這個時候，未送出的資料會’123’會顯示出來，當A事務復原時就導緻了髒資料。相當于(NOLOCK)

送出讀：READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

這個時候，未送出的資料會’123’不會顯示出來，當A事務送出以後B中才能讀取到資料。避免了髒讀。

不可重複讀：REPEATABLE READ

不可重複讀是指在一個事務内，多次讀同一資料。在這個事務還沒有結束時，另外一個事務也通路該同一資料。那麼，在第一個事務中的兩次讀資料之間，由于第二個事務的修改，那麼第一個事務兩次讀到的資料可能是不一樣的。這樣就發生了在一個事務内兩次讀到的資料是不一樣的，是以稱為是不可重複讀。

例如：

1.在A連接配接中執行如下語句。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT * FROM Customer WHERE ID=17

WAITFOR delay '00:00:30'

2.在B連接配接中執行如下語句，而且要在第一個事物的三十秒等待内。

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

這個時候，此連接配接将鎖住不能執行，一直等到A連接配接結束為止。而且A連接配接中兩次讀取到的資料相同，不受B連接配接幹擾。

注，對于Read Committed和Read UnCommitted情況下，B連接配接不會鎖住，等到A連接配接執行完以後，兩條查詢語句結果不同，即第二條查詢的Title變成了d。

序列化讀：SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

UPDATE Customer SET Title='111'

2. 在B連接配接中執行，并且要在A執行後的20秒内。

BEGIN TRAN tt

SELECT '2','2'

COMMIT TRAN tt

在A連接配接的事務送出之前，B連接配接無法插入資料到表中，這就避免了幻覺讀。

注：幻覺讀是指當事務不是獨立執行時發生的一種現象，例如 第一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，第二個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行，就好像發生了幻覺一樣。

共享鎖

共享鎖（S 鎖）允許并發事務在封閉式并發控制（請參閱并發控制的類型）下讀取 (SELECT) 資源。資源上存在共享鎖（S 鎖）時，任何其他事務都不能修改資料。讀取操作一完成，就立即釋放資源上的共享鎖（S 鎖），除非将事務隔離級别設定為可重複讀或更進階别，或者在事務持續時間内用鎖定提示保留共享鎖（S 鎖）。

更新鎖

更新鎖（U 鎖）可以防止常見的死鎖。在可重複讀或可序列化事務中，此事務讀取資料 [擷取資源（頁或行）的共享鎖（S 鎖）]，然後修改資料 [此操作要求鎖轉換為排他鎖（X 鎖）]。如果兩個事務獲得了資源上的共享模式鎖，然後試圖同時更新資料，則一個事務嘗試将鎖轉換為排他鎖（X 鎖）。共享模式到排他鎖的轉換必須等待一段時間，因為一個事務的排他鎖與其他事務的共享模式鎖不相容；發生鎖等待。第二個事務試圖擷取排他鎖（X 鎖）以進行更新。由于兩個事務都要轉換為排他鎖（X 鎖），并且每個事務都等待另一個事務釋放共享模式鎖，是以發生死鎖。

若要避免這種潛在的死鎖問題，請使用更新鎖（U 鎖）。一次隻有一個事務可以獲得資源的更新鎖（U 鎖）。如果事務修改資源，則更新鎖（U 鎖）轉換為排他鎖（X 鎖）。

排他鎖

排他鎖（X 鎖）可以防止并發事務對資源進行通路。使用排他鎖（X 鎖）時，任何其他事務都無法修改資料；僅在使用 NOLOCK 提示或未送出讀隔離級别時才會進行讀取操作。

資料修改語句（如 INSERT、UPDATE 和 DELETE）合并了修改和讀取操作。語句在執行所需的修改操作之前首先執行讀取操作以擷取資料。是以，資料修改語句通常請求共享鎖和排他鎖。例如，UPDATE 語句可能根據與一個表的聯接修改另一個表中的行。在此情況下，除了請求更新行上的排他鎖之外，UPDATE 語句還将請求在聯接表中讀取的行上的共享鎖。

部落格園大道至簡

http://www.cnblogs.com/jams742003/

轉載請注明：部落格園