mysql死锁场景整理

本文死锁场景皆为工作中遇到（或同事遇到）并解决的死锁场景，写这篇文章的目的是整理和分享，欢迎指正和补充，本文死锁场景包括：

创新互联公司专业为企业提供阿合奇网站建设、阿合奇做网站、阿合奇网站设计、阿合奇网站制作等企业网站建设、网页设计与制作、阿合奇企业网站模板建站服务，10年阿合奇做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

注 ：以下场景隔离级别均为默认的Repeatable Read；

前提 ：表 t_user 的 uid 字段创建了唯一索引，并拥有可更新字段age。

场景复现 ：

相应业务案例和解决方案 ：

该场景常见于事务中存在for循环更新某条记录的情况，死锁日志显示 lock_mode X locks rec but not gap waiting （即行锁而非间隙锁），解决方案：

表结构 :

场景复现 ：

首先查询表中目前存在的记录：

执行两个事务的操作：

死锁原因分析 ：

解决方案 ：

t_user结构改造为：

场景复现操作（几率不高） ：

假设存在以下数据 ：

死锁分析 ：

事务1 ：

① 锁住zone_id=1对应的间隙锁: zoneId in (1,2)

② 锁住索引zone_id=1对应的主键索引行锁id = [1,2]

③ 锁住uid=1对应的间隙锁: uid in (1, 2)

④ 锁住uid=1对应的主键索引行锁: id = [1, 3]

事务2 ：

① 锁住zone_id=2对应的间隙锁: zoneId in (1,2)

② 锁住索引zone_id=2对应的主键索引行锁id = [3,4]

③ 锁住uid=2对应的间隙锁: uid in (1, 2)

④ 锁住uid=2对应的主键索引行锁: id = [2, 4]

解决方案 ：创建联合索引，使执行计划只会用到一个索引。

测试表结构 :

场景复现操作 ：

解决办法：尽量避免这种插入又回滚的场景。

避免死锁的原则：

mysql解决死锁问题

官方定义如下：两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

这个就好比你有一个人质，对方有一个人质，你们俩去谈判说换人。你让对面放人，对面让你放人。

看到这里，也许你会有这样的疑问，事务和谈判不一样，为什么事务不能使用完锁之后立马释放呢？居然还要操作完了之后一直持有锁？这就涉及到 MySQL 的并发控制了。

MySQL的并发控制有两种方式，一个是 MVCC，一个是两阶段锁协议。那么为什么要并发控制呢？是因为多个用户同时操作 MySQL 的时候，为了提高并发性能并且要求如同多个用户的请求过来之后如同串行执行的一样（ 可串行化调度 ）。具体的并发控制这里不再展开。咱们继续深入讨论两阶段锁协议。

官方定义：

对应到 MySQL 上分为两个阶段：

就是说呢，只有遵循两段锁协议，才能实现 可串行化调度 。

但是两阶段锁协议不要求事务必须一次将所有需要使用的数据加锁，并且在加锁阶段没有顺序要求，所以这种并发控制方式会形成死锁。

MySQL有两种死锁处理方式：

由于性能原因，一般都是使用死锁检测来进行处理死锁。

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

MySQL如何处理死锁

mysql 查看死锁

第一步，查出已锁的进程

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

``

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

``

INNODB_TRX表主要是包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务

select * from information_schema.innodb_trx

``

第二步，kill进程

show engin innodb status; //最后一次死锁信息及sql

show open tables where in_use 0 //查看锁表

如何查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 @@error0 return @@error

insert into #temp_who_lock(spid,blk)

select 0 ,blocked

from (select * from master..sysprocesses where blocked0)a

where not exists(select * from master..sysprocesses where a.blocked =spid and blocked0)

union select spid,blocked from master..sysprocesses where blocked0

if @@error0 return @@error

select @count=count(*),@index=1 from #temp_who_lock

if @@error0 return @@error

if @count=0

begin

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

return 0

end

while @indexA 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 idA 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 @indexA 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'

如何查看MySQL数据库的死锁信息

查看MySQL数据库的死锁日志

1. 使用终端或命令提示符登录到MySQL，输入命令：mysql -h xxxx.xxx.xxx -P 3306 -u username -p 解释：xxxx.xxx.xxx是数据库IP地址，username是数据库用户名，输入命令后，会让你输入username对应的密码，就可以登录了

2. 如何查看MySQL数据库的死锁信息 在MySQL客户端下输入命令： show engine innodb status \G;

3. 如何定位MySQL数据库的死锁信息 在打印出来的信息中找到“LATEST DETECTED DEADLOCK”一节内容，看图中红线

4. 如何分析日志，定位死锁原因 看3里面的图，紫色划线部分 分析： 事务1，等待 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`，这个位置的X锁 事务2，持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`这个地方的S锁 事务2，等待这个地方的X锁 理论上这个事务2是可以提交的不会，死锁，但是这个事务日志只打印最后一部分死锁，信息，这里面隐含的条件是，事务1也持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`这个地方的S锁，这样，事务2不能加X锁，同时事务1也不能加X锁，产生死锁。

新闻标题：mysql死锁怎么排查 mysql死锁排查思路
标题链接：http://kswjz.com/article/hghcch.html