如何查看死锁的”SQL语句“或”存储过程“
2017-10-18
展开全部
假如发生了死锁,我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程?此时我们可以使用以下存储过程来检测,就可以查出引起死锁的进程和SQL语句。
Sql代码
usemaster
go
create proceduresp_who_lock
as
begin
declare @spid int,@blint,
@intTransactionCountOnEntryint,
@intRowcountint,
@intCountPropertiesint,
@intCounter int
create table #tmp_lock_who(
id intidentity(1,1),
spidsmallint,
blsmallint)
IF @@ERROR<>0 RETURN@@ERROR
insert into#tmp_lock_who(spid,bl) select 0 ,blocked
from (select * fromsysprocesses where blocked>0 ) a
where not exists(select *from
(select * from sysprocesseswhere blocked>0 ) b
wherea.blocked=spid)
union select spid,blockedfrom sysprocesses where blocked>0
IF @@ERROR<>0 RETURN@@ERROR
-- 找到临时表的记录数
select @intCountProperties= Count(*),@intCounter = 1
from#tmp_lock_who
IF @@ERROR<>0 RETURN@@ERROR
if@intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <=@intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl =bl
from #tmp_lock_who where Id= @intCounter
begin
if @spid=0
select '引起数据库死锁的是: '+ CAST(@bl ASVARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))
+ '被进程号SPID:'+ CAST(@bl ASVARCHAR(10))
+ '阻塞,其当前进程执行的SQL语法如下'DBCC INPUTBUFFER
use master
go
create proceduresp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntryint,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who(
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN@@ERROR
insert into#tmp_lock_who(spid,bl) select 0 ,blocked
from (select * fromsysprocesses where blocked>0 ) a
where not exists(select *from
(select * from sysprocesseswhere blocked>0 ) b
where a.blocked=spid)
union select spid,blockedfrom sysprocesses where blocked>0
IF @@ERROR<>0 RETURN@@ERROR
-- 找到临时表的记录数
select @intCountProperties= Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN@@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <=@intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl =bl
from #tmp_lock_who where Id= @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))
+ '被进程号SPID:'+ CAST(@bl AS VARCHAR(10))
+ '阻塞,其当前进程执行的SQL语法如下'DBCC INPUTBUFFER
与锁定有关的两个问题--死锁和阻塞
Sql代码
usemaster
go
create proceduresp_who_lock
as
begin
declare @spid int,@blint,
@intTransactionCountOnEntryint,
@intRowcountint,
@intCountPropertiesint,
@intCounter int
create table #tmp_lock_who(
id intidentity(1,1),
spidsmallint,
blsmallint)
IF @@ERROR<>0 RETURN@@ERROR
insert into#tmp_lock_who(spid,bl) select 0 ,blocked
from (select * fromsysprocesses where blocked>0 ) a
where not exists(select *from
(select * from sysprocesseswhere blocked>0 ) b
wherea.blocked=spid)
union select spid,blockedfrom sysprocesses where blocked>0
IF @@ERROR<>0 RETURN@@ERROR
-- 找到临时表的记录数
select @intCountProperties= Count(*),@intCounter = 1
from#tmp_lock_who
IF @@ERROR<>0 RETURN@@ERROR
if@intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <=@intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl =bl
from #tmp_lock_who where Id= @intCounter
begin
if @spid=0
select '引起数据库死锁的是: '+ CAST(@bl ASVARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))
+ '被进程号SPID:'+ CAST(@bl ASVARCHAR(10))
+ '阻塞,其当前进程执行的SQL语法如下'DBCC INPUTBUFFER
use master
go
create proceduresp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntryint,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who(
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN@@ERROR
insert into#tmp_lock_who(spid,bl) select 0 ,blocked
from (select * fromsysprocesses where blocked>0 ) a
where not exists(select *from
(select * from sysprocesseswhere blocked>0 ) b
where a.blocked=spid)
union select spid,blockedfrom sysprocesses where blocked>0
IF @@ERROR<>0 RETURN@@ERROR
-- 找到临时表的记录数
select @intCountProperties= Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN@@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <=@intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl =bl
from #tmp_lock_who where Id= @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))
+ '被进程号SPID:'+ CAST(@bl AS VARCHAR(10))
+ '阻塞,其当前进程执行的SQL语法如下'DBCC INPUTBUFFER
与锁定有关的两个问题--死锁和阻塞
本回答被网友采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
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 的必要。
本回答被网友采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询