SQL SERVER的鎖機制（四）——概述（各種交易隔離等級發生的影響）

SQL SERVER的鎖機制（一）——概述（鎖的種類與範圍）

SQL SERVER的鎖機制（二）——概述（鎖的相容性與可以鎖定資源）

 

本文上接SQL SERVER的鎖機制（三）——概述（鎖與交易隔離等級）

 

 

六、各種交易隔離等級發生的影響

修改資料的使用者會影響同時讀取或修改相同資料的其他使用者。即這些使用者可以並發訪問資料。如果資料存放區系統沒有並發控制，則使用者可能會看到以下負面影響：

· 未提交的依賴關係（髒讀）

· 不一致的分析（不可重複讀取）

· 幻讀

（一）髒讀：

例：張某正在執行某項業務，如下：

begin traninsert tbUnRead select 3,'張三'unionselect 4,'李四'---延遲秒，類比真實交易情形，用於處理商務邏輯waitfor delay '00:00:05'rollback tran---此時李某對錶中資料進行查詢，執行了以下語句：set Transaction isolation level read uncommitted--查詢資料select * from tbUnRead where name like '張%'

 

 

則李某可以看到張某所執行的插入語句，把資料添加到了資料庫，如。

但是張某最終是沒有提交事務，而是復原了事務，所以這條記錄並沒有真正插入到資料庫中。從而發生李某將髒讀的資料當成真實的查詢結果。

   要解決此問題，就是要把資料庫的交易隔離等級由未提交讀修改成已提交讀。只有當查詢結果的正確性不是非常重要，或者是隔一段時間查詢一次情況下，即使這一次查詢結果是錯，而下次查詢結果是對的，並不會有太大影響，這才適合使用未提交讀。

 

（二）不可重複讀取

例：張某正在查詢資料，如下

set Transaction isolation level read committedbegin transelect * from tbUnRead  where ID=2 ---延遲秒，類比真實交易情形，用於處理商務邏輯waitfor delay '00:00:05'select * from tbUnRead  where ID=2 commit tran---此時李對錶中資料進行了更新，如下語句：update tbUnReadset name='Jack_upd'where ID=2

 

 上面的執行語句造成張某在同一個事務內，兩次相同的查詢條件，查詢到不相同的結果（如）。

這是由於“已提交讀”隔離等級對共用鎖定保留的時間是：一旦查詢完畢就立即釋放，而非事務完成才釋放。所在張某雖然還在使用事務，事務過程中的所有獨佔鎖都會一直保留，讓事務中所更改的資料別人不可進行查詢與更改，直到事務完成。但是，被查詢的資料在事務過程中是查詢完畢就立即釋放共用鎖定，所以別人仍然可以進行修改，造成一筆事務中，兩次相同的查詢條件，可以得到不相同的結果。最佳的解決方案是將隔離等級設定為“可重複讀”。

“可重複讀”交易隔離等級，讓事務過程中所曾經建立的共用鎖定都一直保留到事務完成，雖然可以避免“不可重複讀取”的問題，但是也會導致資料鎖定太久，而別人無法讀取資料，影響並發率，甚至提高了“死結”的發生率。

 

（三）幻讀

例：張某正在查詢資料，如下

set Transaction isolation level REPEATABLE READbegin transelect * from tbUnRead  where ID=3 ---延遲秒，類比真實交易情形，用於處理商務邏輯waitfor delay '00:00:05'select * from tbUnRead  where ID=3 commit tran --此時李某新增了一條記錄，如下語句：INSERT TBUNreadselect 3,'幻讀'

 

 

 

張某已經把隔離等級設定為“可重複讀”，雖然是曾經讀取的資料，不管是共用鎖定還是互斥鎖都 保留到了事務結束，但是無法阻止其他人運行新增操作，導致第一次查詢時沒有資料，第二次查詢時卻有了資料。被稱為“幻讀”。如。

為了避免此類問題，可以將隔離等級設定為“可序列化”，設定之後，則其他人則無法新增資料。

 

 

七、各隔離等級所能防止的訪問錯誤

隔離等級	髒讀	不可重複讀取	幻讀
未提交讀	是	是	是
已提交讀	否	是	是
可重複讀	否	否	是
快照	否	否	否
可序列化	否	否	否

 

 

八、常用鎖與交易隔離等級間的互動影響

	已提交讀	可重複讀	快照	可序列化
共用	讀完資料後就釋放	事務結束才釋放	不加鎖，以版本來控制	事務結束才釋放
更新	讀完資料後就釋放或是升級成獨佔鎖	讀完資料後就釋放或是升級成獨佔鎖	不加鎖，以版本來控制	讀完資料後就釋放或升級成獨佔鎖
獨佔	事務結束才釋放	事務結束才釋放	不加鎖，以版本來控制	事務結束才釋放

 

九、動態鎖定管理

資料庫引擎使用動態鎖定管理原則來控制鎖定和系統的最佳成本效益。資料庫引擎可以動態調整資料粒度與鎖定類型，當使用最低一級的行鎖而非更大範圍的頁鎖時，可以降低兩個事務要求相同範圍的資料鎖定的可能性，增強並行訪問的能力，可同時服務更多的使用者，減小死結的機率。相反低級鎖轉為進階鎖可以減小系統的資源負擔，但會增加並行爭用的可能性。

此機制由鎖管理器進行管理，每一個鎖都需要記憶體去記錄，並且要與鎖管理器進行合作，才能完成資料訪問操作，你可以想像當表中有100萬條記錄時，你執行一條沒有where語句的update指令時，在預設情況下資料庫引擎會採用行鎖，但這要記錄100萬條行鎖記錄，以及相關的意圖共用鎖，必定會消耗掉大量的系統資源，當系統資源不足時，資料庫引擎會自動提升鎖的層級，也就是由行鎖提升為頁鎖，如果資源還是不足，則會再次提升，提升為表鎖。

就以上例子來說，如果每個頁可以放200條記錄，則100萬表記錄的行鎖轉為5000個頁鎖，還省掉了大量的意圖共用鎖。如果資源還是一足，則可以再次提升鎖層級，提升到表鎖，這樣就只需要一個鎖就可以了。

愈大範圍的鎖花費在管理鎖之上的資源就愈少。但相對來說，同時上線並發訪問該資源的人數就越少。例如：或採用行鎖，則你訪問你的記錄，我訪問我的記錄，互相不影響，但如果升級到頁鎖，則如果你先搶到該分頁，而我要訪問的記錄又恰恰在這一分頁上，則我必須要等你釋放該分頁之後才能訪問。如果升級到表鎖，則同一時間，該表中的記錄只能一個人才能訪問，其他人不能訪問。如。

 

 

 

一般情況下，是不需要手工去設定鎖定範圍的，可以由Microsoft SQL Server 資料庫引擎視情況而定，使用動態鎖定策略確定最經濟的鎖。 執行查詢時，資料庫引擎會根據架構和查詢的特點自動決定最合適的鎖。 例如，為了縮減鎖定的開銷，最佳化器可能在執行索引掃描時在索引中選擇頁級鎖。

動態鎖定具有下列優點：

· 簡化資料庫管理。 資料庫管理員不必調整鎖定擴大閾值。

· 提高效能。 資料庫引擎通過使用適合任務的鎖使系統開銷減至最小。

· 應用程式開發人員可以集中精力進行開發。 資料庫引擎將自動調整鎖定。

在 SQL Server 2008 中，鎖定擴大的行為已發生改變，其中引入了 LOCK_ESCALATION選項