如何查mysql死锁进程

如何查mysql死锁进程

查询死锁进程通常需要编写存储过程，以确保数据库能够识别并处理潜在的死锁情况。通过使用如下的存储过程，可以有效地监测并识别死锁进程。

首先，你需要创建并执行以下的存储过程来查询当前造成死锁的进程：

sql
drop procedure sp_who_lock
go
CREATE procedure sp_who_lock
as
begin
declare @spid int, @blk int, @count int, @index int, @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
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语法如下' dbcc inputbuffer(@spid) dbcc inputbuffer(@blk) set @index=@index+1 end end
drop table #temp_who_lock
return 0
end
GO
--执行该存储过程
exec sp_who_lock

此外，为了更好地理解死锁的产生原因，可以参考以下几点：

一、产生死锁的原因

在SQL Server中，阻塞更多的是产生于实现并发之间的隔离性。为了使得并发连接所做的操作之间的影响到达某一期望值而对资源人为的进行加锁(锁本质其实可以看作是一个标志位)。当一个连接对特定的资源进行操作时，另一个连接同时对同样的资源进行操作就会被阻塞，阻塞是死锁产生的必要条件。

二、如何避免死锁

1. 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；

2. 设置死锁超时参数为合理范围，如：3分钟-10分钟；超过时间，自动放弃本次操作，避免进程悬挂；

3. 优化程序，检查并避免死锁现象出现；

4. 对所有的脚本和SP都要仔细测试，在正是版本之前；

5. 所有的SP都要有错误处理（通过@error）；

6. 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁。

三、处理死锁

1. 最简单的处理死锁的方法就是重启服务。

2. 根据指定的死锁进程ID进行处理，根据第二步查询到的死锁进行，大致分析造成死锁的原因，并通过如下语句释放该死锁进程 `kill pid` --pid为查询出来的死锁进程号

3. 通过存储过程杀掉某个库下面的所有死锁进程和锁

sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_killspid]
GO
create proc sp_killspid @dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0 begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
--使用方法，“db_name”为处理的数据库名称
exec sp_killspid 'db_name'2024-12-21