Mysql 死锁问题

1.Mysql中的隔离级别RC与RR

1.1. 数据库事务ACID特性

数据库事务的4个特性：

原子性(Atomic): 事务中的多个操作，不可分割，要么都成功，要么都失败； All or Nothing.

一致性(Consistency): 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变；

隔离性(Isolation): 多个事务之间就像是串行执行一样，不相互影响;

持久性(Durability): 事务提交后被持久化到永久存储.

1.2. 隔离性

其中 隔离性 分为了四种：

READ UNCOMMITTED：可以读取未提交的数据，未提交的数据称为脏数据，所以又称脏读。此时：幻读，不可重复读和脏读均允许；

READ COMMITTED：只能读取已经提交的数据；此时：允许幻读和不可重复读，但不允许脏读，所以RC隔离级别要求解决脏读；

REPEATABLE READ：同一个事务中多次执行同一个select,读取到的数据没有发生改变；此时：允许幻读，但不允许不可重复读和脏读，所以RR隔离级别要求解决不可重复读；

SERIALIZABLE: 幻读，不可重复读和脏读都不允许，所以serializable要求解决幻读；

1.3. 几个概念

脏读：可以读取未提交的数据。RC 要求解决脏读；

不可重复读：同一个事务中多次执行同一个select, 读取到的数据发生了改变(被其它事务update并且提交)；

可重复读：同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现)；RR各级级别要求达到可重复读的标准；

幻读：同一个事务中多次执行同一个select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete/insert并且提交)。SERIALIZABLE要求解决幻读问题；

这里一定要区分 不可重复读 和 幻读：

不可重复读的重点是修改:

同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了

幻读的重点在于新增或者删除:

同样的条件的select, 第1次和第2次读出来的记录数不一样

从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大：

对于前者, 在RC下只需要锁住满足条件的记录，就可以避免被其它事务修改，也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读；

对于后者, 要锁住满足条件的记录及所有这些记录之间的gap，也就是需要 gap lock。

而ANSI SQL标准没有从隔离程度进行定义，而是定义了事务的隔离级别，同时定义了不同事务隔离级别解决的三大并发问题：

Isolation Level	Dirty Read	Unrepeatable Read	Phantom Read
Read UNCOMMITTED	YES	YES	YES
READ COMMITTED	YES	YES	YES
READ REPEATABLE	NO	NO	YES
SERIALIZABLE	NO	NO	NO

1.4. 数据库的默认隔离级别

除了MySQL默认采用RR隔离级别之外，其它几大数据库都是采用RC隔离级别。

但是他们的实现也是极其不一样的。Oracle仅仅实现了RC 和 SERIALIZABLE隔离级别。默认采用RC隔离级别，解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。

MySQL的实现：MySQL默认采用RR隔离级别，SQL标准是要求RR解决不可重复读的问题，但是因为MySQL采用了gap lock，所以实际上MySQL的RR隔离级别也解决了幻读的问题。那么MySQL的SERIALIZABLE是怎么回事呢？其实MySQL的SERIALIZABLE采用了经典的实现方式，对读和写都加锁。

1.5. MySQL 中RC和RR隔离级别的区别

MySQL数据库中默认隔离级别为RR，但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢？我们该如何选择呢？为什么MySQL将RR作为默认的隔离级别呢？

1.5.1 RC 与 RR 在锁方面的区别

1> 显然 RR 支持 gap lock(next-key lock)，而RC则没有gap lock。因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；

2> RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，即使不符合where条件的记录，也不会是否行锁和gap lock；所以从锁方面来看，RC的并发应该要好于RR；

1.5.2 RC 与 RR 在复制方面的区别

1> RC 隔离级别不支持 statement 格式的bin log，因为该格式的复制，会导致主从数据的不一致；只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。复制时，我们最好使用：binlog_format=row

2> MySQL5.6 的早期版本，RC隔离级别是可以设置成使用statement格式的bin log，后期版本则会直接报错；

1.5.3 RC 与 RR 在一致性读方面的区别

简单而且，RC隔离级别时，事务中的每一条select语句会读取到他自己执行时已经提交了的记录，也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时，事务中的一致性读的ReadView是以第一条select语句的运行时，作为本事务的一致性读snapshot的建立时间点的。只能读取该时间点之前已经提交的数据。

具体可以参加：MySQL 一致性读 深入研究

1.5.4 RC 支持半一致性读，RR不支持

RC隔离级别下的update语句，使用的是半一致性读(semi consistent)；而RR隔离级别的update语句使用的是当前读；当前读会发生锁的阻塞。

1> 半一致性读：

简单来说，semi-consistent read是read committed与consistent read两者的结合。一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足 update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。semi-consistent read只会发生在read committed隔离级别下，或者是参数innodb_locks_unsafe_for_binlog被设置为true(该参数即将被废弃)。

对比RR隔离级别，update语句会使用当前读，如果一行被锁定了，那么此时会被阻塞，发生锁等待。而不会读取最新的提交版本，然后来判断是否符合where条件。

半一致性读的优点：

减少了update语句时行锁的冲突；对于不满足update更新条件的记录，可以提前放锁，减少并发冲突的概率。

2.Mysql中的锁

2.1.Mysql都有什么锁

MySQL有三种锁的级别：表级、行级、页级。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

算法：

行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。

间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。

2.1.1 行锁（Record Lock）

行锁锁定的是索引记录，而不是行数据，也就是说锁定的是key。

2.1.2 间隙锁（Gap Lock）

例如：

create table test(id int,v1 int,v2 int,primary key(id),key idx_v1(v1))Engine=InnoDB DEFAULT CHARSET=UTF8;

该表的记录如下：

+—-+——+——+

| id | v1 | v2 |

+—-+——+——+

| 1 | 1 | 0 |

| 2 | 3 | 1 |

| 3 | 4 | 2 |

| 5 | 5 | 3 |

| 7 | 7 | 4 |

| 10 | 9 | 5 |

间隙锁（Gap Lock）一般是针对非唯一索引而言的，test表中的v1（非唯一索引）字段值可以划分的区间为：

（-∞,1）

（1,3）

（3,4）

（4,5）

（5,7）

（7,9）

（9, +∞）

假如要更新v1=7的数据行，那么此时会在索引idx_v1对应的值，也就是v1的值上加间隙锁，锁定的区间是（5,7）和（7,9）。同时找到v1=7的数据行的主键索引和非唯一索引，对key加上锁。

记录的GAP的区间如下：是一个左开右闭的空间（原因是默认主键的有序自增的特性）

2.1.3后码锁（Next-Key Lock）

记录锁和间隙锁的结合，对于InnoDB中，更新非唯一索引对应的记录（在这里来说是更新v1字段的值），会加上Next-Key Lock。如果更新记录为空，就不能加记录锁，只能加间隙锁。

在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks。所谓Next-Key Locks，就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

下面我们针对大部分的SQL类型分析是如何加锁的，假设事务隔离级别为可重复读。

select .. from

不加任何类型的锁

select…from lock in share mode

在扫描到的任何索引记录上加共享的（shared）next-key lock，还有主键聚集索引加排它锁

select..from for update

在扫描到的任何索引记录上加排它的next-key lock，还有主键聚集索引加排它锁

update..where delete from..where

在扫描到的任何索引记录上加next-key lock，还有主键聚集索引加排它锁

insert into..

简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。不过在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。想象一下，如果一个表有一个索引idx_test，表中有记录1和8，那么每个事务都可以在2和7之间插入任何记录，只会对当前插入的记录加record lock，并不会阻塞其他session插入与自己不同的记录，因为他们并没有任何冲突。

假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时，如果另外一个session已经获得改行的排它锁，那么将会导致死锁。

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围，按照这个方法是会和第一次测试结果一样。但是，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

2.1.4 意向锁（Next-Key Lock）

innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。
说白了意向锁的主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”

2.1.5 锁选择

1）如果更新条件没有走索引，例如执行”update from t1 set v2=0 where v2=5;” ，此时会进行全表扫描，扫表的时候，要阻止其他任何的更新操作，所以上升为表锁。

2）如果更新条件为索引字段，但是并非唯一索引（包括主键索引），例如执行“update from t1 set v2=0 where v1=9;” 那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因：

a）首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值；

b）还要保证锁定的区间不能插入新的数据。

3）如果更新条件为唯一索引，则使用Record Lock（记录锁）。

2.2.什么情况下会造成死锁

所谓死锁: 是指两个或两个以上的进程在执行过程中,
因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.
此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.
表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

3.Mysql中发生死锁的问题分析（默认RR隔离级别）

默认情况下，InnoDB存储引擎不会回滚超时引发的异常，除死锁外。

3.1 案例一

一个session通过for循环会有几条如下的语句：

Select * from xxx where id=’随机id’ for update

基本来说，程序开启后不一会就死锁。

这可以是说最经典的死锁情形了。

对于这个问题的改进很简单，直接把所有分配到的id直接一次锁住就行了。

Select * from xxx where id in (xx,xx,xx) for update

在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁

3.2 案例二

在开发中，经常会做这类的判断需求：根据字段值查询（有索引），如果不存在，则插入；否则更新。

展开代码
以id为主键为例，目前还没有id=2的行
​
Session1:
​
select * from test where id=2 for update;
​
Empty set (0.00 sec)
​

​
session2:
​
select * from test where id=3 for update;
​
Empty set (0.00 sec)
​

​
Session1:
​
insert into test values(2,’a’,’b’,now());
​
锁等待中……
​

​
Session2:
​
insert into test values(3,’b’,’c’,now());
​
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
select的where子句没有满足条件的记录，而对于不存在的记录 并且在RR级别下，select加锁类型为gap lock，gap lock之间是兼容的，所以两个事务都能成功执行select；关于gap lock可以参考文章加锁分析。

insert时，其加锁过程为先在插入间隙上获取插入意向锁，插入数据后再获取插入行上的排它锁。又插入意向锁与gap lock和 Next-key lock冲突，即一个事务想要获取插入意向锁，如果有其他事务已经加了gap lock或 Next-key lock，则会阻塞。

场景中两个事务都持有gap lock，然后又申请插入意向锁，此时都被阻塞，循环等待造成死锁。

\	Gap	Insert Intension	Record	Next-Key
Gap	兼容	兼容	兼容	冲突
Insert Intension	兼容	冲突	兼容	冲突
Record	兼容	兼容	冲突	冲突
Next-Key	兼容	兼容	冲突	冲突

当对存在的行进行锁的时候(主键)，mysql就只有行锁。

当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁）

锁住的范围详细可见2.1.2

对于这种死锁的解决办法是：

insert into t3(xx,xx) on duplicate key update xx=’XX’;

用mysql特有的语法来解决此问题。因为insert语句对于主键来说，插入的行不管有没有存在，都会只有行锁。

3.3 案例三

展开代码
mysql> select * from test where id=9 for update;
​
+—-+——–+——+———————+
​
| id | v1 | v2 | ctime |
​
+—-+——–+——+———————+
​
| 9 | a | b | 2018-08-05 11:36:30 |
​
+—-+——–+——+———————+
​
1 row in set (0.00 sec)
​

​
Session2:
​
mysql> select * from test where id<20 for update;
​
锁等待中
​

​
Session1:
​
mysql> insert into test values(7,’ae’,’af’,now());
​
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
这个跟案例一其它是差不多的情况，只是session1不按常理出牌了，

Session2在等待Session1的id=9的锁，session2又持了1到8的锁（注意9到19的范围并没有被session2锁住），最后，session1在插入新行时又得等待session2,故死锁发生了。

这种一般是在业务需求中基本不会出现，因为你锁住了id=9，却又想插入id=7的行，这就有点跳了，当然肯定也有解决的方法，那就是重理业务需求，避免这样的写法。