Sql Server 鎖資源模式詳解

學習資料，用做備忘

1. 共用鎖定

共用鎖定允許並發事務在封閉式並發控制下讀取(Select) 資源。資源上存在共用鎖定(S鎖)時，任何其他事物都不能修改資料。

2. 更新鎖定

在可重複讀或可序列化事物中，一個修改需要先讀取資料(擷取資源頁或行的共用鎖定)，然後修改資料(此操作要求鎖轉換為排它鎖)。如果兩個事務獲得了同一個資源上的共用模式鎖，然後試圖同時更新資料，則事務會把共用鎖定轉換為排它鎖(X鎖)。由於兩個事務都無法完成轉換，因此發生死結。
為了避免這種潛在的死結問題，SQL Server 使用更新鎖定(U)。一次只有一個事務可以獲得資源的更新鎖定。事務真正修改資料時，將更新鎖定(U)轉換為排它鎖.

3. 排它鎖

排它鎖(X 鎖)可以防止並發事務對資源進行訪問。使用排它鎖時，任何其他事務都無法讀取或修改資料；僅在使用 NoLock 提示或未提交讀隔離等級時才會進行讀取操作。

資料修改語句(Insert、Update 或 Delete)合并了修改和讀取的操作。語句在執行所需的修改操作之前首先執行讀取操作以獲得資料。銀次，資料修改語句通常請求共用鎖定和獨佔鎖定。例如，Update 語句可能根據與一個表的串連修改另一個表中的行。在此情況下，除了請求更新行上的獨佔鎖定之外，Update 語句還將請求語一個表的聯結修改另一個表中的行。在此情況下，除了請求更新行上的排它鎖之外，Update語句還將請求在連接表中讀取行上的共用鎖定。

4. 意圖鎖定

資料庫引擎使用意圖鎖定來保護鎖層次的底層資源，以防止其他事務對自己鎖住的資源造成傷害，提高鎖衝突檢測效能。
意圖鎖定可以提高效能，因為資料庫引擎僅在表級檢查意圖鎖定，確定事務是否能安全滴擷取該表上的鎖，而不需要檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表

5. 架構鎖

資料庫引擎在表資料定義語言 (Data Definition Language)(DDL)操作(例如添加列或刪除表)的過程中使用架構修改鎖，阻止其他使用者對這個表格的訪問。

資料庫引擎在編譯和執行查詢時使用架構穩定性(Sch-S)鎖，該鎖智慧組織對錶格做修改型的DDL 操作 和 DML 操作

6. 大容量更新鎖定

資料庫引擎在將資料大量複製到表中時使用大容量更新(BU)鎖，並指定TABLOCK提示或使用sp_tableoption 設定 table lock on bulk load 表選項。大容量更新鎖定(BU 鎖)允許多個現成將資料並發地大量載入到同一表，同時防止其他不進行大量載入資料的進程訪問表。

7. 關鍵範圍鎖定

在使用可序列化交易隔離等級時，對於 SQL 陳述式讀取的記錄集，建範圍鎖可以隱式保護該記錄集中包含的行範圍。建範圍鎖可防止幻讀。通過保護行之間建的範圍，它還可防止對事務訪問的記錄集進行幻插入或刪除。

鎖相容性控制多個事務能否同時擷取同時擷取同一資源上的鎖。如果資源已被另一個事務鎖定，則僅當請求鎖的模式與現有鎖的模式相相容時，才會授予新的鎖請求。反之，請求新鎖的事務將被迫進入等待狀態，阻塞也就隨之產生。

鎖的模式和相容性是SQL Server預先定義好的，沒有任何參數或配置能夠去修改它們。

如果應用程式申請的鎖粒度都比較小，陳勝阻塞的幾率就會比較小。如果一個串連會經常申請頁面級、表級，甚至是資料庫一級的鎖資源，程式產生阻塞的可能性就會很大。

如果一個串連總是能夠非常快地把申請到的鎖釋放掉，那阻塞就不容易發生。如果它總是長時間地持有某些鎖資源，那麼就很容易發生阻塞了。

影響鎖的粒度和持有時間的三個情況：

1. 一個事務內部要訪問或者修改的資料量越大，它所要申請的鎖的數目就會越多，粒度也就可能越大

2. 一個事務做的事情越複雜，它要申請的鎖的範圍也就會越大

3. 一個事務延續的時間越長，它持有鎖的時間也就會越長

事務的隔離等級能影響鎖的申請以及釋放的時間；而語句的執行計畫，也會影響到鎖的粒度以及申請的數量

原文地址：Sql Server 鎖資源模式詳解