SQL Server鎖機制概覽及遊標鎖定

最近遇到了一個遊標鎖定相關的問題，簡單的說就是和http://support.microsoft.com/kb/953948相關的

問題，目前為止使用Read Uncommitted可以得到比較好的體驗，但客戶不想要髒資料，看來還得想想其他

辦法。以前一直因為覺得遊標效率低就沒怎麼深入研究過，這次主要談鎖順便提到遊標。

1. 基本概念
鎖是用於解決對於資源（行、頁、表）的爭用。主要是解決以下兩個問題：
a. 讀到舊資料
b. 多使用者嘗試修改時怎麼處理
（就是解決讓不讓你讀和讓你讀到什麼的問題）
處理b問題就要靠並發控制。

並發控制：
分為兩種樂觀和悲觀。
悲觀就是很霸道地把資料佔為己有，直到自己願意放手。用於資料非常搶手（high contention of data）

的情況下。
樂觀就是很大方地公用資料，當A使用者提交資料的更新要求，系統先檢查該資料是不是在A使用者讀之後已經

被修改過了。如果已經被修改過的話就報錯並復原A的更新要求。
順便說下，API級，也就是ADO, ADO.NET, OLE DB和ODBC也可以進行並發控制。

並發控制是由交易隔離等級定義的。它決定了當讀其他事務正在修改的行時，是返回：
1. 被攔住，直到其他事務釋放（Serializable，最高等級）
2. 讀到其他事務鎖定之前的版本（Read committed ，資料庫預設）
3. 讀到其他事務還沒有提交的版本（Read uncommitted ，最低等級）
還有一個Repeatable read比Read committed等級略低，可能會重複讀

2. 鎖
一般不會主動要求加鎖，而是由SQL Server資料庫引擎的lock manager來管理自動化佈建。說幾種主要的鎖

：
Shared (S) 共用鎖定，當資源上存在S鎖，任何其他事務都不能修改資料
Update (U) 更新鎖定，防止多會話同時操作，避免死結。當兩個事務爭用時，一個事務會把S鎖換成X

鎖，但這種轉換可能需要一定的時間，於是就可能出現兩個事務都轉換為X鎖，等待對方釋放S鎖，造成死

鎖。為了避免問題，最好使用U鎖，一次只有一個事務可以獲得U鎖。
Exclusive (X) 獨佔鎖定，防止多個更新同時起作用，任何其他事務都無法修改資料
Intent（I） 意圖鎖定，用於組成IS，IX，SIX鎖，用於防止其他事務使較低層級的鎖以無效的方式修改

進階鎖，提高檢測鎖衝突效率
Schema（Sch-S） 架構鎖，執行依賴於表架構的操作時，包含兩種：構修改 (Sch-M) 和架構穩定性 (Sch

-S)。

要獲得鎖相關的資訊，可以使用如下的方法：
1. SQL Server Profiler
可以在Event中選擇添加
2. sys.dm_tran_locks
DMV方式
3. sp_lock（以前好像還有sp_lock2？）
向後相容的特性，不過還挺好用，還有sys.syslockinfo
其他還有SMO、DMO方法等。

關於死結的問題可以參考上面提到的U鎖。SQL Server中有死結監視器，如果檢測到循環相依性關係，選擇其

中的一個作為犧牲品（真可憐。。。），終止事務提示錯誤。

3. 遊標鎖定
遊標類型不同鎖定方式也不同。
靜態資料指標鎖定每一行。
動態資料指標只在提取行時才擷取鎖。
遊標提交時可以選擇關閉也可以選擇繼續開啟。靠CURSOR_CLOSE_ON_COMMIT來控制。

對於遊標的並發控制，SQL Server有四個選項：
READ_ONLY
不允許通過遊標進行定點更新
OPTIMISTIC WITH VALUES
T-SQL遊標不支援。開放式並行存取控制。開啟遊標和更新的間隔允許其他進程進行更新。
OPTIMISTIC WITH ROW VERSIONING
T-SQL遊標不支援。開放式並行存取控制。基於timestamp
SCROLL LOCKS。封閉式並行存取控制

先簡單整理到這裡，更細節的以後有機會再補充。

參考資料：
Locking and Row Versioning
http://msdn.microsoft.com/en-us/library/ms187101.aspx

Cursor Locking
http://msdn.microsoft.com/en-us/library/aa172580.aspx