MySQL——InnoDB行锁

傷城~ 2022-03-10 08:52 259阅读 0赞

### InnoDB行锁 ###

*   *  先从两阶段锁协议说起
     *  InnoDB的行锁模式及加锁方法
     *  InnoDB行锁实现方式
     *   *  Next-Key锁
         *  相关知识点
     *  什么时候使用表锁
     *  死锁和死锁检测
     *  总结

可以通过检查InnoDB\_row\_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

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

## 先从两阶段锁协议说起 ##

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

我先给你举个例子。在下面的操作序列中，事务 B 的 update 语句执行时会是什么现象呢？假设字段 id 是表 t 的主键。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70]  
这个问题的结论取决于事务 A 在执行完两条 update 语句后，持有哪些锁，以及在什么时候释放。  
实际上事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。  
**事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的。**

也就是说，在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是**两阶段锁协议**。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

## InnoDB的行锁模式及加锁方法 ##

InnoDB实现了以下两种类型的行锁。

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

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的**意向锁（Intention Locks）**，这两种意向锁都是表锁。

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70 1]  
如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

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

对于UPDATE、DELETE和INSERT语句， InnoDB会自动给涉及数据集加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁；

事务可以通过以下语句显示给记录集加共享锁或排他锁。  
共享锁（S）：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

排他锁（X）：

SELECT * FROM table_name WHERE ... FOR UPDATE;

用`SELECT ... IN SHARE MODE`获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行`UPDATE`或者`DELETE`操作。  
但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，**对于锁定行记录后需要进行更新操作的应用**，应该使用`SELECT... FOR UPDATE`方式获得排他锁。

## InnoDB行锁实现方式 ##

**InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁**。InnoDB行锁分为3种情形。

*  Record lock：对索引项加锁。
 *  Gap lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。
 *  Next-key lock：前两种的组合，对记录及其前面的间隙加锁。

InnoDB这种行锁实现特点意味着：*如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样！*  
（1）在不通过索引条件查询时，InnoDB会锁定表中的所有记录。

（2）由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，**如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB也会对所有记录加锁**。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

### Next-Key锁 ###

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，**InnoDB也会对这个“间隙”加锁**，这种锁机制就是所谓的Next-Key锁。

举例来说，假如emp表中只有101条记录，其empid的值分别是1、2、…、100、101，下面的SQL：

Select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。 **InnoDB使用Next-Key锁的目的，是为了防止幻读**，以满足相关隔离级别的要求。

范围条件加锁时使用Next-Key锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用Next-Key锁！

### 相关知识点 ###

1.  innodb对于行的查询使用next-key lock
2.  Next-locking keying为了解决Phantom Problem幻读问题
3.  当查询的索引含有唯一属性时，将next-key lock降级为record key
4.  Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
5.  有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. *将事务隔离级别设置为RC* B.*将参数innodb\_locks\_unsafe\_for\_binlog设置为1*

## 什么时候使用表锁 ##

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们选择InnoDB表的理由。  
但在个别特殊事务中，也可以考虑使用表级锁。

*  第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
 *  第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

## 死锁和死锁检测 ##

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70 2]  
这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

*  一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb\_lock\_wait\_timeout 来设置。
 *  另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数  
    innodb\_deadlock\_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，`innodb_lock_wait_timeout` 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。

所以，正常情况下我们还是要采用第二种策略，即：**主动死锁检测**，而且 `innodb_deadlock_detect` 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

问题的症结在于，**死锁检测要耗费大量的 CPU 资源**。

一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

另一个思路是**控制并发度**。根据上面的分析，如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。

因此，**这个并发控制要做在数据库服务端**。如果你有**中间件**，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在**进入引擎之前排队**。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

## 总结 ##

对于InnoDB表，本章主要讨论了以下几项内容。

1.  InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会对所有数据加锁。
2.  InnoDB Next-Key锁机制，以及 InnoDB使用Next-Key锁的原因。
3.  在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
4.  MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。
5.  锁冲突甚至死锁很难完全避免。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括以下几项：

*  尽量使用较低的隔离级别；
 *  精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
 *  选择合理的事务大小，小事务发生锁冲突的几率也更小；
 *  给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
 *  不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
 *  尽量用相等条件访问数据，这样可以避免Next-Key锁对并发插入的影响；
 *  不要申请超过实际需要的锁级别；
 *  除非必须，查询时不要显示加锁；
 *  对于一些特定的事务，可以使用表锁来提高处理速度或减少发生死锁的几率。

注：文章内容参考自

1.  极客时间《MySQL实战45讲》
2.  书籍《深入浅出MySQL》

[2019031014041820.png]: /images/20220310/827239712bac43cfb83711ca7f070d76.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70]: /images/20220310/8552ba2c94ba4f65930d355acb9693e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70 1]: /images/20220310/3456727f2a2b4e4593465caeda6f761f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjU4NjEyMA_size_16_color_FFFFFF_t_70 2]: /images/20220310/20b276afe9434f6cbc9f12f7e59ab1a6.png