SQL 查看 死锁
时间：2021-07-01 10:21:17
帮助过：2人阅读
master
declare @spid int,
@bl int,
@intCounter int,
@intRowcount int,
@intCountProperties int,
@intTransactionCountOnEntry int
declare @tmp_lock_who table (
id int identity(
1,
1),
spid smallint,
bl smallint)
IF @@ERROR<>0
SELECT @@ERROR
insert into @tmp_lock_who(spid,bl)
select 0 ,blocked
from (
select * from SYS.sysprocesses
where blocked
>0 ) a
where not exists
(select * from (
select * from SYS.sysprocesses
where blocked
>0 ) b
where a.blocked
=spid)
union select spid,blocked
from SYS.sysprocesses
where blocked
>0
IF @@ERROR<>0
SELECT @@ERROR
select @intCountProperties = Count(
*),
@intCounter = 1
from @tmp_lock_who
IF @@ERROR<>0
SELECT @@ERROR
if @intCountProperties=0
BEGIN
select N
‘现在没有阻塞和死锁信息‘ as message
END
ELSE
begin
while @intCounter <= @intCountProperties
begin
select @spid = spid,
@bl = bl
from @tmp_lock_who where Id
= @intCounter
begin
if @spid =0
select N
‘引起数据库死锁的SPID: ‘+ CAST(
@bl AS VARCHAR(
10))
+ N
‘ SQL: ‘
else
select N
‘SPID: ‘+ CAST(
@spid AS VARCHAR(
10))
+ N
‘被SPID: ‘+ CAST(
@bl AS VARCHAR(
10))
+ N
‘阻塞,SQL: ‘
DBCC INPUTBUFFER (
@bl)
end
set @intCounter = @intCounter + 1
end
end
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type=‘OBJECT‘
SQL 查看 死锁
标签：
