DECLARE @spid INT
DECLARE @blk INT
DECLARE @count INT
DECLARE @index INT
DECLARE @lock TINYINT

成都創(chuàng)新互聯(lián)是一家專注于做網(wǎng)站、成都網(wǎng)站制作與策劃設(shè)計,丹寨網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十載,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:丹寨等地區(qū)。丹寨做網(wǎng)站價格咨詢:13518219792

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  '引起數(shù)據(jù)庫死鎖的是: ' + Cast(@spid AS VARCHAR(10)) + '進(jìn)程號,其執(zhí)行的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)) + '進(jìn)程號,其執(zhí)行的SQL語法如下' 
        ELSE
          SELECT '進(jìn)程號SPID：' + Cast(@spid AS VARCHAR(10)) + '被' + '進(jìn)程號SPID：' + Cast(@blk AS VARCHAR(10)) + '阻塞,其當(dāng)前進(jìn)程執(zhí)行的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

文章名稱：日常SQL數(shù)據(jù)庫死鎖跟蹤及處理
文章URL：http://aaarwkj.com/article32/gghppc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供虛擬主機(jī)、微信公眾號、用戶體驗(yàn)、網(wǎng)站營銷、品牌網(wǎng)站設(shè)計、服務(wù)器托管

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)