如何查看死锁的”SQL语句“或”存储过程“

假如发生了死锁，我们怎么去检测具体发生死锁的是哪条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
与锁定有关的两个问题－－死锁和阻塞

找到事务号，可以从 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 的必要。