查询系统中死锁的SQL语句代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】查询系统中死锁的SQL语句

作者:dezai / 发布于2014/2/8/ 628

查询系统中死锁的SQL语句

查询系统中死锁的SQL语句

declare @spid int,@bl int,
@intTransactionCountOnEntry   int,
@intRowcount       int,
@intCountProperties     int,
@intCounter       int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 print @@ERROR
insert into #tmp_lock_who(spid,bl) 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
IF @@ERROR<>0 print @@ERROR
-- 找到临时表的记录数
select   @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 print @@ERROR
if  @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select   @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
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 )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end

select * from #tmp_lock_who

drop table #tmp_lock_who

select @@lock_timeout

查看锁信息 
 select  进程id=req_spid 
 ,数据库=db_name(rsc_dbid) 
 ,类型=case  rsc_type  when  1  then  'NULL  资源（未使用）' 
 when  2  then  '数据库' 
 when  3  then  '文件' 
 when  4  then  '索引' 
 when  5  then  '表' 
 when  6  then  '页' 
 when  7  then  '键' 
 when  8  then  '扩展盘区' 
 when  9  then  'RID（行  ID)' 
 when  10  then  '应用程序' 
 end 
 ,rsc_objid,rsc_indid 
 from  master..syslockinfo 



select * from sys.dm_tran_locks
SET SHOWPLAN_ALL ON; SET STATISTICS IO on; SET STATISTICS TIME on

试试其它关键字

　死锁的SQL语句　

同语言下

可能有用的

dezai贡献的其它代码(1065)