Sql Server 死锁相关sql语句

--查询所有死锁数据
SELECT request_session_id spid,                             --死锁进程id
       OBJECT_NAME(resource_associated_entity_id) tableName --死锁表名
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
ORDER BY spid;



--查询所有死锁数据（按照表死锁数量倒叙排序）
SELECT COUNT(1),                                            --死锁数量
       OBJECT_NAME(resource_associated_entity_id) tableName --死锁表名
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
GROUP BY OBJECT_NAME(resource_associated_entity_id)
ORDER BY COUNT(1) DESC;



--杀死所有死锁进程
DECLARE @spid INT;
SET @spid = 62;
DECLARE @sql VARCHAR(1000);
SET @sql = 'kill ' + CAST(@spid AS VARCHAR);
EXEC (@sql);