SQL Server - 鎖定類型介紹

本篇文章是從 (http://www.sqlteam.com) 轉載而來，主要介紹SQL Server 中鎖定的類型，再請大家參考。

原文主旨：Introduction to Locking in SQL Server

鎖定是很多RDBMS很重要的一部份，而且也是每個DBA必須去了解的部份。他是一個資料庫中的功能，在多使用者的環境下沒有他就不能正常的運作。主要的問題為鎖定在本質上是一種邏輯而非實體的問題。這代表著硬體數量的多寡也將無法幫助你去解決他。或許硬體的增加可能可以增加效能，但這只是一個假像的修正。在一個沉重的多使用者環境問題遲早會再出現。

鎖定模式：
全部的範例皆執行在預設的 READ COMMITED  的隔離層級下。透過鎖定在不同的隔離層級下，下列範例是透過語法秀出鎖定的類型。在此先行說明在sys.dm_tran_locks中的三個欄位的意義。

1. resource_type：這將告訴我們目前在資料庫中鎖定的項目為何。數位可能為：DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT.
2. request_mode：這將告訴我們鎖定的模式為何。
3. resource_description：這將顯示一個資料的描述與簡介。通常保持著一個Page的ID，物件檔案，資料列，列，等....，這不是一個填補在每一個鎖定的樣目。

範列中過濾條件為 resource_type <> 'DATABASE' 意思上是代表將不要去看到一般分享的資料庫鎖定。這是固定的行為。全部的輸出皆由 sys.dm_tran_locks 的動態管理檢示表。在下列的範例中，將只秀出鎖定的部份。在執行上你也可以決定自行輸出的欄位。

共享鎖定 Shared Locks (s)
共享鎖定是保持在資料讀取悲觀 (Pessimistic) 並行的模組下。一個共享鎖定運作時是保持其他交易能取讀取但是不能夠修改鎖定資料的情況下。鎖定的資料將在共享鎖定讀取完成之後釋放，除非該交易是透過提示(READCOMMITTED、READCOMMITTEDLOCK)的方式或是隔離等級等於或大於 REPEATABLE READ。在一般的情況下，你將看不到共享鎖定，因為在 選取sys.dm_tran_locks 的命令過程中 SELECT 已經釋放鎖定 。這也就是為什麼需要一個額外的 (HOLDLOCK) 提示來看到鎖定的類型。

BEGIN TRAN
USE AdventureWorks
SELECT * FROM Person.Address WITH (HOLDLOCK)
WHERE AddressId = 2
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

更新鎖定 Update Locks (U)
更新鎖定是一種分享和獨占的混合鎖定。在DML執行之前修改之前會使用共用鎖定，其他交易可以讀取被鎖定的資料，但不可以修改。一旦開始修改時，就變成了獨占鎖定，其他交易無法讀取和更新被鎖定的資料，直到交易結束。所以更新鎖是可以避免造成死結的發生。在相同的時間只有一個更新鎖能夠鎖定資料，類似獨占鎖。但是不同的地方，更新鎖只能鎖住自已本身不能夠修改在基礎的資料上。他可以在修改資料之前轉換成獨占鎖，你可以在更新鎖的部份透過提示 UPDLOCK來完成。

BEGIN TRAN

USE AdventureWorks

SELECT * FROM Person.Address WITH (UPDLOCK)
WHERE AddressId < 2 SELECT resource_type, request_mode, resource_description FROM sys.dm_tran_locks WHERE resource_type <> 'DATABASE'

ROLLBACK

獨占鎖定 Exclusive Locks (X)
獨占鎖定是使用在一個修改資料的交易中並防止其他交易進行修改。你只可以透過 NOLOCK的提示讀取到已鎖定的資料或讀取未確認的隔離等級資料。當修改資料時，SQL Server會使用獨佔鎖定。在交易結束之前，其他交易的鎖定請求都會被拒絕。一個資源只能有一個獨佔鎖定，當一個交易對某個資源進行獨占鎖定時，其他交易無法讀取該資源，由此可知，這種鎖定會限制了同時線上數量。

BEGIN TRAN

USE AdventureWorks

UPDATE Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE AddressId = 5

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

意圖鎖定 Intent Locks (I)
意圖鎖定的意思是當一個交易通知其他的交易，他有 "意圖" 去鎖定資料，所以如同名稱一般。他是確保交易得到正確的資料進行修改，防止其他交易去取得一個更高層級的鎖定。這個意義上是表示，你在 Page 得到一個鎖定之前或在 Table 上的一個 Row 的意圖鎖定。這是防止其他的交易將獨占鎖定放置在表格上，並嘗試去取消 Row 或 Page 的鎖定。在範列上可以到看意圖獨占鎖定放置在 Page 和 Table 上，是為了要防止其他交易去鎖定資料。

BEGIN TRAN

USE AdventureWorks

UPDATE TOP(5) Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE PostalCode = '98011'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> DATABASE

ROLLBACK

綱要鎖定 Schema Locks (Sch)
綱要鎖定分成兩種類型：

• 綱要平穩鎖定 Schema Stability Locks (Sch-S)：使用在當產生執行計畫時，這個鎖定並不會封鎖去存取其他的資料。
• 綱要修改鎖定 Schema Modification Locks (Sch-S)：使用在當執行一個 DDL 的語句。當結構正在改變時，將封鎖存取物件資料。

在下列的範例上將可以看到 Sch-S 和 Sch-M 的鎖定目前的系統表格和 TestTable再加上一些其他的鎖定在系統表格上。
BEGIN TRAN

USE AdventureWorks

CREATE TABLE TestTable (TestColumn INT)

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

大量更新鎖定 Bulk Update Locks (BU)
大量更新鎖定是使用大家操作當 TABLOCK 提示是使用在匯入的時候。這是允許多個快速同時的新增經由不允許的資料讀取到其他的交易上。

交談鎖定 Conversion Locks
交談鎖定是鎖定結果從一個類型的鎖定轉換到另一種。下列是三種交談鎖定的類型：

• 共享與意圖獨佔 Shared with Intent Exclusive (SIX)：一個交易將包含一個共享鎖定與一些Page 或 Row 的獨占鎖定。
• 共享與意圖更新 Shared with Intent Update (SIU)：一個交易將包含一個共享鎖定與一些Page 或 Row 的更新鎖定。
• 更新與意圖獨占鎖定 Update with Intent Exclusive (UIX)：一個交易將包含一個更新鎖定與一些Page 或 Row 的獨占鎖定。

在下列的範列中，你可以看到 UIX 的交談鎖定在頁面上：
BEGIN TRAN

USE AdventureWorks

UPDATE TOP(5) Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE PostalCode = '98011'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

鍵值範圍鎖定 Key-Range Locks
鍵值範圍鎖定保護一個 Row 的範圍隱含在一個記錄的讀取經由一個 Transact-SQL 語句當使用序列化交易隔離層級。鍵值範圍鎖定可以防止 幽靈讀取 (Phantom Reads) 的情況發生。經由鍵值範圍保護的的資料列，他可以防止 幽靈新增或刪除到一個交易記錄。在下列的範列將秀出兩種鍵值範圍鎖定：

譯註：
Phantom Reads：主要用在高層級的交易隔離，當交易A同時查詢一個資料表二次，假設第一次得到五筆，而第二次查詢前，剛好交易B新增或刪除了一筆時，造成交易A的第二次查詢與第一次查詢筆數不相同。

• RangeX-X - 獨占鎖定在鍵值和獨占鎖定之間最後的鍵值範圍。
• RangeS-U - 共享鎖定在鍵值和更新鎖定間最後的鍵值範圍。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN

USE AdventureWorks

UPDATE Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE AddressLine1 LIKE '987 %'

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK

鎖定粒度 Lock Granularity
鎖定的粒度考量到 Table、Page 和 Row 的鎖定。如果你有一個叢集索引在表格上將會由鍵值鎖定取代列的鎖定。鎖定在較低的層級會同時的增加，但是如果使用了過多的鎖定是將造成消耗更多的記憶體，反之亦然。所以粒度簡單的意思是指SQL Server鎖定資料的層級。同樣地我們需要注意更多的限制隔離層級，較高的鎖定層級會去保持資料在正確的狀態。你可以覆寫鎖定層級經由使用 ROWLOCK、PAGLOCK、TABLOCK提示，但是使用這些提示是不鼓勵的，因為SQL Server明白什麼時候使用合適的鎖定。如果你必須使用提示你將必須注意資料一致性的問題與可能發生的情況。

譯註：關於提示的使用，可以參考我的另二此文章

1. 你是否需要 SQL Server Query Hint ?
2. SQL Server 效能調整 - Optimizer Hint 的使用

自旋鎖 Spinlocks
自旋鎖是一種輕量級的鎖定機制，他將不鎖定資料，但是他將透過交易鎖定一個區塊的資料一段時間後，再進行釋放，然後再轉由其他交易鎖定。這是一種互斥機制藉以減少 SQL Server中的 Threads 進行 本文交換(Context Switching) 次數。

鎖定相容性矩陣 Lock Compatibility Matrix
下列的圖表是微軟的網站捉取來的，同樣的一個好的資源進行鎖定時，可以透過 鎖定相容性矩陣來與其他的鎖定模組進行比較，或許在現在你沒有使用的急迫性，但是以後你應該是會用到的。

總結
希望這個主題可以讓大家對SQL Server的運作與鎖定有些微的了解，也可以知道鎖定的重要性
在應用程式、資料庫設計和操作上。請記得鎖定的問題是一種邏輯性並非實際自然可見的，所以必須深思熟慮後才可以了解。鎖定與交易的隔離等級有非常大的相關聯，作者將會在下一個主題介紹一些方法去解決鎖定上的問題。

參考網址：

1. Introduction to Locking in SQL Server：http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
2. Lock Compatibility：http://msdn.microsoft.com/En-US/library/ms186396.aspx
3. Lock Granularity and Hierarchies：http://msdn.microsoft.com/en-us/library/ms189849.aspx
4. SET TRANSACTION ISOLATION LEVEL：http://msdn.microsoft.com/en-us/library/ms173763.aspx

關鍵字：SQL Server、Locking、Locks、Locks Hints