SQL Server 鎖

標籤：SQL SERVER/MSSQL SERVER/資料庫/死鎖/

鎖資源  

Microsoft SQL Server 資料庫引擎具有多粒度鎖定，允許一個事務鎖定不同型別的資源。 為了儘量減少鎖定的開銷，資料庫引擎自動將資源鎖定在適合任務的級別。 鎖定在較小的粒度（例如行）可以提高併發度，但開銷較高，因為如果鎖定了許多行，則需要持有更多的鎖。 鎖定在較大的粒度（例如表）會降低了併發度，因為鎖定整個表限制了其他事務對錶中任意部分的訪問。 但其開銷較低，因為需要維護的鎖較少。

資料庫引擎通常必須獲取多粒度級別上的鎖才能完整地保護資源。 這組多粒度級別上的鎖稱為鎖層次結構。 例如，為了完整地保護對索引的讀取，資料庫引擎例項可能必須獲取行上的共享鎖以及頁和表上的意向共享鎖。

資料庫引擎可以鎖定的資源。

資源	說明
RID	用於鎖定堆中的單個行的行識別符號。
KEY	索引中用於保護可序列化事務中的鍵範圍的行鎖。
PAGE	資料庫中的 8 KB 頁，例如資料頁或索引頁。
EXTENT	一組連續的八頁，例如資料頁或索引頁。
HoBT	堆或 B 樹。 用於保護沒有聚集索引的表中的 B 樹（索引）或堆資料頁的鎖。
TABLE	包括所有資料和索引的整個表。
FILE	資料庫檔案。
APPLICATION	應用程式專用的資源。
METADATA	後設資料鎖。
ALLOCATION_UNIT	分配單元。
DATABASE	整個資料庫。

 

鎖模式  

資料庫引擎使用的資源鎖模式。

鎖模式	說明
共享 (S)	用於不更改或不更新資料的讀取操作，如 SELECT 語句。
更新 (U)	用於可更新的資源中。 防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。
排他 (X)	用於資料修改操作，例如 INSERT、UPDATE 或 DELETE。 確保不會同時對同一資源進行多重更新。
意向	用於建立鎖的層次結構。 意向鎖包含三種型別：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。
架構	在執行依賴於表架構的操作時使用。 架構鎖包含兩種型別：架構修改 (Sch-M) 和架構穩定性 (Sch-S)。
大容量更新 (BU)	在向表進行大容量資料複製且指定了 TABLOCK 提示時使用。
鍵範圍	當使用可序列化事務隔離級別時保護查詢讀取的行的範圍。 確保再次執行查詢時其他事務無法插入符合可序列化事務的查詢的行。

共享鎖

共享鎖（S 鎖）允許併發事務在封閉式併發控制下讀取 (SELECT) 資源。 有關詳細資訊，請參閱併發控制的型別。 資源上存在共享鎖（S 鎖）時，任何其他事務都不能修改資料。 讀取操作一完成，就立即釋放資源上的共享鎖（S 鎖），除非將事務隔離級別設定為可重複讀或更高階別，或者在事務持續時間內用鎖定提示保留共享鎖（S 鎖）。

更新鎖

更新鎖（U 鎖）可以防止常見的死鎖。 在可重複讀或可序列化事務中，此事務讀取資料 [獲取資源（頁或行）的共享鎖（S 鎖）]，然後修改資料 [此操作要求鎖轉換為排他鎖（X 鎖）]。 如果兩個事務獲得了資源上的共享模式鎖，然後試圖同時更新資料，則一個事務嘗試將鎖轉換為排他鎖（X 鎖）。 共享模式到排他鎖的轉換必須等待一段時間，因為一個事務的排他鎖與其他事務的共享模式鎖不相容；發生鎖等待。 第二個事務試圖獲取排他鎖（X 鎖）以進行更新。 由於兩個事務都要轉換為排他鎖（X 鎖），並且每個事務都等待另一個事務釋放共享模式鎖，因此發生死鎖。

若要避免這種潛在的死鎖問題，請使用更新鎖（U 鎖）。 一次只有一個事務可以獲得資源的更新鎖（U 鎖）。 如果事務修改資源，則更新鎖（U 鎖）轉換為排他鎖（X 鎖）。

排他鎖

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

資料修改語句（如 INSERT、UPDATE 和 DELETE）合併了修改和讀取操作。 語句在執行所需的修改操作之前首先執行讀取操作以獲取資料。 因此，資料修改語句通常請求共享鎖和排他鎖。 例如，UPDATE 語句可能根據與一個表的聯接修改另一個表中的行。 在此情況下，除了請求更新行上的排他鎖之外，UPDATE 語句還將請求在聯接表中讀取的行上的共享鎖。

意向鎖

資料庫引擎使用意向鎖來保護共享鎖（S 鎖）或排他鎖（X 鎖）放置在鎖層次結構的底層資源上。 意向鎖之所以命名為意向鎖，是因為在較低階別鎖前可獲取它們，因此會通知意向將鎖放置在較低階別上。

