SQL Server 中的鎖定介紹

Microsoft® SQL Server™ 2000 具有多粒度鎖定，允許一個事務鎖定不同型別的資源。為了使鎖定的成本減至最少，SQL Server 自動將資源鎖定在適合任務的級別。鎖定在較小的粒度（例如行）可以增加併發但需要較大的開銷，因為如果鎖定了許多行，則需要控制更多的鎖。鎖定在較大的粒度（例如表）就併發而言是相當昂貴的，因為鎖定整個表限制了其它事務對錶中任意部分進行訪問，但要求的開銷較低，因為需要維護的鎖較少[@more@]

SQL Server 可以鎖定以下資源（按粒度增加的順序列出）。

資源	描述
RID	行識別符號。用於單獨鎖定表中的一行。
鍵	索引中的行鎖。用於保護可序列事務中的鍵範圍。
頁	8 千位元組 (KB) 的資料頁或索引頁。
擴充套件盤區	相鄰的八個資料頁或索引頁構成的一組。
表	包括所有資料和索引在內的整個表。
DB	資料庫。

SQL Server 使用不同的鎖模式鎖定資源，這些鎖模式確定了併發事務訪問資源的方式。

SQL Server 使用以下資源鎖模式。

鎖模式	描述
共享 (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) 鎖鎖定的資料。

意向鎖

意向鎖表示 SQL Server 需要在層次結構中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級的共享意向鎖表示事務打算在表中的頁或行上放置共享 (S) 鎖。在表級設定意向鎖可防止另一個事務隨後在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高效能，因為 SQL Server 僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

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

鎖模式	描述
意向共享 (IS)	透過在各資源上放置 S 鎖，表明事務的意向是讀取層次結構中的部分（而不是全部）底層資源。
意向排它 (IX)	透過在各資源上放置 X 鎖，表明事務的意向是修改層次結構中的部分（而不是全部）底層資源。IX 是 IS 的超集。
與意向排它共享 (SIX)	透過在各資源上放置 IX 鎖，表明事務的意向是讀取層次結構中的全部底層資源並修改部分（而不是全部）底層資源。允許頂層資源上的併發 IS 鎖。例如，表的 SIX 鎖在表上放置一個 SIX 鎖（允許併發 IS 鎖），在當前所修改頁上放置 IX 鎖（在已修改行上放置 X 鎖）。雖然每個資源在一段時間內只能有一個 SIX 鎖，以防止其它事務對資源進行更新，但是其它事務可以透過獲取表級的 IS 鎖來讀取層次結構中的底層資源。

架構鎖

執行表的資料定義語言 (DDL) 操作（例如新增列或除去表）時使用架構修改 (Sch-M) 鎖。

當編譯查詢時，使用架構穩定性 (Sch-S) 鎖。架構穩定性 (Sch-S) 鎖不阻塞任何事務鎖，包括排它 (X) 鎖。因此在編譯查詢時，其它事務（包括在表上有排它 (X) 鎖的事務）都能繼續執行。但不能在表上執行 DDL 操作。

大容量更新鎖

當將資料大容量複製到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設定了 table lock on bulk 表選項時，將使用大容量更新 (BU) 鎖。大容量更新 (BU) 鎖允許程式將資料併發地大容量複製到同一表，同時防止其它不進行大容量複製資料的程式訪問該表。