InnoDB 并发插入，如何使用自增锁和意向锁

傷城~ 2022-03-17 00:42 143阅读 0赞

并发的任务对同一个临界资源进行操作，如果不采取措施，可能导致不一致，故必须进行**并发控制**（Concurrency Control）。InnoDB 存储引擎中使用的多种并发控制策略，按照锁的粒度划分，可以分成行锁和表锁。

## 1. 并发控制 ##

并发控制保证数据一致性的常见手段有：**锁（Locking）**和**[数据多版本（Multi Versioning）][Multi Versioning]**。乐观锁和悲观锁其实都是并发控制的机制，同时它们在原理上就有着本质的差别；

*  乐观锁是一种思想，它其实并不是一种真正的『锁』，它会先尝试对资源进行修改，在写回时判断资源是否进行了改变，如果没有发生改变就会写回，否则就会进行重试，在整个的执行过程中其实都**没有对数据库进行加锁**；
 *  悲观锁就是一种真正的锁了，它会在获取资源前对资源进行加锁，确保同一时刻只有有限的线程能够访问该资源，其他想要尝试获取资源的操作都会进入等待状态，直到该线程完成了对资源的操作并且释放了锁后，其他线程才能重新操作资源；

### **1.1 乐观锁和悲观锁如何选择呢？** ###

乐观锁不会存在死锁的问题，但是由于更新后验证，所以当**冲突频率**和**重试成本**较高时更推荐使用悲观锁，而需要非常高的**响应速度**并且**并发量**非常大的时候使用乐观锁就能较好的解决问题，在这时使用悲观锁就可能出现严重的性能问题；在选择并发控制机制时，需要综合考虑上面的四个方面（冲突频率、重试成本、响应速度和并发量）进行选择。

## 2. 事务隔离级别 ##

ISO 和 ANIS SQL 标准制定了四种事务隔离级别，而 InnoDB 遵循了 SQL:1992 标准中的四种隔离级别：`READ UNCOMMITED`、`READ COMMITED`、`REPEATABLE READ` 和 `SERIALIZABLE`；每个事务的隔离级别其实都比上一级多解决了一个问题：

<table> 
 <tbody> 
  <tr> 
   <td>隔离级别</td> 
   <td>MVCC版本生成时机</td> 
   <td style="width:148px;">写操作释放锁的时机</td> 
   <td style="width:114px;">锁的范围</td> 
   <td>脏读</td> 
   <td>不可重复度</td> 
   <td>幻读</td> 
  </tr> 
  <tr> 
   <td>READ UNCOMMITTED</td> 
   <td>/</td> 
   <td style="width:148px;">SQL执行完立即释放</td> 
   <td style="width:114px;">行锁</td> 
   <td>√</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>READ COMMITTED</td> 
   <td>每次select时</td> 
   <td style="width:148px;">commit</td> 
   <td style="width:114px;">行锁</td> 
   <td>&nbsp;</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>REPEATABLE READ</td> 
   <td>事务第一次select时</td> 
   <td style="width:148px;">commit</td> 
   <td style="width:114px;">行锁或间隙锁</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>特定情况</td> 
  </tr> 
  <tr> 
   <td>SERIALIZABLE</td> 
   <td>事务第一次select时</td> 
   <td style="width:148px;">commit</td> 
   <td style="width:114px;">行锁或间隙锁</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

*  REPEATABLE READ的特定情况：可以防止大部分的幻读，但像这样的：读-写-读的情况，使用不加锁的select依然会幻读
 *  SERIALIZABLE时InnoDB 隐式地将全部的查询语句加上共享锁，解决了幻读的问题。

总之，不可重复读的重点是**修改，**幻读的重点在于**新增或者删除。**

## 3. **共享/排它锁** ##

简单的锁住太过粗暴，连“读任务”也无法并行，任务执行过程本质上是串行的。

对数据的操作其实只有两种，也就是读和写，而数据库在实现锁时，也会对这两种操作使用不同的锁；InnoDB 实现了**标准的行级锁**，也就是共享锁（Shared Lock）和互斥锁（Exclusive Lock）；共享锁和互斥锁的作用其实非常好理解：

*  共享锁（**S**hare Locks，记为S锁），读取数据时加S锁
 *  排他锁（e**X**clusive Locks，记为X锁），修改数据时加X锁

锁的兼容性上：

*  共享锁之间不互斥，即：读读可以并行
 *  排他锁与任何锁互斥，即：写读，写写不可以并行

所以我们可以在数据库中**并行读**，但是只能**串行写**，只有这样才能保证不会发生线程竞争，实现线程安全。共享/排它锁的潜在问题是，不能充分的并行，解决思路是**[数据多版本][Multi Versioning]。**

## 4. **意向锁** ##

共享/排它锁只是对某一个数据行进行加锁，InnoDB 支持多种粒度的锁，InnoDB 存储引擎引入了意向锁（Intention Lock），意向锁就是一种表级锁。它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。

