sqlserver查询是否有死锁

概述/************************************************************* Code formatted by SoftTree SQL Assistant ?v6.5.258* Time: 2014/11/12 5:46:41**********************************************************

/************************************************************* Code formatted by SoftTree sql Assistant ?v6.5.258* Time: 2014/11/12 5:46:41************************************************************/DECLARE @spID INTDECLARE @blk INTDECLARE @count INTDECLARE @index INTDECLARE @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,blockedFROM   (          SELECT *          FROM   MASTER..sysprocesses          WHERE  blocked > 0      )aWHERE  NOT EXISTS(          SELECT *          FROM   MASTER..sysprocesses          WHERE  a.blocked = spID                 AND blocked > 0      )UNIONSELECT spID,blockedFROM   MASTER..sysprocessesWHERE  blocked > 0 --if @@error<>0 return @@error    SELECT @count = COUNT(*),@index = 1FROM   #temp_who_lock --select @count,@index --if @@error<>0 return @@error    IF @count = 0BEGIN   SELECT '没有阻塞和死锁信息'           --return 0END WHILE @index <= @countBEGIN   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 + 1END IF @lock = 0BEGIN   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   ENDEND DROP table #temp_who_lock --return 0    --KILL 64

总结

以上是内存溢出为你收集整理的sqlserver查询是否有死锁全部内容，希望文章能够帮你解决sqlserver查询是否有死锁所遇到的程序开发问题。

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