SQL 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

是

否

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

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

本文轉自pursuer.chen(陳敏華)部落格園部落格，原文連結：http://www.cnblogs.com/chenmh/p/4595958.html，如需轉載請自行聯系原作者