SQL Server資料庫表鎖定原理以及如何解除表的鎖定

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

原文地址:http://www.cnblogs.com/changbluesky/archive/2010/06/10/1753021.html