带你了解Mysql数据库死锁

最近在看一些东西sharding-jdbc,突然想写一下数据库死锁相关的问题。好像前者后者没什么必要关系哈。

关于数据库发生死锁可能很少人遇到过，也可能遇到了看到一个报错你就过去了（因Mysql有检测死锁机制），没当回事。我们今天来聊聊死锁。

死锁发生的两个必要条件

1.肯定在多条sql语句执行事务操作

2.肯定多个事务操作同一数据，并相互等待对方资源

如下图：

左图那两辆车造成死锁了吗？不是！右图四辆车造成死锁了吗？是！

我们mysql用的存储引擎是innodb，从日志来看，innodb主动探知到死锁，并回滚了某一苦苦等待的事务。问题来了，innodb是怎么探知死锁的？

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。

仅用上述方法来检测死锁太过被动，innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发

死锁检测是死锁发生时innodb给我们的救命稻草，我们需要它，但我们更需要的是避免死锁发生的能力，如何尽可能避免？这需要了解innodb中的锁。

InnoDB实现了两种类型的行锁

共享锁（S）:允许一个事务读取一行，阻止其他事务获取相同数据集的排他锁

排他锁（X）:允许获得到排他锁的事务更新数据，但是阻止其他事务获取相同数据集的共享锁和排他锁

用人话理解的话就是：

共享锁就是我读的时候，你可以读，但是不能写。排他锁就是我写的时候，你不能写也不能读。其实就是MylSAM的读锁和写锁，但是针对的对象不同而已。

除此之外InnoDB还有两个表锁

意向共享锁（IS）:表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁之前必须先取得该表的IS锁

意向排他锁（IX）:表示事务准备给数据行加入排他锁，必须先取得该表的I锁

InnoDB行锁模式兼冲突

请求锁模式（行） 当前锁模式（列）	S	IS	X	IX
S	兼容	兼容	冲突	冲突
IS	兼容	兼容	冲突	兼容
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容

注意：

当一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之如果请求不兼容，则该事务就等待锁释放。

意向锁是InnoDB自动加的，不需要用户干预。

对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；对于一般的select语句（非事务），InnoDB不会加任何锁。

案例分析

假设我们有一张数据表 user  包含3个字段 id 是主键 、 name是非唯一主键  、age是普通字段

innodb对于主键使用了聚簇索引，这是一种数据存储方式，表数据是和主键一起存储，主键索引的叶结点存储行数据。对于普通索引，其叶子节点存储的是主键值。

1) delete from user where id =2

由于id是主键则直接锁住一行

2) delete from user where name = 'gaoxing'

由于name为二级索引(普通索引)，首先锁住二级索引两行，然后锁住聚族索引两行。

3) delete from user where age=30

因为age没有索引所以走的是全表扫描过滤条件，这是表上的所有记录都会添加X锁。

死锁是怎样发生的？

大学数据库课大家应该都学过(没上过大学更要装作学过一样/手动滑稽)，为了保证并发操作数据的正确性，数据库都有数据隔离的概念，1）未提交读（Read uncommitted）；2）已提交读（Read committed（RC））；3）可重复读（Repeatable read（RR））；4）可串行化（Serializable）。我们较常使用的是RC和RR。

提交读(RC)：只能读取到已经提交的数据。RC会出现幻读(有的人也叫脏读，你乐意咋读就咋读明白就行),有缺点就有优点，优点就是可以解决不同事务提交数据版本不一致的问题。（和人一样，技术牛逼的一般都没有那个女朋友)

可重复读(RR)：在同一个事务内的查询都是事务开始时刻一致的，Mysql 的InnoDB默认级别。

举例：下图你会发现事务A第一次查询是一条数据，因为数据隔离级别为RC,事务B插入数据已经提交，第二次查询发现两条数据，吓人不 刺激不 这就是幻读或脏读或你爱咋读咋读。

innoDB的RR是如何避免脏读的呢？当然要借助锁了。为了解决幻读，innoDB引入了gap锁

在执行事务A的时候，不仅给当前行添加X锁，还在非唯一索引上添加name 相邻的两个索引区间添加gap锁。

在事务B执行insert into values (null,wangmingli,50) commit提交的时候会检查这个区间是否被锁上，如果被锁上，不能立即执行，需要等待gap锁释放。当事务A提交后gap锁就会释放，事务Binsert就可以进行了。推荐一篇好文，可以深入理解锁的原理：http://hedengcheng.com/?p=771#_Toc374698322

了解了innodb锁的基本原理后，下面分析下死锁的成因。如前面所说，死锁一般是事务相互等待对方资源，最后形成环路造成的。下面简单讲下造成相互等待最后形成环路的例子。

1.不同表相同记录的行锁冲突，事务A和事务B出现循环等待的情况

2.相同表记录行锁冲突,事务A要修改1,2  ，事务B要修改2,1

3.不同索引锁冲突加顺序不同，事务A是1-2-4，事务B是4-2-1，这就有造成死锁的可能性。

 

4.gap锁，道理相同 自己画下就知道了。

如何尽量避免死锁，重点来了

1）以固定的顺序访问表和行。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形，将两个事务的sql顺序调整为一致，也能避免死锁。

2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

如何定位死锁原因

1）通过应用业务日志定位到问题代码，找到相应的事务对应的sql；

      因为死锁被检测到后会回滚，这些信息都会以异常反应在应用的业务日志中，通过这些日志我们可以定位到相应的代码，并把事务的sql给梳理出来。

此外，我们根据日志回滚的信息发现在检测出死锁时这个事务被回滚。

2）确定数据库隔离级别。

     执行select @@global.tx_isolation，可以确定数据库的隔离级别，我们数据库的隔离级别是RC，这样可以很大概率排除gap锁造成死锁的嫌疑;

3）找DBA执行下show InnoDB STATUS看看最近死锁的日志。

     这个步骤非常关键。通过DBA的帮忙，我们可以有更为详细的死锁信息。通过此详细日志一看就能发现，与之前事务相冲突的事务结构如下。