Mysql之Innodb锁模式和死锁解析

男娘i 2022-02-26 00:26 423阅读 0赞

# 一. 背景知识 #

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。由于后面的死锁问题基本都是并发事务的前提下发生，因此我们先了解一下事务的相关知识。

### 1、事务的ACID属性 ###

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。  
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这里的“一致” 包含两个层面，一个是数据库层面的数据完整性约束，比如所有的内部数据结构（如B树索引或双向链表）必须是正确的。另一个层面是应用层的逻辑约束，比如银行的转账一方加了100元，那么另一方必须减100元（数额不能多也不能少）。  
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。  
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

### 2、并发事务处理带来的问题 ###

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。  
不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变，这种现象就叫做“不可重复读”。  
幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

### 3、事务的隔离级别 ###

上述并发问题中，“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。ISO/ANSI SQL92定义了4个事务隔离级别：

未提交读（Read Uncommitted）：最低的隔离级别，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。  
已提交读（Read Committed）：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题，但两次读取同一份数据可能看到不同的结果（存在不可重复读问题）。  
可重复读（Repeated Read）：在一个事务中，对于同一份数据的读取结果总是相同的（快照读，通过数据多版本并发控制实现，简称MVCC，也就是对当前的数据Copy一个版本，以后的读操作从这个版本中读取），无论是否有其他事务对数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读问题。  
注意：存在幻读问题，可重复读只是针对普通的查询语句，1、若查询语句显示的加上锁如，lock in share mode或者for update，则此次查询会放弃Copy版本（快照读），采用当前最新版本（当前读），从而可以读到其它事务提交后（包括更新和插入）的最新数据；2、若当前事务恰好更新了本事务内的数据和其它事务提交后的数据，那么被更新的数据会同步到Copy版本（更新快照），因此之后的查询均可查询到被更新的数据。  
序列化（Serializable）：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。

# 二、锁模式 #

### 1、意向锁 ###

InnoDB默认使用行锁，行锁是对索引加的锁，而不是针对记录加的锁。因此，在不通过索引条件查询的时候，使用的是表锁。不管是表锁还是行锁，都有如下两种锁模式：

共享锁（S）：允许一个事务去读数据，阻止其他事务获得相同数据集的排他锁。  
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了提高表锁和行锁冲突检测效率，InnoDB提供了如下两种意向锁（Intention Locks），这两种意向锁都是表锁，是InnoDB在获取行锁前自动加上的：

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。  
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。那么锁与锁的“冲突-兼容”规则是怎样的呢？

### 2、“冲突-兼容”规则 ###

引用某知乎的图片如下。

![在这里插入图片描述][20190328153051259.png]

思考1：意向锁是怎么提高检测效率的？  
答案：在加表锁的时可立即检测到是否存在意向锁，若存在，则说明有行锁，因此无须遍历整个表即可获得冲突结果。

思考2：为什么IX 与 X会产生冲突？与行级锁矛盾？  
答案：IX，IS是表级锁，不会和行级的X，S锁发生冲突，锁的冲突是按级分开的。

### 3、加锁机制 ###

InnoDB支持行锁、间隙锁、插入间隙意向锁和后码锁：

1）行锁（Record Lock）  
行锁是加在某一行数据对应的索引上，对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集索引加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁，因此也无需等待锁（Create Table And Select, CATS 语句除外，CATS中的查询语句会隐式加上共享锁，原因是为了保证主从复制的正确性）；普通SELECT可通过如下形式显示给数据集索引加共享锁或排他锁：

共享锁（S）：SELECT \* FROM table\_name WHERE … LOCK IN SHARE MODE  
排他锁（X）：SELECT \* FROM table\_name WHERE … FOR UPDATE

注意：即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析加锁机制时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。比如，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。

2）间隙锁（Gap Lock）  
在事务隔离级别为可重复读的情况下，InnoDB还会出现间隙锁（Gap Lock），间隙锁是指加在索引之间的锁（开区间，不包括索引记录本身）。

由于可以阻止其它事务在索引间隙中插入新数据，因此，间隙锁可避免一部分幻读的产生，当然也可以通过如下语句来关闭间隙锁：  
set innodb\_locks\_unsafe\_for\_binlog = ‘on’

我们可通过如下范围查询或者查询不存在记录来模拟间隙锁：

