mysql查询数据库阻塞_SQL Server数据库阻塞，死锁查询

导读

正文SELECT SPID=p.spid,DBName = convert(CHAR(20),d.name),ProgramName = program_name,LoginName = convert(CHAR(20),l.name),HostName = convert(CHAR(20),hostname),Status = p.status,BlockedBy = p.blocked,LoginTime = login_time,QUERY = CAST(TEXT AS VARCHAR(MAX))

FROM MASTER.dbo.sysprocesses p

INNER JOIN MASTER.dbo.sysdatabases d

ON p.dbid = d.dbid

INNER JOIN MASTER.dbo.syslogins l

ON p.sid = l.sid

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE p.blocked = 0

AND EXISTS (SELECT 1

FROM MASTER..sysprocesses p1

WHERE p1.blocked = p.spid)

@H_301_6@

存储过程查询具体的死锁，阻塞中的sql语句

新建存储过程

create procedure [dbo].[sp_who_lock]

as

begin

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 RETURN @@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 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

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

drop table #tmp_lock_who

return 0

end

GO

@H_301_6@

后续可直接执行存储过程查看

exec sp_who_lock

@H_301_6@

结束阻塞的进程

kill 进程号

@H_301_6@

相关

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

如您喜欢交流学习经验，点击链接加入交流1群：1065694478(已满)交流2群：163560250