SQL Server數據庫表鎖定原理以及如何解除表的鎖定--示例演示

作者: 我愛菊花來源: 博客園發布時間: 2010-10-15 08:10 閱讀: 1276 次推薦: 0 原文鏈接 [收藏]

　　有幾個朋友留言建議結合例子來演示一下，上篇已經說過鎖的幾種類型，可以利用系統動態視圖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 AdventureWorks2008

BEGIN TRAN
select * 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_database
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

--ROLLBACK TRAN

　　在事務回滾之前, 查看鎖的類型：

　　其他session對Table只讀，不能更新，在開一個新的session測試：

select * from Sales.SalesOrderHeader where SalesOrderID='43662'

go
update Sales.SalesOrderHeader set OrderDate=GETDATE() where SalesOrderID='43662'

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

　　2. Update locks (U)：更新鎖是共享鎖和獨占鎖的組合.用UPDLOCK保持更新鎖。

USE AdventureWorks2008

BEGIN TRAN
select * 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_database
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

ROLLBACK TRAN

　　查看到鎖的信息：

　　3.Exclusive locks (X)：獨占鎖是為了鎖定數據被一個session修改的數據，而不能夠被另外的session修改。只能指定NOLOCK來讀取。

USE AdventureWorks2008

BEGIN TRAN

update 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_locks
WHERE 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 AdventureWorks2008

BEGIN TRAN
CREATE TABLE MyTable (ID INT, NAME VARCHAR(20),COUNTRY VARCHAR(15))

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

ROLLBACK 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 語句時，數據庫引擎查詢處理器會決定將要訪問哪些資源。查詢處理器根據訪問類型和事務隔離級別設置來確定保護每一資源所需的鎖的類型。然后，查詢處理器將向鎖管理器請求適當的鎖。如果與其他事務所持有的鎖不會發生沖突, 鎖管理器將授予該鎖。

標簽：SQL Server