SQL Server数据库表锁定原理以及如何解除表的锁定(2)_建站学-个人建站指南,网页制作,网站设计,网站制作教程

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

(责任编辑：admin)

搜索

热门标签:

SQL Server数据库表锁定原理以及如何解除表的锁定(2)