查数据库中死锁的问题

declare @spid int,@bl int

DECLARE s_cur CURSOR FOR

select 0 ,blocked

from (select * from sysprocesses where blocked>0 ) a

where not exists(select * from (select * from sysprocesses where blocked>0 ) b

where a.blocked=spid)

union select spid,blocked from sysprocesses where blocked>0

OPEN s_cur

FETCH NEXT FROM s_cur INTO @spid,@bl

WHILE @@FETCH_STATUS = 0

begin

if @spid =0

select ' 引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + ' 进程号, 其执行的SQL 语法如下'

else

select ' 进程号SPID ：'+ CAST(@spid AS VARCHAR(10))+ ' 被' + ' 进程号SPID ：'+ CAST(@bl AS VARCHAR(10)) +' 阻塞, 其当前进程执行的SQL 语法如下'

DBCC INPUTBUFFER (@bl )

FETCH NEXT FROM s_cur INTO @spid,@bl

end

CLOSE s_cur

DEALLOCATE s_cur

转载于:https://www.cnblogs.com/wenanry/archive/2009/12/02/1615578.html