SQL Server資料庫表鎖定原理以及如何解除表的鎖定–樣本示範

<<接上篇:SQL Server資料庫表鎖定原理以及如何解除表的鎖定>>

 

有幾個朋友留言建議結合例子來示範一下, 上篇已經說過鎖的幾種類型, 可以利用系統動態視圖sys.dm_tran_locks查看到,重要的欄位如下:

resource_type	被鎖的資源類型(Database, FILE, Object,PAGE,KEY,EXTENT,RID,APPLICATION,METADATA,HOBT,APPOCATION_UNIT)
request_mode	鎖的類型(共用鎖定,更新鎖定,排它鎖, 架構鎖等)
resource_description	資源描述
request_session_id	Request session ID

 

一: 下面以AdventureWorks2008為樣本資料庫做簡要的說明,過濾掉一般的資料庫的共用鎖定, 作為樣本必須要看到鎖, 所以用WITH(HOLDLOCK)來保持鎖.

1. Shared locks (S) 共用鎖定

USE AdventureWorks2008BEGIN TRANselect * from Sales.SalesOrderHeader WITH(HOLDLOCK)where SalesOrderID='43662'     SELECT resource_type, request_mode, resource_description,request_session_id, DB_NAME(resource_database_id)as resource_databaseFROM   sys.dm_tran_locksWHERE  resource_type <> 'DATABASE'--ROLLBACK TRAN

 

在交易回復之前, 查看鎖的類型:

 

其他session對Table唯讀, 不能更新, 在開一個新的session測試:

select * from Sales.SalesOrderHeader  where SalesOrderID='43662'goupdate Sales.SalesOrderHeader set OrderDate=GETDATE() where SalesOrderID='43662'

 

select可以正常執行, update語句一直處於等待狀態, 等待上面的session釋放鎖.

 

2. Update locks (U): 更新鎖定是共用鎖定和獨佔鎖的組合.用UPDLOCK保持更新鎖定

USE AdventureWorks2008BEGIN TRANselect * from Sales.SalesOrderHeader WITH(UPDLOCK)where SalesOrderID='43662'     SELECT resource_type, request_mode, resource_description,request_session_id,DB_NAME(resource_database_id)as resource_databaseFROM   sys.dm_tran_locksWHERE  resource_type <> 'DATABASE'ROLLBACK TRAN

 

查看到鎖的資訊:

 

 

3.Exclusive locks (X): 獨佔鎖是為了鎖定資料被一個session修改的資料, 而不能夠被另外的session修改. 只能指定NOLOCK來讀取.

 

USE AdventureWorks2008BEGIN TRANupdate Sales.SalesOrderHeader set ShipDate=GETDATE() where SalesOrderID='43662'    SELECT resource_type, request_mode, resource_description,request_session_id,DB_NAME(resource_database_id)as resource_database--,*FROM   sys.dm_tran_locksWHERE  resource_type <> 'DATABASE'ROLLBACK TRAN

查看鎖:

 

 

4.Intent locks (I): 意圖鎖定用於建立鎖的階層. 意圖鎖定包含三種類型：意圖共用 (IS)、意向排他 (IX) 和意向排他共用 (SIX)。

資料庫引擎使用意圖鎖定來保護共用鎖定（S 鎖）或獨佔鎖定（X 鎖）放置在鎖階層的底層資源上。 意圖鎖定之所以命名為意圖鎖定，是因為在較低層級鎖前可擷取它們，因此會通知意向將鎖放置在較低層級上。

意圖鎖定有兩種用途：

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

 

 

5. Schema locks (Sch): 架構鎖

• Schema stability lock(Sch-S): 保持架構穩定性,用在產生執行計畫時,不會阻止對資料的訪問.
• Schema modification lock (Sch-M):用在DDL操作時.當架構正在被改變時, 阻止對對象資料的訪問.

 

USE AdventureWorks2008BEGIN TRANCREATE TABLE MyTable (ID INT, NAME VARCHAR(20),COUNTRY VARCHAR(15))SELECT resource_type, request_mode, resource_descriptionFROM   sys.dm_tran_locksWHERE  resource_type <> 'DATABASE' order by request_modeROLLBACK TRAN

6. Bulk Update locks (BU)

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

 

7. Key - Range locks

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

 

二: 死結與死結解除

1. 死結

使用或管理資料庫都不可避免的涉及到死結. 一旦發生死結, 資料相互等待對方資源的釋放,會阻止對資料的訪問, 嚴重會造成DB掛掉. 當資源被鎖定, 無法被訪問時, 可以終止訪問DB的那個session來達到解鎖的目的(即 Kill掉造成鎖的那個進程).

在兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定資源，此時會造成這些任務永久阻塞，從而出現死結。 例如：

• 事務 A 擷取了行 1 的共用鎖定。
• 事務 B 擷取了行 2 的共用鎖定。
• 現在，事務 A 請求行 2 的獨佔鎖定，但在事務 B 完成並釋放其對行 2 持有的共用鎖定之前被阻塞。
• 現在，事務 B 請求行 1 的獨佔鎖定，但在事務 A 完成並釋放其對行 1 持有的共用鎖定之前被阻塞。

事務 B 完成之後事務 A 才能完成，但是事務 B 由事務 A 阻塞。該條件也稱為循環相依性關係： 事務 A 依賴於事務 B，事務 B 通過對事務 A 的依賴關係關閉迴圈。

除非某個外部進程斷開死結，否則死結中的兩個事務都將無限期等待下去。 Microsoft SQL Server 資料庫引擎死結監視器定期檢查陷入死結的任務。 如果監視器檢測到循環相依性關係，將選擇其中一個任務作為犧牲品，然後終止其事務並提示錯誤。 這樣，其他任務就可以完成其事務。 對於事務以錯誤終止的應用程式，它還可以重試該事務，但通常要等到與它一起陷入死結的其他事務完成後執行。

 

2. 死結檢測

2.1 SQL Server 資料庫引擎自動檢測 SQL Server 中的死結迴圈。資料庫引擎選擇一個會話作為死結犧牲品，然後終止當前事務（出現錯誤）來打斷死結。

2.2 查看DMV: sys.dm_tran_locks

2.3 SQL Server Profiler能夠直觀的顯示死結的圖形事件.

 

三: 鎖相容性

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

 

 

 

四: 總結

鎖的原理比較抽象,對使用者來說是透明的,不用過多的關注. 應用程式一般不直接請求鎖. 鎖由資料庫引擎的一個組件（稱為“鎖管理器”）在內部管理. 當資料庫引擎執行個體處理Transact-SQL 陳述式時, 資料庫引擎查詢處理器會決定將要訪問哪些資源. 查詢處理器根據訪問類型和交易隔離等級設定來確定保護每一資源所需的鎖的類型. 然後, 查詢處理器將向鎖管理器請求適當的鎖. 如果與其他事務所持有的鎖不會發生衝突, 鎖管理器將授予該鎖.