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

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

　　1. 數據庫表鎖定原理

　　1.1 目前的C/S，B/S結構都是多用戶訪問數據庫，每個時間點會有成千上萬個user來訪問DB,其中也會同時存取同一份數據,會造成數據的不一致性或者讀臟數據。

　　1.2 事務的ACID原則

　　1.3 鎖是關系數據庫很重要的一部分，數據庫必須有鎖的機制來確保數據的完整和一致性。

　　1.3.1 SQL Server中可以鎖定的資源:

　　1.3.2 鎖的粒度:

　　1.3.3 鎖的升級：鎖的升級門限以及鎖升級是由系統自動來確定的，不需要用戶設置。

　　1.3.4 鎖的類型

　　(1) 共享鎖：共享鎖用于所有的只讀數據操作。

　　(2) 修改鎖：修改鎖在修改操作的初始化階段用來鎖定可能要被修改的資源，這樣可以避免使用共享鎖造成的死鎖現象。

　　(3) 獨占鎖：獨占鎖是為修改數據而保留的。它所鎖定的資源，其他事務不能讀取也不能修改。獨占鎖不能和其他鎖兼容。

　　(4) 架構鎖：結構鎖分為結構修改鎖（Sch-M）和結構穩定鎖（Sch-S）。執行表定義語言操作時，SQL Server采用Sch-M鎖，編譯查詢時，SQL Server采用Sch-S鎖。

　　(5) 意向鎖：意向鎖說明SQL Server有在資源的低層獲得共享鎖或獨占鎖的意向。

　　(6) 批量修改鎖：批量復制數據時使用批量修改鎖。

　　1.3.4 SQL Server鎖類型　　

　　(1) HOLDLOCK: 在該表上保持共享鎖，直到整個事務結束，而不是在語句執行完立即釋放所添加的鎖。　　

　　(2) NOLOCK：不添加共享鎖和排它鎖，當這個選項生效后，可能讀到未提交讀的數據或“臟數據”，這個選項僅僅應用于SELECT語句。　　

　　(3) PAGLOCK：指定添加頁鎖（否則通常可能添加表鎖）。　　　

　　(4) READCOMMITTED用與運行在提交讀隔離級別的事務相同的鎖語義執行掃描。默認情況下，SQL Server 2000 在此隔離級別上操作。

　　(5) READPAST: 跳過已經加鎖的數據行，這個選項將使事務讀取數據時跳過那些已經被其他事務鎖定的數據行，而不是阻塞直到其他事務釋放鎖，READPAST僅僅應用于READ COMMITTED隔離性級別下事務操作中的SELECT語句操作。　　　

　　(6) READUNCOMMITTED：等同于NOLOCK。　　　

　　(7) REPEATABLEREAD：設置事務為可重復讀隔離性級別。　　　

(　　8) ROWLOCK：使用行級鎖，而不使用粒度更粗的頁級鎖和表級鎖。　　　　

　　(9) SERIALIZABLE：用與運行在可串行讀隔離級別的事務相同的鎖語義執行掃描。等同于 HOLDLOCK。　　　

　　(10) TABLOCK：指定使用表級鎖，而不是使用行級或頁面級的鎖，SQL Server在該語句執行完后釋放這個鎖，而如果同時指定了HOLDLOCK，該鎖一直保持到這個事務結束。

　　(11) TABLOCKX：指定在表上使用排它鎖，這個鎖可以阻止其他事務讀或更新這個表的數據，直到這個語句或整個事務結束。　　　

　　(12) UPDLOCK ：指定在讀表中數據時設置更新鎖（update lock）而不是設置共享鎖，該鎖一直保持到這個語句或整個事務結束，使用UPDLOCK的作用是允許用戶先讀取數據（而且不阻塞其他用戶讀數據），并且保證在后來再更新數據時，這一段時間內這些數據沒有被其他用戶修改。

　　(本段摘自CSDN博客: http://blog.csdn.net/zp752963831/archive/2009/02/18/3906477.aspx)

　　2. 如何解除表的鎖定，解鎖就是要終止鎖定的那個鏈接，或者等待該鏈接事務釋放。

　　2.1 Activity Monitor

　　可以通過Wait Type，Blocked By欄位查看到，SPID 54 被SPID 53 阻塞。可以右鍵Details查到詳細的SQL 語句,或Kill掉這個進程。

　　2.2 SQL Server提供幾個DMV，查看locks：

　　sys.dm_exec_requests

　　sys.dm_tran_locks

　　sys.dm_os_waiting_tasks

　　sys.dm_tran_database_transactions

　　(1)

select * from sys.dm_tran_locks where resource_type<>'DATABASE' --and resource_database_id=DB_ID()

　　(2)

SELECT session_id, blocking_session_id,*
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0

　　(3)

SELECT
request_session_id as Spid,
Coalesce(s.name + '.' + o.name + isnull('.' + i.name,''),
s2.name + '.' + o2.name,
db.name) AS Object,
l.resource_type as Type,
request_mode as Mode,
request_status as Status
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p
ON l.resource_associated_entity_id = p.hobt_id
LEFT JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
LEFT JOIN sys.objects o
ON p.object_id = o.object_id
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN sys.objects o2
ON l.resource_associated_entity_id = o2.object_id
LEFT JOIN sys.schemas s2
ON o2.schema_id = s2.schema_id
LEFT JOIN sys.databases db
ON l.resource_database_id = db.database_id
WHERE resource_database_id = DB_ID()
ORDER BY Spid, Object, CASE l.resource_type
When 'database' Then 1
when 'object' then 2
when 'page' then 3
when 'key' then 4
Else 5 end