如何查看SQL表死锁

最新推荐文章于 2025-07-10 11:33:09 发布

转载最新推荐文章于 2025-07-10 11:33:09 发布 · 1.2w 阅读

MS-SQL 学习笔记专栏收录该内容

47 篇文章

订阅专栏

本文介绍了如何使用SQL Server的内置工具和自定义存储过程来查看死锁的表，并提供了KILL进程的方法来解决死锁问题。通过实例演示了如何执行SQL查询和存储过程来定位并解决死锁。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/***********************************************************
**如何查看死锁的表：方法一**********************************
************************************************************/
SELECT request_session_id spid ,
OBJECT_NAME(resource_associated_entity_id) tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'

/***********************************************************
**如何查看死锁的表：方法二**********************************
************************************************************/
IF EXISTS ( SELECT 1
FROM sys.procedures
WHERE name = 'sp_who_lock' )
BEGIN
DROP PROCEDURE sp_who_lock
END
GO

CREATE PROCEDURE sp_who_lock
AS
BEGIN
DECLARE @spid INT
DECLARE @blk INT
DECLARE @count INT
DECLARE @index INT
DECLARE @lock TINYINT
SET @lock = 0
CREATE TABLE #temp_who_lock
(
id INT IDENTITY(1, 1) ,
spid INT ,
blk INT
)
IF @@error <> 0
RETURN @@error
INSERT INTO #temp_who_lock
( spid ,
blk
)
SELECT 0 ,
blocked
FROM ( SELECT *
FROM master..sysprocesses
WHERE blocked > 0
) a
WHERE NOT EXISTS ( SELECT *
FROM master..sysprocesses
WHERE a.blocked = spid
AND blocked > 0 )
UNION
SELECT spid ,
blocked
FROM master..sysprocesses
WHERE blocked > 0
IF @@error <> 0
RETURN @@error
SELECT @count = COUNT(*) ,
@index = 1
FROM #temp_who_lock
IF @@error <> 0
RETURN @@error
IF @count = 0
BEGIN
SELECT '没有阻塞和死锁信息'
RETURN 0
END
WHILE @index <= @count
BEGIN
IF EXISTS ( SELECT 1
FROM #temp_who_lock a
WHERE id > @index
AND EXISTS ( SELECT 1
FROM #temp_who_lock
WHERE id <= @index
AND a.blk = spid ) )
BEGIN
SET @lock = 1
SELECT @spid = spid ,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
SELECT '引起数据库死锁的是: ' + CAST(@spid AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
SELECT @spid ,
@blk
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
END
SET @index = @index + 1
END
IF @lock = 0
BEGIN
SET @index = 1
WHILE @index <= @count
BEGIN
SELECT @spid = spid ,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
IF @spid = 0
SELECT '引起阻塞的是:' + CAST(@blk AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
ELSE
SELECT '进程号SPID：' + CAST(@spid AS VARCHAR(10))
+ '被' + '进程号SPID：'
+ CAST(@blk AS VARCHAR(10))
+ '阻塞,其当前进程执行的SQL语法如下'
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
SET @index = @index + 1
END
END
DROP TABLE #temp_who_lock
RETURN 0
END
GO

EXEC sp_who_lock
*/

/***********************************************************
**如何Kill死锁的表进程**************************************
************************************************************/

kill spid