SQL Server 的事務和鎖（一）

最近在項目中進行壓力測試遇到了資料庫的死結問題，簡言之，如下的代碼在 SERIALIZABLE 隔離等級造成了死結：

SELECT @findCount=COUNT(id) FROM MyTableWHERE [fk_related_id]=@ArgumentIF (@findCount > 0)BEGINROLLBACK TRANSACTIONRETURN ERROR_CODEENDINSERT INTO MyTable ([fk_related_id],…)VALUES (@Argument,…)COMMIT TRANSACTIONRETURN SUCCESS_CODE

 

在搞清楚這個問題的過程中做了不少的實驗，與各位共用。這一篇是開篇，主要說明的是 SQL Server 的四種（其實還有別的）經典的交易隔離等級，以及在不同的隔離等級下鎖的使用手段，以及所帶來的不同的資料一致性。

SQL Server 中鎖的種類（Schema操作就暫時不涉及了）

鎖類型	描述
（Shared Lock）	用於唯讀操作資料鎖定
（Update Lock）	用於資料的更新，在資料真正的需要更新的時候會申請升級為X鎖。
X（Exclusive Lock）	獨佔鎖，用於資料的更改。
Key-Range Lock（稍後討論）	僅僅在 Serializable 隔離等級保護資料，以避免任何有可能使得本事務第二次讀取資訊產生錯誤的資料插入操作

各個交易隔離等級下鎖的使用

SQL Server 中有四種交易隔離等級，具體的大家去參建 MSDN。下面列出在不同的交易隔離等級下這些鎖是如何使用的：

隔離等級	讀資料鎖狀態	寫資料鎖狀態	鎖持有時間
Read Uncommitted	不獲得任何鎖	不獲得任何鎖
Read Committed	資料獲得S鎖	對於 INSERT、DELETE、UPDATE的執行，獲得X鎖；對於UPDATE的標記，獲得U鎖；	讀完即釋放，並不持有至事務結束。
Repeatable Read	資料獲得S鎖	對於 INSERT、DELETE、UPDATE的執行，獲得X鎖；對於UPDATE的標記，獲得U鎖；	持有至事務結束
Serializable	資料獲得S鎖，同時獲得Key-Range鎖。	對於 INSERT、DELETE、UPDATE的執行，獲得X鎖；對於UPDATE的標記，獲得U鎖，同時獲得Key-Range鎖。	持有至事務結束

我們可以利用這些知識形象說明各個隔離等級下的資料一致性：

Read Uncommitted 層級

（1）髒讀

（2）更新丟失

（3）不可重複讀取

（4）幻讀

Read Committed 層級

（1）髒讀

（2）更新丟失

（3）不可重複讀取

（4）幻讀

Repeatable Read 層級

（1）髒讀

（2）更新丟失

（3）不可重複讀取

（4）幻讀

Serializable 層級

（1）髒讀

（2）更新丟失

（3）不可重複讀取

（4）幻讀

我們從可以比較直觀的看到以下的結論

	髒讀	更新丟失	不可重複讀取	幻讀
Read Uncommitted	可能	可能	可能	可能
Read Committed	不可能	可能	可能	可能
Repeatable Read	不可能	不可能	不可能	可能
Serializable	不可能	不可能	不可能	不可能

這一篇到此為止，下一篇詳細介紹 Key-Range Lock 並分析開篇提到的死結問題。