如何查mysql死锁进程

查询死锁进程

采用如下存储过程来查询数据中当前造成死锁的进程。

drop procedure sp_who_lock

go

CREATE procedure sp_who_lock

as

begin

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

if @@error<>0 return @@error

if @count=0

begin

select '没有阻塞和死锁信息'

return 0

end

while @index<<A href="mailto:=@count">=@count

begin

if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<<A href="mailto:=@index">=@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<<A href="mailto:=@count">=@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、通过存储过程杀掉某个库下面的所有死锁进程和锁

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'

假如发生了死锁，我们怎么去检测具体发生死锁的是哪条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

与锁定有关的两个问题－－死锁和阻塞

---