2012年1月31日 星期二

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

        快照集隔離是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 ServerALLOW_SNAPSHOT_ISOLATIONREAD_COMMITTED_SNAPSHOTSNAPSHOT快照集隔離tempdbBlockingsnapshot isolation level

沒有留言:

張貼留言