**意向锁**是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

> 如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的。
> 
> 意向锁有点sqlserver页锁的味道，但比页锁的粒度小，一个表可以包含非常多的意向锁，属于某几行的共享锁和排他锁。

意向锁也分为两种：

*  意向共享锁(intention shared lock, IS)：**事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁**；
 *  意向互斥锁(intention exclusive lock, IX)：**事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁**；

意向锁协议(intention locking protocol)并不复杂，重点理解上面的加粗内容。由于意向锁仅仅表明意向，它其实是比较弱的锁（意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示**是否有人请求锁定表中的某一行数据**），意向锁之间并**不相互互斥**，而是**可以并行**，但它会与共享锁/排它锁互斥。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70][]

### 4.1 行**锁** ###

**行锁也叫记录锁**（Record Lock）是加到**索引记录**上的锁。即使定义了一个没有索引的表，InnoDB创建一个隐藏的聚集索引，并使用该索引进行记录锁定。

如果我们使用 id 或者 last\_name 作为 SQL 中 WHERE 语句的过滤条件，那么 InnoDB 就可以**通过索引建立的 B+ 树找到行记录并添加索引**，但是如果使用 first\_name 作为过滤条件时，由于 InnoDB 不知道待修改的记录具体存放的位置，也无法对将要修改哪条记录提前做出判断就会锁定整个表。

可以通过 [`SHOW ENGINE INNODB STATUS`][SHOW ENGINE INNODB STATUS]命令查看监控信息

### 4.2 间隙锁 ###

间隙锁是对**索引记录之间**的间隙的锁，或者对第一条索引记录之前或之后的间隙的锁。比如 `SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;` 它会阻止其他事务向表中插入 `id = 15` 的记录，因为范围中所有现有值之间的间隙都被锁定。

间隙可能跨越单个索引值、多个索引值，甚至可能为空。间隙锁是性能和并发性之间权衡的一部分，用于某些事务隔离级别，而不是其他级别。

SELECT \* FROM child WHERE id = 100;

如果ID没有被索引或具有非唯一索引，那么语句将锁定前面的间隙。

如果ID列具有唯一索引，则以下语句只对ID值为100的行使用索引记录锁，而不管其他会话是否插入前一间隙中的行。

这里也值得注意的是，冲突的锁可以通过不同的事务保持在间隙上。例如，事务A可以在间隙上持有共享间隙锁（GAP S-LOCK），而事务B可以在同一间隙上持有独占间隙锁（GAP X-LOCK）。允许冲突的间隙锁的原因是，如果从索引中清除某个记录，则必须合并不同事务对该记录保留的间隙锁。

数据库参数中innodb\_locks\_unsafe\_for\_binlog，默认值是OFF（启用间隙锁），TRUE（禁用间隙锁），这个参数会影响到主从复制及灾难恢复。

InnoDB中的间隙锁是“完全禁止的”，虽然间隙锁中也分为共享锁和互斥锁，共享和独占的间隙锁没有区别，不过它们之间并不是互斥的，也就是不同的事务可以同时持有一段相同范围的共享锁和互斥锁，它唯一阻止的就是**其他事务向这个范围中添加新的记录**。

### 4.3 **Next-Key 锁** ###

默认情况下，InnoDB在可重复读事务隔离级别下运行。在这种情况下，InnoDB使用Next-Key锁进行搜索和索引扫描，**以防止出现幻读的问题**。

Next-Key锁是**行锁加上****记录前的间隙锁的结合。**如果一个会话在索引中的记录R上有一个共享或独占的锁，则另一个会话不能按照索引顺序在R之前的间隙中插入新的索引记录。

可以通过 [`SHOW ENGINE INNODB STATUS`][SHOW ENGINE INNODB STATUS]命令查看监控信息

> 比如：当我们更新一条记录，比如 `SELECT * FROM users WHERE age = 30 FOR UPDATE;`，InnoDB 不仅会在范围 `(21, 30]` 上加 Next-Key 锁，还会在这条记录后面的范围 `(30, 40]`加间隙锁，所以插入 `(21, 40]` 范围内的记录都会被锁定。
> 
>     |   id | last_name   | first_name   |   age |
>     |------+-------------+--------------+-------|
>     |    4 | stark       | tony         |    21 |
>     |    1 | tom         | hiddleston   |    30 |
>     |    3 | morgan      | freeman      |    40 |
>     |    5 | jeff        | dean         |    50 |
>     |    2 | donald      | trump        |    80 |
>     //Age 的 Next-Key 锁就可以在需要的时候锁定以下的范围：
>     (-∞, 21]
>     (21, 30]
>     (30, 40]
>     (40, 50]
>     (50, 80]
>     (80, ∞)
> 
> 注：区间字段是通过where的第一个比给定参数决定的（db向左扫描扫到第一个比给定参数小的值， 向右扫描扫描到第一个比给定参数大的值， 然后以此为界，构建一个区间）