语句1：SELECT \* FROM table\_name WHERE index > 100 LOCK IN SHARE MODE （假设表中只有index为1,2,…101的记录, 那么会锁定101这条记录的索引以及大于101的索引间隙）  
语句2：UPDATE table\_name SET … WHERE index = 103 （假设表中只有index为1,2,…101,105,106的记录, 那么会锁定101至105之间的索引间隙）

注意：间隙锁没有区分排他和共享模式，并且只在UPDATE、DELETE以及特殊的SELECT语句中出现（INSERT语句采用了另外一种锁——插入意向间隙锁）

3）插入意向间隙锁（Insert Intention Gap Lock）  
在InnoDB中，INSERT语句会在插入记录前，在记录索引所在的间隙加上一个插入意向间隙锁（Insert Intention Gap Lock），插入成功后会将插入意向间隙锁转换为对应记录索引上的排它锁。比如有3和5这两条记录，现要插入4这条记录，那么就会在索引区间（3,5）加Insert Intention Gap Lock，插入成功后会对4这条记录的索引加X Lock。

由于插入意向间隙锁是兼容的，因此在多事务同时写入不同数据至同一索引间隙（甚至是同一个非唯一索引）的时候，并不会发生锁等待，从而提高并发插入性能。

4）后码锁（ Next-Key Lock）  
后码锁（Next-Key Lock）就是行锁（Record Lock）和间隙锁（Gap Lock）的结合体，它锁定记录索引本身以及记录索引的前后两个间隙。在事务隔离级别为可重复读且间隙锁开启的情况下， InnoDB会默认使用Next-Key Lock ，示例如下：

SELECT \* FROM table\_name WHERE index = 100 FOR UPDATE（假设表中存在index为95、100、105的记录, 那么会锁定100这条记录的索引以及（95,100）和（100,105）两个索引间隙）

注意：当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为行锁，即仅锁住索引本身，而不是范围。

### 4、“冲突-兼容”规则2 ###

考虑插入意向锁后的冲突规则为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_20_color_FFFFFF_t_70]

# 三、死锁 #

1、死锁模型  
死锁一般是两个或两个以上事务相互等待对方释放锁，形成死循环所造成的，如下图所示。

2、可能的几种死锁  
1）多个事务按不同的顺序锁定相同的数据集导致的死锁  
如果多个事务按不同的顺序锁定相同的数据集，此时事务之间就会形成循环等待造成死锁，这是一种最常见也比较容易理解的死锁。

2）索引不合理导致的死锁  
由于InnoDB的锁是加在索引上的，因此索引不合理将直接导致锁定范围增大，发生锁冲突和死锁的的概率也随着增加。如果对索引和加锁机制不太了解的话，就很难定位死锁的原因。

3）插入意向间隙锁与间隙所冲突导致的死锁  
当多个事务持有间隙锁，且事务相互插入对方锁定的索引间隙时，便造成了死锁。如图所示。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_20_color_FFFFFF_t_70 1]

4）唯一键值冲突导致的死锁  
这个场景主要发生在三个或三个以上的事务同时进行唯一键值相同的记录插入操作，如图所示。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_20_color_FFFFFF_t_70 2]

3、死锁预防策略  
1）减少事务操作的记录数  
2）合理设置索引（索引的粒度为一条记录）  
3）对事务中要操作的记录进行排序  
4）避免使用唯一键值约束

# 4、补充知识 #

1）查看事务隔离级别  
SELECT @@tx\_isolation;

2）设置事务隔离级别  
SET \[SESSION | GLOBAL\] TRANSACTION ISOLATION LEVEL \{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE\}

3）查看锁等待状态  
SELECT \* FROM information\_schema.innodb\_locks;

4）查看innodb状态(包含最近的死锁日志)  
show engine innodb status;

[20190328153051259.png]: /images/20220226/69f878fef3c14d60845283b3b84ee3d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_20_color_FFFFFF_t_70]: /images/20220226/df3a8cfd24234502ba511e7cedf1f6f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_20_color_FFFFFF_t_70 1]: /images/20220226/9e41887e69da4ffdb4f11362a5baec60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyMzM4OTYy_size_20_color_FFFFFF_t_70 2]: /images/20220226/f6ea93c547eb47c3a91f1d25cede2ac9.png