sql server中怎样查询引起死锁的sql语句
4个回答
展开全部
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
展开全部
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
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
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
你说的是什么 insert语句?
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
2020-12-07 · MySQL开源数据库领先者
关注
展开全部
找到事务号,可以从 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 的必要。
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 的必要。
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询