金蝶CLOUD阻塞和死锁跟踪解析

DECLARE @blk INT

DECLARE @count INT

DECLARE @index INT

DECLARE @lock TINYINT

SET @lock=0

CREATE TABLE #temp_who_lock

(

id INT IDENTITY(1, 1),

spid INT,

blk INT

)

--if @@error<>0 return @@error

INSERT INTO #temp_who_lock

(spid,

blk)

SELECT 0,

blocked

FROM (SELECT

FROM master..sysprocesses

WHERE blocked > 0)a

WHERE NOT EXISTS(SELECT

FROM master..sysprocesses

WHERE a.blocked = spid

AND blocked > 0)

UNION

SELECT spid,

blocked

FROM master..sysprocesses

WHERE blocked > 0

--if @@error<>0 return @@error

SELECT @count = Count(*),

@index = 1

FROM #temp_who_lock

--select @count,@index

--if @@error<>0 return @@error

IF @count = 0

BEGIN

SELECT '没有阻塞和死锁信息'

--return 0

END

WHILE @index <= @count

BEGIN

IF EXISTS(SELECT 1

FROM #temp_who_lock a

WHERE id > @index

AND EXISTS(SELECT 1

FROM #temp_who_lock

WHERE id <= @index

AND a.blk = spid))

BEGIN

SET @lock=1

SELECT @spid = spid,

@blk = blk

FROM #temp_who_lock

WHERE id = @index

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

SELECT @spid,

@blk

DBCC inputbuffer(@spid)

DBCC inputbuffer(@blk)

END

SET @index=@index + 1

END

IF @lock = 0

BEGIN

SET @index=1

WHILE @index <= @count

BEGIN

SELECT @spid = spid,

@blk = blk

FROM #temp_who_lock

WHERE id = @index

IF @spid = 0

SELECT '引起阻塞的是:' + Cast(@blk AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'

ELSE

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

PRINT ( LTRIM(@spid) + ''+ LTRIM(@blk));

if(@spid <> 0)

BEGIN

DBCC inputbuffer(@spid) --

END

DBCC inputbuffer(@blk) --引起阻塞语句

SET @index=@index + 1

END

END

DROP TABLE #temp_who_lock

--return 0

--KILL 54