SQLServer查看死锁的表和Kill死锁进程
1、查询出现死锁的表
查询语句
select
request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
from
sys.dm_tran_locks
where
resource_type='OBJECT'
查询结果如下
2、杀死某个锁
要想杀死某个锁需要使用kill spid
语句如下
kill 56
go
kill 54
3、也可以创建查看死锁进程的存储过程
代码如下
CREATE PROCEDURE [dbo].[sp_who_lock]
AS
BEGIN
DECLARE @spid INT ,
@bl INT ,
@intTransactionCountOnEntry INT ,
@intRowcount INT ,
@intCountProperties INT ,
@intCounter INT,
@sql_handle VARBINARY(64)
DECLARE @tmp_lock_who TABLE
(
id INT IDENTITY(1, 1) ,
spid SMALLINT ,
bl SMALLINT,
sql_handle VARBINARY(64)
)
IF @@ERROR <> 0
RETURN @@ERROR
;
WITH tb_blocked AS(
SELECT spid, blocked, sql_handle FROM master..sysprocesses WHERE blocked > 0
)
INSERT INTO @tmp_lock_who
( spid ,
bl, sql_handle
)
SELECT DISTINCT blocked,0, p_bl.sql_handle
FROM tb_blocked
CROSS APPLY (SELECT p_bl.sql_handle FROM master..sysprocesses p_bl WHERE p_bl.spid = tb_blocked.blocked) p_bl
WHERE NOT EXISTS ( SELECT *
FROM tb_blocked a
WHERE tb_blocked.blocked = a.spid )
UNION ALL
SELECT spid, blocked, sql_handle FROM tb_blocked
IF @@ERROR <> 0
RETURN @@ERROR
-- 找到临时表的记录数
SELECT @intCountProperties = COUNT(*),
@intCounter = 1
FROM @tmp_lock_who
IF @@ERROR <> 0
RETURN @@ERROR
IF @intCountProperties = 0
SELECT '现在没有阻塞和死锁信息' AS message
-- 循环开始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一条记录
SELECT @spid = spid, @bl = bl, @sql_handle = sql_handle
FROM @tmp_lock_who
WHERE id = @intCounter
BEGIN
IF @bl = 0
BEGIN
SELECT '阻塞根源' + CAST(@spid AS VARCHAR(10)) AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
END
ELSE
BEGIN
SELECT CAST(@spid AS VARCHAR(10)) + '被' + CAST(@bl AS VARCHAR(10)) + '阻塞' AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
END
DBCC INPUTBUFFER(@spid)
END
-- 循环指针下移
SET @intCounter = @intCounter + 1
END
RETURN 0
END
GO
可以通过执行存储锁秤sp_who_lock查看当前死锁进程
exec sp_who_lock
4、也可以穿件杀掉引起死锁进程的存储过程
代码如下
create proc dbo.p_killspid
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master.dbo.sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
调用存储过程exec dbo.p_killspid spid杀死进程。