深入分析MSSQL數據庫中事務隔離級別和鎖機制

2024-08-31 01:02:22

字體：大中小

來源：轉載

供稿：網友

事務隔離級別簡單的說，就是當激活事務時，控制事務內因SQL語句產生的鎖定需要保留多入，影響范圍多大，以防止多人訪問時，在事務內發生數據查詢的錯誤。設置事務隔離級別將影響整條連接。

鎖機制
NOLOCK和READPAST的區別。

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

BEGIN TRAN tINSERT INTO CustomerSELECT '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. 執行一條插入語句。

BEGIN TRAN tINSERT INTO CustomerSELECT 'b','b'COMMIT TRAN t

這個時候，即使步驟1的事務回滾，那么a這條數據將丟失，而b繼續插入數據庫中。

NOLOCK

1．執行如下語句。

BEGIN TRAN tttSELECT * 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 style="margin: 3px auto 0px; padding: 0px 0px 0px 5px; font-family: 'Courier New', Courier, monospace; border-left-color: rgb(108, 226, 108); border-left-width: 3px; border-left-style: solid; width: 640px; clear: both; line-height: 20px; ">SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- (必須)BEGIN TRAN tttSELECT * FROM Customer WITH (ROWLOCK) WHERE ID=17WAITFOR delay '00:00:20'COMMIT TRAN ttt

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

INSERT INTO Customer SELECT 'a','b' –不等待DELETE Customer where ID=17 –等待DELETE Customer where ID<>17 –不等待SELECT * FROM Customer –不等待UPDATE Customer SET style="margin: 3px auto 0px; padding: 0px 0px 0px 5px; font-family: 'Courier New', Courier, monospace; border-left-color: rgb(108, 226, 108); border-left-width: 3px; border-left-style: solid; width: 640px; clear: both; line-height: 20px; ">BEGIN TRAN tttSELECT * FROM Customer WITH (HOLDLOCK)WAITFOR delay '00:00:10'COMMIT TRAN ttt

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

update Customer set style="margin: 3px auto 0px; padding: 0px 0px 0px 5px; font-family: 'Courier New', Courier, monospace; border-left-color: rgb(108, 226, 108); border-left-width: 3px; border-left-style: solid; width: 640px; clear: both; line-height: 20px; ">BEGIN TRAN tttSELECT * FROM Customer WITH (TABLOCKX)WAITFOR delay '00:00:10'COMMIT TRAN ttt

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

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

2. 在其他連接中執行。

update Customer set style="margin: 3px auto 0px; padding: 0px 0px 0px 5px; font-family: 'Courier New', Courier, monospace; border-left-color: rgb(108, 226, 108); border-left-width: 3px; border-left-style: solid; width: 640px; clear: both; line-height: 20px; ">BEGIN TRAN tINSERT INTO CustomerSELECT '123','123'WAITFOR delay '00:00:20'COMMIT TRAN t

2. 在B連接中執行。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM Customer

這個時候，未提交的數據會'123'會顯示出來，當A事務回滾時就導致了臟數據。相當于(NOLOCK)

提交讀：READ COMMITTED

1. 在A連接中執行。

BEGIN TRAN tINSERT INTO CustomerSELECT '123','123'WAITFOR delay '00:00:20'COMMIT TRAN t

2. 在B連接中執行。

SET TRANSACTION ISOLATION LEVEL READ COMMITTEDSELECT * FROM Customer

這個時候，未提交的數據會'123'不會顯示出來，當A事務提交以后B中才能讀取到數據。避免了臟讀。

不可重復讀：REPEATABLE READ

不可重復讀是指在一個事務內，多次讀同一數據。在這個事務還沒有結束時，另外一個事務也訪問該同一數據。那么，在第一個事務中的兩次讀數據之間，由于第二個事務的修改，那么第一個事務兩次讀到的數據可能是不一樣的。這樣就發生了在一個事務內兩次讀到的數據是不一樣的，因此稱為是不可重復讀。

例如：

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

SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRAN tttSELECT * FROM Customer WHERE ID=17WAITFOR delay '00:00:30'SELECT * FROM Customer WHERE ID=17COMMIT TRAN ttt

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

UPDATE Customer SET style="margin: 3px auto 0px; padding: 0px 0px 0px 5px; font-family: 'Courier New', Courier, monospace; border-left-color: rgb(108, 226, 108); border-left-width: 3px; border-left-style: solid; width: 640px; clear: both; line-height: 20px; ">SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRAN tUPDATE Customer SET style="margin: 3px auto 0px; padding: 0px 0px 0px 5px; font-family: 'Courier New', Courier, monospace; border-left-color: rgb(108, 226, 108); border-left-width: 3px; border-left-style: solid; width: 640px; clear: both; line-height: 20px; ">BEGIN TRAN ttINSERT INTO CustomerSELECT '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 語句還將請求在聯接表中讀取的行上的共享鎖。