### 4.4 **插入意向锁** ###

对已有数据行的修改与删除，必须加互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

**插入意向锁**，是**间隙锁(Gap Locks)**的一种，它是插入之前由插入操作设置的间隙锁的一种类型。这个锁表示插入的意图，如果插入到同一索引间隙中的多个事务没有插入到间隙中的同一位置，那么它们就不需要等待对方。如果是同一位置，但不要因为行不冲突而相互阻塞。

可以通过 [`SHOW ENGINE INNODB STATUS`][SHOW ENGINE INNODB STATUS]命令查看监控信息

/**
     * 虽然事务隔离级别是RR（Repeatable Read），虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：
     * 1. 使用的是插入意向锁
     * 2. 并不会阻塞事务B
    **/
    //事务A先执行，在10与20两条记录中插入了一行，还未提交：
    insert into t values(11, xxx);
    //事务B后执行，也在10与20两条记录中插入了一行：
    insert into t values(12, ooo);
    
    CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
    INSERT INTO child (id) values (90),(102);
    
    //事务1：排他说包含包括记录102之前的间隙锁
    START TRANSACTION;
    SELECT * FROM child WHERE id > 100 FOR UPDATE;
    
    //事务2：将一条记录插入GAP中，事务在等待获取排他锁时接受插入意向锁
    START TRANSACTION;
    INSERT INTO child (id) VALUES (101);

### 4.5 **自增锁** ###

auto-inc锁是一种特殊的表级锁，由插入带有自动递增列的表中的事务获取。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务都必须等待自己向该表中进行插入，以便第一个事务插入的行接收连续的主键值。

参数 `innodb_autoinc_lock_mode允许你切换模式以提高并发。`

假如我们插入的数据中有AUTO\_INCREMENT列，InnoDB在RR（Repeatable Read）隔离级别下，能解决幻读问题。

/**
     * 数据表：t(id AUTO_INCREMENT, name);
     * 数据:
     * 1, shenjian
     * 2, zhangsan
     * 3, lisi
    **/
    //事务A先执行，还未提交：
    insert into t(name) values(xxx);
    //事务B后执行：
    insert into t(name) values(ooo);

> 1. 事务A先执行insert，会得到一条(4, xxx)的记录，由于是自增列，InnoDB会自动增长，注意**此时事务并未提交**；
> 
> 2. 事务B后执行insert，**假设不会被阻塞**，那会得到一条(5, ooo)的记录；
> 
> 3. 事务A继续insert：会得到一条(6, xxoo)的记录。
> 
> 4. 事务A再select：select \* from t where id>3，在mvvc中当然可以得到(4, xxx)和(6, xxoo)
> 
> 咦，这对于事务A来说，就很奇怪了，对于AUTO\_INCREMENT的列，**连续插入了两条记录**

自增锁是一种特殊的**表级别锁**（table-level lock），专门针对事务插入AUTO\_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。与此同时，InnoDB提供了innodb\_autoinc\_lock\_mode配置，可以调节与改变该锁的模式与行为。

## 5. **insert**|**select|update|delete** ##

**select**

普通的select是[快照读][Multi Versioning]，而select ... for update或select ... in share mode则会根据情况加不同的锁

如果在**唯一索引上**用唯一的查询条件时（ where id=1），加记录锁

否则，其他的查询条件和索引条件，加间隙锁（`BETWEEN AND`）或Next-Key 锁（可重复隔离级别）

**update与delete**

如果在唯一索引上使用唯一的查询条件来update/delete，加记录锁

否则，**符合查询条件**的索引记录之前，都会加Next-Key 锁

注：如果update的是聚集索引，则对应的普通索引记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的：普通索引存储PK的值，检索普通索引本质上要二次扫描聚集索引。

**insert**

insert和update与delete不同，它会用排它锁封锁被插入的索引记录，同时，会在插入区间加插入意向锁，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY插入。![82818753][]

## 6. 命令 ##

# 设置事务隔离级别
    set (session/global) transaction isolation level [read uncommitted/read committed/repeatable read/serilisable]
    
    # 查看事务隔离级别 
    select @@(session./global.)tx_isolation;
    
    # 开锁
    start transaction / rollback / commit
    select ... lock in share mode / select ... for update
    
    # 查看锁等待情况
    select * from information_schema.innodb_locks;
    select * from information_schema.innodb_lock_waits;
    select * from information_schema.innodb_trx;
    
    # 查看InnoDB状态(包括锁)
    show engine innodb status;

**引用资料**

*  [InnoDB Locking][]

[Multi Versioning]: https://blog.csdn.net/alex_xfboy/article/details/82808216
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70]: /images/20220317/9306b965a3c745869d64d6295643fb29.png
[SHOW ENGINE INNODB STATUS]: https://dev.mysql.com/doc/refman/5.7/en/show-engine.html
[82818753]: /images/20220317/2e393898550f4c0bac1c152da6bcb8d0.png
[InnoDB Locking]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html