意向鎖有兩種用途：

• 防止其他事務以會使較低階別的鎖無效的方式修改較高階別資源。
  
  
• 提高資料庫引擎在較高的粒度級別檢測鎖衝突的效率。
  
  

例如，在該表的頁或行上請求共享鎖（S 鎖）之前，在表級請求共享意向鎖。 在表級設定意向鎖可防止另一個事務隨後在包含那一頁的表上獲取排他鎖（X 鎖）。 意向鎖可以提高效能，因為資料庫引擎僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。 而不需要檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

意向鎖包括意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。

鎖模式	說明
意向共享 (IS)	保護針對層次結構中某些（而並非所有）低層資源請求或獲取的共享鎖。
意向排他 (IX)	保護針對層次結構中某些（而並非所有）低層資源請求或獲取的排他鎖。 IX 是 IS 的超集，它也保護針對低層級別資源請求的共享鎖。
意向排他共享 (SIX)	保護針對層次結構中某些（而並非所有）低層資源請求或獲取的共享鎖以及針對某些（而並非所有）低層資源請求或獲取的意向排他鎖。 頂級資源允許使用併發 IS 鎖。 例如，獲取表上的 SIX 鎖也將獲取正在修改的頁上的意向排他鎖以及修改的行上的排他鎖。 雖然每個資源在一段時間內只能有一個 SIX 鎖，以防止其他事務對資源進行更新，但是其他事務可以通過獲取表級的 IS 鎖來讀取層次結構中的低層資源。
意向更新 (IU)	保護針對層次結構中所有低層資源請求或獲取的更新鎖。 僅在頁資源上使用 IU 鎖。 如果進行了更新操作，IU 鎖將轉換為 IX 鎖。
共享意向更新 (SIU)	S 鎖和 IU 鎖的組合，作為分別獲取這些鎖並且同時持有兩種鎖的結果。 例如，事務執行帶有 PAGLOCK 提示的查詢，然後執行更新操作。 帶有 PAGLOCK 提示的查詢將獲取 S 鎖，更新操作將獲取 IU 鎖。
更新意向排他 (UIX)	U 鎖和 IX 鎖的組合，作為分別獲取這些鎖並且同時持有兩種鎖的結果。

架構鎖

資料庫引擎在表資料定義語言 (DDL) 操作（例如新增列或刪除表）的過程中使用架構修改 (Sch-M) 鎖。 保持該鎖期間，Sch-M 鎖將阻止對錶進行併發訪問。 這意味著 Sch-M 鎖在釋放前將阻止所有外圍操作。

某些資料操作語言 (DML) 操作（例如表截斷）使用 Sch-M 鎖阻止併發操作訪問受影響的表。

資料庫引擎在編譯和執行查詢時使用架構穩定性 (Sch-S) 鎖。 Sch-S 鎖不會阻止某些事務鎖，其中包括排他 (X) 鎖。 因此，在編譯查詢的過程中，其他事務（包括那些針對表使用 X 鎖的事務）將繼續執行。 但是，無法針對表執行獲取 Sch-M 鎖的併發 DDL 操作和併發 DML 操作。

大容量更新鎖

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

鍵範圍鎖

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

鎖相容性控制  

鎖相容性控制多個事務能否同時獲取同一資源上的鎖。 如果資源已被另一事務鎖定，則僅當請求鎖的模式與現有鎖的模式相相容時，才會授予新的鎖請求。 如果請求鎖的模式與現有鎖的模式不相容，則請求新鎖的事務將等待釋放現有鎖或等待鎖超時間隔過期。 例如，沒有與排他鎖相容的鎖模式。 如果具有排他鎖（X 鎖），則在釋放排他鎖（X 鎖）之前，其他事務均無法獲取該資源的任何型別（共享、更新或排他）的鎖。 另一種情況是，如果共享鎖（S 鎖）已應用到資源，則即使第一個事務尚未完成，其他事務也可以獲取該項的共享鎖或更新鎖（U 鎖）。 但是，在釋放共享鎖之前，其他事務無法獲取排他鎖。

最常見的鎖模式的相容性。

	現有授予模式
請求模式	IS	S	U	IX	SIX	X
意向共享 (IS)	是	是	是	是	是	否
共享 (S)	是	是	是	否	否	否
更新 (U)	是	是	否	否	否	否
意向排他 (IX)	是	否	否	是	否	否
意向排他共享 (SIX)	是	否	否	否	否	否
排他 (X)	否	否	否	否	否	否

 

注意：意向排他鎖（IX 鎖）與 IX 鎖模式相容，因為 IX 表示打算只更新部分行而不是所有行。 還允許其他事務嘗試讀取或更新部分行，只要這些行不是其他事務當前更新的行即可。

 

使用下表可以確定 Microsoft SQL Server 中所有可用的鎖模式的相容性。

 

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流討論》