如何使用快照集隔離來降低鎖定問題的發生

        快照集隔離是SQL Server 2005所引進的一個新功能，快照集隔離一旦啟用後，即會在 tempdb 中維護每筆交易所更新的資料列版本。透過唯一交易序號識別每筆交易，並會針對每個資料列版本記錄這些唯一的號碼。交易使用其序號在該交易序號之前的最新資料列版本。該交易會忽略在交易開始之後建立的較新資料列版本。

PS：由於快照集隔離主要透過 tempdb 來進行交易的快照與維護，所以建議你一定要進行tempdb的最佳化，建議你可以參考我的另一篇文章 [SQL Server 儲存設備 (Storage) 最佳調整作業]。

        「快照集」這個詞彙反映了根據交易開始時資料庫的狀態，交易中所有查詢都看到資料庫的同一版本 (或快照集) 這一事實。在快照集交易中的基礎資料列或資料頁面上沒有鎖定，這允許其他交易執行，而不會被之前未完成的交易封鎖。修改資料的交易不會封鎖讀取資料的交易，而讀取資料的交易也不會封鎖寫入資料的交易，因為它們通常處於 SQL Server 中預設的 READ COMMITTED 隔離等級中。這種無封鎖的行為也會大幅降低複雜交易發生死結的可能性。

        再來我們來介紹一下 SQL Server Database Engine 的 交易隔離層級，為了保證資料庫中的每一個交易的完整性，所以所有的交易一定必須遵守 (ACID)的原則，也就是 (Atomicity, Consistency, Isolation, and Durability) 四個項目，其中重要的就是隔離屬性 (Isolaton Property)是大家一定要認識的。它的能力是保護交易免於受到其他並行交易執行更新的影響。隔離等級 (Isolation Level) 事實上可針對每一筆交易來自訂。

        SQL Server Database Engine支援以 SQL-92 定義的交易隔離層級。設定交易隔離層級可讓程式設計人員，針對某些增加完整性問題的風險以及支援更大的資料並行存取之間，進行截長補短的取捨。交易隔離等級為：

隔離等級	中途讀取 (Dirty read)	非可重複讀取	幽靈 (Phantom)
READ UNCOMMITTED	是	是	是
READ COMMITTED	否	是	是
REPEATABLE READ	否	否	是
SNAPSHOT	否	否	否
SERIALIZABLE	否	否	否

PS：READ COMMITTED為SQL Server Database Engine的預設交易隔離層級

        由於SQL Server Database Engine的預設交易隔離層級為READ COMMITTED，但由於有些應用用程式或語法的設計問題，所以造成系統中有許多Blocking或Deadlock的發生，所以有些人就會透過 with nolock 的Table Hint來進行降低隔離層級至 (READ UNCOMMITTED)，但是在使用上並沒有完全的了解此語法的使用方式，導致造成 Dirty Read的發生，所以還是要看你的系統的應用，不能盲目的使用。

接下來我們來進行一個實驗，確認一下快照集隔離的威力。

1、我們先透過下列的語法確認目前已有那些資料庫已啟用 [快照集隔離] 的功能。
select database_id, name,snapshot_isolation_state, is_read_committed_snapshot_on
from sys.databases

2、確認在 [cary_test] 的資料庫下的 test資料表中有三筆資料。
SELECT [sn] ,[name]
  FROM [cary_test].[dbo].[test]


3、透過下列的指令進行資料列的更新，但是為了模擬鎖定的狀況，所以我在語法中有設定 Begin Transaction，但是卻沒有進行Commit或Rollback的指令。
Begin Tran
update test
set name = 'jim'
where sn = 2;


4、當下你在透過另一個查詢視窗進行 Select 時，由於需要取得資料列，所以會一直查不出來，但是如果你跳過 sn = 2 的那一筆時，查詢就會正確。

查詢不出結果
SELECT [sn] ,[name]
  FROM [cary_test].[dbo].[test]

查詢正確
SELECT [sn] ,[name]
  FROM [cary_test].[dbo].[test]
 WHERE sn <> 2


5、現在我們就來啟用快照集隔離的功能，請透過下列的語法進行。
ALTER DATABASE cary_test
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE cary_test
SET READ_COMMITTED_SNAPSHOT ON

select database_id, name,snapshot_isolation_state, is_read_committed_snapshot_on
from sys.databases


6、接下來再將原本1~4的步驟再次模擬測試，你會發現原本在第4個步驟中查詢不出來的情況，已可正常的查詢。

相信透過上面的解說，你應已了解到快照集隔離可以有效的降低 Blocking 與 Deadlock 的發生，但是再次提醒由於啟用後會大量的使用到 tempdb，所以請別忘了進行 tempdb 的效能最佳化，以免解決了 Blocking，但是造成了另一個 I/O 上的瓶頸就划不來了。



參考進結：
Locking Hints
http://msdn.microsoft.com/en-us/library/aa213026(v=sql.80).aspx
資料表提示 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187373.aspx
sys.databases (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms178534.aspx
Database Engine 中的隔離等級
http://msdn.microsoft.com/zh-tw/library/ms189122.aspx
調整交易隔離等級
http://msdn.microsoft.com/zh-tw/library/ms189542.aspx
啟用資料列版本控制式的隔離等級
http://msdn.microsoft.com/zh-tw/library/ms175095.aspx
使用快照集隔離
http://msdn.microsoft.com/zh-tw/library/tcbchxcb(v=vs.80).aspx
SQL Server 儲存設備 (Storage) 最佳調整作業
http://caryhsu.blogspot.com/2011/02/sql-server-io.html


關鍵字：SQL Server、ALLOW_SNAPSHOT_ISOLATION、READ_COMMITTED_SNAPSHOT、SNAPSHOT、快照集隔離、tempdb、Blocking、snapshot isolation level