检查是否存在死锁的情况 

 1 USE master
 2 GO
 3 
 4 DECLARE @spid int,
 5         @bl int
 6 
 7 DECLARE s_cur CURSOR FOR 
 8     SELECT  0, blocked FROM (SELECT * FROM sys.sysprocesses WHERE blocked>0 ) a 
 9         WHERE NOT EXISTS(SELECT * FROM (SELECT * FROM sysprocesses where  blocked>0 ) b WHERE a.blocked=spid)
10     UNION 
11     SELECT spid,blocked from sysprocesses 
12         WHERE blocked>0
13 
14 OPEN s_cur
15 
16 FETCH NEXT FROM s_cur INTO @spid, @bl
17 WHILE @@FETCH_STATUS = 0
18 BEGIN
19     IF @spid = 0 
20         SELECT '引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
21     ELSE
22         SELECT '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + ' 进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
23 
24     DBCC INPUTBUFFER (@bl )
25     
26     FETCH NEXT FROM s_cur INTO @spid,@bl
27 END
28 
29 CLOSE s_cur
30 DEALLOCATE s_cur

死锁存在的原理等情况详见阿里解释：   https://m.aliyun.com/yunqi/ziliao/41632

 

posted @ 2018-03-14 16:45  殇琉璃  阅读(169)  评论(0)  编辑  收藏  举报