Question

sql server中怎样查询引起死锁的sql语句

Answer 1

DECLARE @spid INT
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

Answer 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

DECLARE @spid INT
DECLARE @blk INT
DECLARE @count INT
DECLARE @index INT
DECLARE @lock <a href="https://www.baidu.com/s?wd=TINYINT&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">TINYINT</a>

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 <a href="https://www.baidu.com/s?wd=EXISTS&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">EXISTS</a>(SELECT *
FROM master..sysprocesses
WHERE a.blocked = spid
AND blocked > 0)
<a href="https://www.baidu.com/s?wd=UNION&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">UNION</a>
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
<a href="https://www.baidu.com/s?wd=END&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">END</a>

WHILE @index <= @count
BEGIN
IF <a href="https://www.baidu.com/s?wd=EXISTS&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">EXISTS</a>(SELECT 1
FROM #temp_who_lock a
WHERE id > @index
AND <a href="https://www.baidu.com/s?wd=EXISTS&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">EXISTS</a>(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 <a href="https://www.baidu.com/s?wd=VARCHAR&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">VARCHAR</a>(10)) + '进程号,其执行的SQL语法如下' ;

SELECT @spid,
@blk

DBCC inputbuffer(@spid)

DBCC inputbuffer(@blk)
<a href="https://www.baidu.com/s?wd=END&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">END</a>

SET @index=@index + 1
<a href="https://www.baidu.com/s?wd=END&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">END</a>

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 <a href="https://www.baidu.com/s?wd=VARCHAR&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">VARCHAR</a>(10)) + '进程号,其执行的SQL语法如下'
<a href="https://www.baidu.com/s?wd=ELSE&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">ELSE</a>
SELECT '进程号SPID：' + Cast(@spid AS <a href="https://www.baidu.com/s?wd=VARCHAR&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">VARCHAR</a>(10)) + '被' + '进程号SPID：' + Cast(@blk AS VARCHAR(10)) + '阻塞,其当前进程执行的SQL语法如下'

PRINT ( <a href="https://www.baidu.com/s?wd=LTRIM&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">LTRIM</a>(@spid) + ''+ <a href="https://www.baidu.com/s?wd=LTRIM&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3Pym1n10vnHTLPj9WPj9h0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1DLPHTknH0vP1n4PjfsnHbvP0" target="_blank" class="baidu-highlight">LTRIM</a>(@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

Answer 3

你说的是什么 insert语句？

Answer 4

找到事务号，可以从 events_statements_current 找到对应的 SQL 语句：
SQL_TEXT: delete from action1 where id = 3 //具体的sql语句
DIGEST: 8f9cdb489c76ec0e324f947cc3faaa7c
DIGEST_TEXT: DELETE FROM `action1` WHERE `id` = ?
CURRENT_SCHEMA: test1
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: 00000
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 1
ROWS_SENT: 0
ROWS_EXAMINED: 3
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)

可以看到是一条 delete 阻塞了后续的 update，生产环境中可以拿着这条 SQL 语句询问开发，是不是有 kill 的必要。