MSSQL查锁解锁
上一篇 /
下一篇 2015-11-25 14:20:05
近来经常出现数据库死锁导致数据库操作时出现问题，为临时规避问题，查找了网络资料，记录sqlserver查锁和批量解锁的方法如下：
--查看锁
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'
--解锁
DECLARE @i INT
DECLARE @n INT
DECLARE @spid INT
set @i=0
SELECT @n=COUNT(*) FROM sys.dm_tran_locks where resource_type='OBJECT'
WHILE(@i<@n)
BEGIN
IF EXISTS(SELECT 1 FROM sys.dm_tran_locks where resource_type='OBJECT')
BEGIN
SET @spid = (select TOP 1 request_session_id FROM sys.dm_tran_locks where resource_type='OBJECT')
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
END
SET @i=@i+1
END
收藏
举报
TAG:
