MySQL锁篇

1 MySQL锁介绍

011.png

2 MySQL表级锁

2.1 表级锁介绍

​ 表级锁由SQL layer实现。MySQL的表级锁有两种：一种是表锁；一种是元数据锁（meta data lock，MDL)。
​ MySQL 实现的表级锁的争用状态变量：

012.png

​ -table_locks_immediate：产生表级锁的次数；
​ -table_locks_waited：出现表级锁争用而发生等待的次数；

2.1.1 表锁介绍

表锁有两种表现形式：表共享读锁（Table Read Lock）、表独占写锁（Table Write Lock）

手动增加表锁
lock table 表名称 read(write),表名称2 read(write)，其他;

查看表锁情况
show open tables;

删除表锁
unlock tables;

2.2.2 表锁演示

2.2.2.1 环境准备

-- 新建表
CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id)
);
INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c'); 
INSERT INTO mylock (id,NAME) VALUES (4, 'd');

2.2.2.2 表读锁

013.png

session1：lock table mylock read; -- 给mylock表加读锁
session1：select * from mylock; -- 可以查询
session1：select * from tdep; -- 不能访问非锁定表
session2：select * from mylock; -- 可以查询 没有锁
session2：update mylock set name='x' where id=2; -- 修改阻塞,自动加行写锁
session1：unlock tables; -- 释放表锁
session2：Rows matched: 1    Changed: 1  Warnings: 0 -- 修改执行完成
session1：select * from tdep; -- 可以访问

2.2.2.3 表写锁

014.png

session1: lock table mylock write; -- 给mylock表加写锁
session1: select * from mylock; -- 可以查询
session1：select * from tdep; -- 不能访问非锁定表
session1：update mylock set name='y' where id=2; -- 可以执行
session2：select * from mylock; -- 查询阻塞
session1：unlock tables; -- 释放表锁
session2：4 rows in set (22.57 sec) -- 查询执行完成
session1：select * from tdep; -- 可以访问

2.2 元数据锁

2.2.1 元数据锁介绍

​ 在 MySQL 5.5 版本中引入了 MDL(metaDataLock 元数据锁) ，当对表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

2.2.2 元数据锁演示

015.png

session1: begin;-- 开启事务
session1: select * from mylock;-- 加MDL读锁
session2: alter table mylock add f int; -- 修改阻塞
session1：commit; -- 提交事务或者rollback释放读锁
session2：Query OK, 0 rows affected (38.67 sec)  --修改完成Records: 0    Duplicates: 0   Warnings: 0

3 MySQL行级锁

3.1 行级锁介绍

行级锁由InnoDB存储引擎实现

3.2 行级锁分类

3.2.1 按照锁定范围分类

​ ①记录锁（Record Locks）: 单个行记录上的锁。
​ ②间隙锁（Gap Locks）: 间隙锁，锁定一个范围，但不包含记录本身。
​ ③临键锁（Next-Key Locks） : 记录锁 + 间隙锁，锁定一个范围，并且锁定记录本身。

记录锁(Record Lock)：

​ 事务加锁后锁住的只是表的某一条记录。

记录锁出现条件：

​精准条件命中，并且命中的条件字段是唯一索引；
例如：update user_info set name=’张三’ where id=1 ,这里的id是唯一索引。
​Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

记录锁的作用：

​加了记录锁之后可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

间隙锁(gap lcok )：

​ 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
​ 很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

间隙锁的出现条件（RR事务隔离级别下）：

​ 使用普通索引锁定；
​ 使用多列唯一索引；
​ 使用唯一索引锁定多行记录。

间隙锁的作用：

​ 防止幻读，以满足相关隔离级别的要求；
​ 满足恢复和复制的需要：

临键锁(Next-Key Lock)

​ 临键锁是INNODB的行锁默认算法，它是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

临键锁出现条件：

​ 范围查询并命中，查询命中了索引。

临键锁的作用：

​ 结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。
​ Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。
​ 除了Next-Key Locking，还有Previous-Key Locking技术。

3.2.2 按照功能分类

①共享读锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

select * from table_name WHERE ... lock in share mode   -- 共享读锁 手动添加
select * from table_name    -- 无锁

②排他写锁（X）：允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
a.自动加

对于UPDATE、DELETE和INSERT的DML语句，InnoDB会自动给涉及数据集加排他锁（X)；

​ b.手动加

select * from table_name where ... for update

​ InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
​ 由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下所示：

	共享锁（S）	排他锁（X）	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	兼容	冲突	兼容	冲突
排他锁（X）	冲突	冲突	冲突	冲突
意向共享锁（IS）	兼容	冲突	兼容	兼容
意向排他锁（IX）	冲突	冲突	兼容	兼容

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

意向锁到底有什么作用？
意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该表某些记录上面是否有行锁。
innodb的意向锁主要用于多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。

​ 举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

​ 主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”

3.3 两阶段锁（2PL）

​ 锁操作分为两个阶段，加锁阶段与解锁阶段， 加锁阶段与解锁阶段不相交。加锁阶段只加锁不放锁；解锁阶段只放锁不加锁。

016.png

3.4 行锁演示

​ InnoDB行锁是通过给索引上的索引项加锁来实现的。InnoDB这种行锁实现特点意味着只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

3.4.1 行读锁

​ Innodb_row_lock_current_waits： 当 前 正 在 等 待 锁 定 的 数 量 ；
​ Innodb_row_lock_time： 从 系 统 启 动 到 现 在 锁 定 总 时 间 长 度 ；
​ Innodb_row_lock_time_avg： 每 次 等 待 所 花 平 均 时 间 ；
​ Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
​ Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

-- 查看行锁状态   
show STATUS like 'innodb_row_lock%';
-- session1: 
begin; -- 开启事务未提交
select * from mylock where ID=1 lock in share mode; -- 手动加id=1的行读锁,使用索引
-- session2: 
update mylock set name='y' where id=2; -- 未锁定该行可以修改
update mylock set name='y' where id=1; -- 锁定该行修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 锁定超时
-- session1: 
commit; -- 提交事务或者rollback释放读锁
-- session2: 
update mylock set name='y' where id=1; -- 修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1  Warnings: 0

-- 注：使用索引加行锁 ，未锁定的行可以访问

3.4.2 行读锁升级为表锁

-- session1: 
begin; -- 开启事务未提交
select * from mylock where name='c' lock in share mode; -- 手动加name='c'的行读锁,未使用索引
-- session2：
update mylock set name='y' where id=2; -- 修改阻塞 未用索引行锁升级为表锁
-- session1: 
commit; -- 提交事务或者rollback释放读锁
-- session2：
update mylock set name='y' where id=2; -- 修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1  Warnings: 0

-- 注：未使用索引行锁升级为表锁

3.4.3 行写锁

-- session1: 
begin;--开启事务未提交
select * from mylock where id=1 for update; -- 手动加id=1的行写锁,
-- session2：
select * from mylock where id=2; -- 可以访问
-- session2: 
select * from mylock where id=1 ; -- 可以读 不加锁
-- session2:
select * from mylock where id=1 lock in share mode ; -- 加读锁被阻塞
-- session1：
commit; -- 提交事务或者rollback释放写锁
-- session2： -- 执行成功

-- 注：主键索引产生记录锁

3.4.4 间隙锁

环境准备：

mysql> create table news (id int, number int,primary key (id)); 
mysql> insert into news values(1,2);
......
-- 加非唯一索引
mysql> alter table news add index idx_num(number);

017.png

3.4.4.1 非唯一索引的等值

-- 非唯一索引的等值
-- session1:
start transaction ;
update news set number=3 where number=4; 
-- session2:
start   transaction ;
insert into news value(2,3);#（均在间隙内，阻塞） 
insert into news value(7,8);#（均在间隙外，成功）
insert into news value(2,8);#（id在间隙内，number在间隙外，成功） 
insert into news value(4,8);#（id在间隙内，number在间隙外，成功） 
insert into news value(7,3);#（id在间隙外，number在间隙内，阻塞） 
insert into news value(7,2);# (id在间隙外，number为上边缘数据，阻塞) 
insert into news value(2,2);#（id在间隙内，number为上边缘数据，阻塞） 
insert into news value(7,5);#（id在间隙外，number为下边缘数据，成功） 
insert into news value(4,5);#（id在间隙内，number为下边缘数据，阻塞）

结论：只要number（where后面的）在间隙里（2 3 4），不包含最后一个数（5），则不管id是多少都会阻塞。

3.4.4.2 主键索引范围

-- 主键索引范围
-- session1:
start transaction ;
update news set number=3 where id>1 and id <6;
-- session2:
start transaction ;
insert into news value(2,3);#（均在间隙内，阻塞） 
insert into news value(7,8);#（均在间隙外，成功）
insert into news value(2,8);#（id在间隙内，number在间隙外，阻塞）
insert into news value(4,8);#（id在间隙内，number在间隙外，阻塞） 
insert into news value(7,3);#（id在间隙外，number在间隙内，成功）
-- id无边缘数据，因为主键不能重复

结论：只要id（在where后面的）在间隙里(2 4 5)，则不管number是多少都会阻塞。

3.4.4.3 非唯一索引无穷大

-- 无穷大
-- session1:
start transaction ;
update news set number=3 where number=13 ;
-- session2:
start transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功) 
insert into news value(14,11);#( 阻 塞 ) 
insert into news value(15,12);#(阻塞)
-- 检索条件number=13,向左取得最靠近的值11作为左区间，向右取得无穷大作为右区间（由于没有记录），因此session1的间隙锁的范围（11，无穷大）

-- 注：非主键索引产生间隙锁，主键范围产生间隙锁

结论：id和number都在间隙里会阻塞。

3.4.5 死锁

018.png

​ 两个 session 互相等待对方的资源释放之后能释放自己的资源，造成了死锁。

-- session1: 
begin; -- 开启事务未提交
update mylock set name='m' where id=1; -- 手动加行写锁 id=1 ，使用索引
-- session2：
begin; -- 开启事务未提交
update mylock set name='m' where id=2; -- 手动加行写锁 id=2 ，使用索引
-- session1: 
update mylock set name='nn' where id=2; -- 加写锁被阻塞
-- session2：
update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

3.5 行锁原理分析

3.5.1 简单SQL的加锁分析

3.5.1.1 下面SQL语句加锁么，加的是什么锁？

​ SQL1：select * from t1 where t1.id = 1;
​ SQL2：delete from t1 where id = 10;

3.5.1.2 谈锁前提

​ 前提一：当前系统的隔离级别是什么？
​ 前提二：id列是不是主键？
​ 前提三：id列如果不是主键，那么id列上有索引吗？
​ 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗

3.5.1.3 简单SQL1的加锁分析

​ 分析加锁需要考虑当前的隔离级别。在串行化隔离级别下，MVCC会降级成Lock-Based CC，是加锁的，加的是读锁。在其他三种隔离级别下，由于MVCC的快照读，所以是不加锁的。

3.5.1.4 简单SQL2的加锁分析

组合一：id主键+RC

如下图所示：

019.png

id是主键时，此SQL只需要在id=10这条记录上加写锁即可。

组合二：id唯一索引+RC

如下图所示：

020.png

过程如下：
由于id是唯一索引时，where会走id列的索引进行过滤，在找到id=10的记录后对唯一索引id=10的记录加X锁。
同时会回表查询主键索引name=d的数据，并对name=d的数据也加上X锁
为什么唯一索引与主键索引都要加上锁？
试想一下，如果在删除的过程中并发了一条更新语句，where用的是name作为条件，那么如果删除操作没有对主键索引加锁，那么更新语句是感知不到删除语句的存在从而进行更新，这违背了同一记录上的写操作需要串行化执行的原则

组合三：id非唯一索引+RC

如下图所示：

021.png

过程如下：
对ID索引中符合条件的数据加上X锁。
再把对应的主键索引上的数据也加上X锁。

与组合二的区别在于ID是非唯一索引会对满足条件的多条数据都加上X锁。而组合二只会对一条数据加上X锁

组合四：id无索引+RC

如下图所示：

022..png

过程如下：
由于没有索引，所以走的是全表扫描，所以会对主键索引上每一条记录施加X锁。
为什么会对所有记录施加X锁，而不是表锁或者说符合条件的数据加X锁呢？
这是由于InnoDB的实现决定的。由于没有索引，无法在存储引擎层过滤（执行计划里的Using Where），所以存储引擎对每一条数据加锁后返回给SqlServer进行过滤。Sql Server在进行过滤的过程中对不满足条件的数据会立即执行unlock_row方法，把不满足条件的记录放锁（违背2PL的约束），这样做，保证了最后只有满足条件的数据持有X锁。但是对每条数据加锁的步骤是没法省略的。

组合五：id主键+RR

与组合一是相同的。
主键范围产生Gap

组合六：id唯一索引+RR

与组合二是相同的。

组合七：id非唯一索引+RR

如下图所示：

023.png

这一组合与RC级别区别就很大了。
RC级别下是允许幻读的出现。
而MySql在RR级别下是不允许幻读的出现的。
所以为了解决幻读的问题，InnoDB会施加间隙锁来解决这一问题（为防止幻读 （RR），不能在间隙insert）。
过程如下：
首先对ID索引中符合条件的数据施加X锁。
对符合条件施加X锁的数据前后间隙施加间隙锁。
对ID索引对应的主键索引的数据施加X锁。

​ 施加了间隙锁以后，数据的前后都不会插入新的数据，就可以保证两次当前读的结果完全一致。

组合八：id无索引+RR

如下图所示：

024.png

​ 这一点就非常恐怖了，由于没有索引所以会对全表加锁，还会对所有的间隙加锁。这个时候对于该表除了不加锁的快照读，其他所有的并发操作全部锁死。
​ 当然对于这一点与组合四类似，MySql也有自己的优化。与组合四：id无索引, RC类似，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加间隙锁。
​ semi-consistent read如何触发：要么是read committed隔离级别；要么是Repeatable Read隔离级别，同时设置了 innodb_locks_unsafe_for_binlog 参数。

3.5.2 复杂SQL语句的行锁分析

SQL：delete from t1 where pubtime>1 and pubtime<20 and userid='hdc' and comment is not NULL;

025.png

在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分？在这里直接给出分析后的结果：
index key：puptime > 1 and puptime < 20。此条件确定了idx_t1_pu上面的值。
index filter：userid = 'hdc' 。此条件，可以在idx_t1_pu索引上进行过滤，但不属于index key。
table filter：comment is not NULL。此条件，无法在idx_t1_pu索引上进行过滤，只能在聚簇索引上过滤。

​ 在where条件过滤时，先过滤index key（索引列为范围查询，起始条件为index First Key，截止条件为index Last key），再过滤index filter（索引列），最后过滤table filter（非索引列）。在ICP过程中，下推index filter。

分析完这些后我们看一下加锁情况（假定在Repeatable Read隔离级别下）：

026.png

从图中可以看出，在RR隔离级别下，
​ 由Index Key所确定的范围，被加上了间隙锁；
​ Index Filter锁给定的条件 (userid = ‘hdc’)何时过滤，视MySQL的版本而定。
​ 在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层 过滤，
​ 在MySQL 5.6版本之后，支持了Index Condition Pushdown(ICP)，则在index key上过滤。
​ 若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁；
​ 若支持ICP，则不满足Index Filter的记录，无需加记录X锁 ；
​ 图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定。
​ 而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。

​ 最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

结论：

​ 在RR隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。
​ Index Key确定的范围，需要加上GAP锁；
​ Index Filter过滤条件，视MySQL版本是否支持ICP而定；若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；
​ Table Filter过滤条件，无论是否满足，都需要加X锁，加锁的数量，要远远大于满足条件的记录数量。

3.6 死锁原理分析

本文前面的部分，基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁，有两个比较重要的作用：
可以根据MySQL的加锁规则，写出不会发生死锁的SQL；
可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

3.6.1 死锁是怎么被发现的？

3.6.1.1 死锁成因&&检测方法

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

027.png

    我们mysql用的存储引擎是innodb，从日志来看，innodb主动探知到死锁，并回滚了某一苦苦等待的事务。问题来了，innodb是怎么探知死锁的？
    直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。
    仅用上述方法来检测死锁太过被动，innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发

3.6.1.2 wait-for graph原理

028.png

    我们怎么知道上图中四辆车是死锁的？他们相互等待对方的资源，而且形成环路！我们将每辆车看为一个节点，当节点1需要等待节点2的资源时，就生成一条有向边指向节点2，最后形成一个有向图。我们只要检测这个有向图是否出现环路即可，出现环路就是死锁！这就是wait-for graph算法。
    innodb将各个事务看为一个个节点，资源就是各个事务占用的锁，当事务1需要等待事务2的锁时，就生成一条有向边从1指向2，最后行成一个有向图。

3.6.2 innodb隔离级别、索引与锁

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

3.6.2.1 锁与索引的关系

​ 假设我们有一张消息表（msg），里面有3个字段。假设id是主键，token是非唯一索引，message没有索引。

id	token	message
bigint	varchar(30)	varchar(4096)

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

029.png

下面分析下索引和锁的关系。

1）delete from msg where id=2；

由于id是主键，因此直接锁住整行记录即可。

030.png

2）delete from msg where token=’ cvs’;

由于token是二级索引，因此首先锁住二级索引（两行），接着会锁住相应主键所对应的记录；

031.png

3）delete from msg where message=订单号是多少’；

message没有索引，所以走的是全表扫描过滤。这时表上的各个记录都将添加上X锁。

032.png

3.6.2.2 锁与隔离级别的关系

​ 我们在3.6.2.1节谈论的其实是RC隔离级别下的锁，它可以防止不同事务版本的数据修改提交时造成数据冲突的情况，但当别的事务插入数据时可能会出现问题。
​ 如下图所示，事务A在第一次查询时得到1条记录，在第二次执行相同查询时却得到两条记录。从事务A角度上看是见鬼了！这就是幻读，RC级别下尽管加了行锁，但还是避免不了幻读。

033.png

​ innodb的RR隔离级别可以避免幻读发生，怎么实现？当然需要借助于锁了！
​ 为了解决幻读问题，innodb引入了gap锁。
​ 在事务A执行：update msg set message=‘订单’ where token=‘asd’;
​ innodb首先会和RC级别一样，给索引上的记录添加上X锁，此外，还在非唯一索引’asd’与相邻两个索引的区间加上锁。
​ 这样，当事务B在执行insert into msg values (null,‘asd',’hello’); commit;时，会首先检查这个区间是否被锁上，如果被锁上，则不能立即执行，需要等待该gap锁被释放。这样就能避免幻读问题。

034.png

3.6.3 死锁成因

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

3.6.3.1不同表相同记录行锁冲突

​ 这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况。

035.png

3.6.3.2相同表记录行锁冲突

​ 这种情况比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。

036.png

3.6.3.3不同索引锁冲突

​ 这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[1,2,3,4,5]，这样就造成了死锁的可能性。

037.png

3.6.3.4 gap锁冲突

​ innodb在RR级别下，如下的情况也会产生死锁，比较隐晦。不清楚的同学可以自行根据上节的gap锁原理分析下。

038.png

3.6.4 如何尽可能避免死锁

​ 1）以固定的顺序访问表和行。比如对于第3.6.3.2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.6.3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。
​ 2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
​ 3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
​ 4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
​ 5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

3.6.5 如何定位死锁成因

​ 1）通过应用业务日志定位到问题代码，找到相应的事务对应的sql；
​ 因为死锁被检测到后会回滚，这些信息都会以异常反应在应用的业务日志中，通过这些日志我们可以定位到相应的代码，并把事务的sql给梳理出来。
​ 2）确定数据库隔离级别。
​ 执行select @@global.tx_isolation，可以确定数据库的隔离级别。如果数据库的隔离级别是RC，这样可以很大概率排除gap锁造成死锁的嫌疑;
​ 3）找DBA执行下show InnoDB STATUS看看最近死锁的日志。
​ 这个步骤非常关键。通过DBA的帮忙，我们可以有更为详细的死锁信息。