MySQL——插入加锁/唯一索引插入死锁/批量插入效率

水深无声 2023-09-24 18:53 49阅读 0赞

本篇主要介绍MySQL跟加锁相关的一些概念、MySQL执行插入Insert时的加锁过程、唯一索引下批量插入可能导致的死锁情况，以及分别从业务角度和MySQL配置角度介绍提升批量插入的效率的方法；

## MySQL跟加锁相关的一些概念 ##

在介绍MySQL执行插入的加锁过程之前，先复习下几种跟锁相关的概念；

### 快照读 ###

*  InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力；

*  快照是整库级别；

*  数据版本与事务ID一一对应；

*  各个数据版本通过当前数据与期间的undo log作用得到；

*  判断数据是否可见的规则——对于当前事务，如果一个数据版本是在其启动之前生成的，就认；如果是我启动以后才生成的，就不认，我必须要找到它的上一个版本；如果是这个事务自己更新的数据，它自己还是要认的；

*  一致性视图生成的时机（即视图数组确定的时机）并非事务的begin命令，而是事务内的第一个select语句；trx\_id生成的时机是事务开启后的第一个当前读DML语句；因此，可能存在"trx\_id小于当前事务A，但未提交的事务A\_pre"和"trx\_id大于当前事务A，但未提交的事务A\_behind"；

*  对于innoDB默认的可重复读级别下，查询只承认在一致性视图创建前就已经提交完成的数据；对于读提交，查询只承认在语句执行前就已经提交完成的数据；

*  不管哪个事务隔离级别，写操作都是当前读；当前读，总是读取已经提交完成的最新版本；

### 当前读 ###

*  新数据都是先读后写的，而这个读，只能读当前最新的值，称为“当前读”（currentread）；

*  行锁是在事务执行更新语句时加上的，要等到事务结束时才释放；

*  除了update 之类的写操作语句外，select语句也可以加行锁，也是当前读； select ... lock in share mode 对数据行加读锁（S锁，共享锁）， select ... for update对数据行加写锁（X锁，排他锁）；

### S锁 ###

*  S锁，英文为Shared Lock，共享锁，也称之为读锁，即Read Lock；

*  S锁之间是共享的，或者说是互不阻塞的；

*  执行select ... lock in share mode读取一条记录时，需要先获取该记录的S锁；

*  S锁规则——事务T1对记录R1加上了S锁，那么当前事务T1可以读取R1这一行记录，但是不能修改R1，其他事务T2可以继续对R1添加S锁，但是不能添加X锁，只有当R1上面的S锁释放了，才能加上X锁；

### X锁 ###

*  X锁，英文为Exclusive Lock，排他锁，也称之为写锁，即Write Lock；如同它的名字，X锁是具有排他性的，即一个写锁会阻塞其他的X锁和S锁；

*  当事务需要修改一条记录时（或执行 select ... for update），需要先获取该记录的X锁；

*  当然，更新数据之前肯定是需要读到最新数据的，因此获取X锁不影响当前事务读取最新数据；

### 幻读 ###

*  事务执行更新的期间，若允许插入了新的数据，导致事更新到了新插入的数据，相当于发生了幻读——事务前后看到的数据不一样，当前读读到了"多出来的"一些数据；

*  幻读破坏了加锁的语义——满足更新语句条件的记录在事务中途增加了，没锁住；

*  此外，幻读影响了binlog与主库数据的一致性——binlog在事务commit时生成，若事务A先执行更新，同时事务B执行插入后commit，然后A再提交，则会导致从库执行binlog时更新到了"预期之外"的行，导致主从数据不一致；

### 间隙锁gap lock ###

*  gap lock锁的是插入行为；gap lock锁之间其实并不互斥；

*  为了解决幻读问题，InnoDB引入间隙锁 (Gap Lock)，锁的就是两个值之间的空隙，可以是主键，也可以是二级索引；

### next-key lock ###

为解决幻读问题引入了间隙锁，再加上更新数据时的行锁，合在一起称为next-key lock；把间隙锁记为开区间，因此每个next-key lock是前开后闭区间；

间隙锁和next-key lock的引入，解决了幻读的问题，但同时在并发情况下，可能导致死锁发生，原因是间隙锁获取不冲突导致多个线程都持有同一个间隙锁，但是执行插入时会冲突；

### 插入意向锁 ###

插入意向锁准确的说它不是锁，而是插入行为；插入意向锁与间隙锁gap lock冲突；配合上面的间隙锁gap lock一起防止了幻读；

如果插入的记录有唯一索引，还会进行Duplicate Key判断，因此需要当前读；如果存在相同Key且该Key被加了互斥锁，则会加S锁（共享锁），然后等待（因为这个相同的Key之后有可能会回滚删除，这里非常容易死锁），直到等到成功插入后，会在这条记录上加X锁（排他锁）；最后在当前事务提交/回滚时释放X锁；

## MySQL执行插入Insert时的加锁过程 ##

### MySQL官方文档对Insert加锁情况的描述 ###

> INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.  
>   
> Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.  
>   
> If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.

解读：

1. INSERT操作会对新插入的记录加行锁写锁（排它锁X锁）；注意：区别于执行update，执行insert时这里加的是X锁，并非next-key lock，因此不会阻塞其他的事务对gap区间的插入操作；

2. 在插入记录前，会向插入记录所在位置申请意向插入意向锁（Insertion Intention Gap Lock）；只要不是插入的是同一行记录，多个事务对相同gap区间的插入操作不会冲突；

3. 对于唯一索引，发生唯一键冲突时，当前事务会先尝试在这条记录上加读锁S锁；加S锁的机制可能会导致死锁：即A线程占用记录的X锁，B、C阻塞，它们需要先获取S锁再获取X锁执行更新；当A释放X锁时，B、C同时拿到S锁（共享锁），但互相都无法继续获取X锁（S锁与X锁互斥），导致死锁；

下面通过几个case来验证和说明上面的insert的加锁过程，简化的表结构如下：

CREATE TABLE `t1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `a` varchar(5),
      `b` varchar(5),
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk_name` (`a`,`b`)
    );

### case1：并发事务先后插入同一行 ###

在记录不存在的情况下，两个同样顺序的批量insert同时执行，第二个会进行锁等待状态；现象如下：

![a9679ed1c87843809090cfefac2fa97d.png][]

解释：

*  insert加的是插入意向锁，它是隐式锁；隐式锁的意思就是没有锁，但是可能因为一些触发规则被升级成行锁；

*  在t1插入记录时，是不加锁的；这个时候事务t1还未提交的情况下，事务t2尝试插入的时候，发现有这条记录；根据官方文档描述，t2会尝试获取S锁，它会判定记录上的事务id是否活跃（存在正在执行还未提交的事务），如果活跃的话，说明正在修改记录的事务未结束，会帮t1把它的隐式锁提升为显式锁（X锁）；因此，t1持有X锁，而t2尝试获得S锁，X锁与S锁冲突，t2阻塞；

### case2：批量插入顺序不一致的导致的死锁 ###

**根据case1的结论**，当多个事务线程批量插入数据的插入顺序不一致时，会导致死锁；现象及解释如下：

![f75d8ae64f0a43d09b0c0b980e0cdd03.png][]

解决方法：在业务侧对插入的数据做好排序，保证并发执行时不会产生死锁；

### case3：delete提交但未purge导致的并发insert死锁 ###

关于purge的相关知识，可参考我的[MySQL——关于删除/purge/删除加锁/删除大量数据/truncate&delete][MySQL_purge_truncate_delete]这篇文章；

begin;
    delete from t1 where a = '25'
    commit;
    
    begin;
    INSERT ignore INTO `t1` (`a`, `b`) VALUES('25','1')
    commit;

这块代码在多个线程同时调用的时候，非常容易死锁；注意这块代码里面有2个事务；

解释：

*  执行delete事务提交成功，若还没来得及 purge（被执行purge的对象包括UNIQUE KEY(a,b)），这时后面的事务执行这条记录的insert时会先尝试对这条记录加S锁；

*  并发情况下，多个insert事务获取S锁成功，但是在执行（含唯一索引）insert时，需要获取X锁，此时并发的线程会出现——同时拿到S锁（共享锁），但互相都无法继续获取X锁（S锁与X锁互斥），导致死锁；

### case4：3个 insert ignore，一个回滚造成的死锁 ###

这个case就跟官方文档中表述的一致——

> "If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock."

当互斥锁释放时，并发的insert线程会由于先尝试获取的S锁，而导致后序获取X锁时产生死锁；

![c3377c70c0c34126b7a3b37bea6c1114.png][]

实验如下：本地MySQL 5.7版本开3个链接窗口，关闭自动提交，按照如下顺序操作，结果如预期一样出现死锁；

![4a644d5212fe412eb3e507a705e21027.png][]

通过 **SHOW ENGINE INNODB STATUS;** 命令，查看日志如下：

=====================================
    2023-03-08 15:15:13 0x7fa4 INNODB MONITOR OUTPUT
    =====================================
    略
    ------------------------
    LATEST DETECTED DEADLOCK ## 最新的死锁
    ------------------------
    2023-03-08 15:10:12 0x7fa4 ## 这里显示了最近一次发生死锁的日期和时间
    *** (1) TRANSACTION: ## 事务1
    TRANSACTION 220709, ACTIVE 10 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 16, OS thread handle 25992, query id 158597 localhost 127.0.0.1 root update
    INSERT ignore INTO `user` (`id`,`username`, `age`, `birthday`, `sex`, `address`, `update_time`) VALUES (55,'小明', 5, '2019-09-17 19:12:37', '男', '杭州', '2022-09-01 16:03:25')
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  
    ## 解释：事务1在等X锁（主键索引space id 302 page no 3 n bits 88 index PRIMARY）
    RECORD LOCKS space id 302 page no 3 n bits 88 index PRIMARY of table `mybatis_01`.`user` trx id 220709 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 19 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
    略
    
    *** (2) TRANSACTION: ## 事务1
    TRANSACTION 220708, ACTIVE 25 sec inserting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 7, OS thread handle 32676, query id 158584 localhost 127.0.0.1 root update
    INSERT ignore INTO `user` (`id`,`username`, `age`, `birthday`, `sex`, `address`, `update_time`) VALUES (55,'小明', 5, '2019-09-17 19:12:37', '男', '杭州', '2022-09-01 16:03:25')
    *** (2) HOLDS THE LOCK(S): 
    ## 解释：事务2持有S锁（主键索引space id 302 page no 3 n bits 88 index PRIMARY，跟上面事务1等的X锁为同一行，X锁与S锁互斥，因此事务1得等事务2释放行锁）
    RECORD LOCKS space id 302 page no 3 n bits 88 index PRIMARY of table `mybatis_01`.`user` trx id 220708 lock mode S locks gap before rec
    Record lock, heap no 19 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
    略
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
    ## 解释：事务2在等X锁（主键索引space id 302 page no 3 n bits 88 index PRIMARY，跟上面事务1等的X锁为同一把锁，而事务1在等事务2持有的S锁释放，因此死锁）
    RECORD LOCKS space id 302 page no 3 n bits 88 index PRIMARY of table `mybatis_01`.`user` trx id 220708 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 19 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
    略
    
    *** WE ROLL BACK TRANSACTION (2)
    ## 解释：根据事务回滚成本，选择事务2回滚，释放S锁
    ------------

死锁日志的查看方法可参考：[MySQL死锁日志][MySQL]、[MySQL死锁日志的查看和分析][MySQL 1]

### MySQL insert加锁流程（重要） ###

1. 首先对插入的间隙加插入意向锁（Insert Intension Locks）；注意插入意向锁可以理解为只是个插入动作，并非真的加锁，它只跟GAP锁（间隙锁）锁的插入区间冲突；

*  如果该间隙已被加上了GAP锁（间隙锁）或 Next-Key 锁（含GAP 锁），则加锁失败，等待间隙释放；

*  如果没有，则加插入意向锁成功，表示可以插入；

2. \[可选 唯一键\]然后判断插入记录是否有唯一键，如果有，则需要进行唯一性约束检查：

*  如果不存在相同键值，则准备插入；

*  如果存在相同键值，则判断该键值是否加锁；

*  如果没有锁， 判断该记录是否被标记为删除；

*  如果标记为删除，说明删除事务已经提交，数据页上的记录还没来得及 purge，这时尝试加S锁；

*  如果没有标记删除，说明数据已存在，本次插入会导致唯一键冲突，则报 1062 duplicate key 错误；

*  如果有锁，说明该记录正在被处理（正在被其他事务新增、删除或更新），且其他事务还未提交，则当前事务尝试加S锁后会进入阻塞，等待其他事务释放记录X锁；

3. 插入记录并对记录加X锁，当前事务提交或回滚时释放X锁；

###  insert加锁的小结 ###

1.  关于隐式锁，一般使用自增主键且不含唯一键的insert语句，并发时不会有冲突，因为插入意向锁是隐式锁，实际代表的是一个准备插入的行为；这个行为只和间隙锁GAP锁冲突；

1.  关于锁升级，当插入的数据含唯一键，出现并发插入时（如事务A和事务B，事务A先执行insert但还未提交事务），后执行插入的事务B会使用当前读，如果发现这条记录已存在（还未提交），则会先尝试获取S锁；此时事务A的插入意向锁（隐式锁）会被提升为显式锁（ X 锁），导致事务B阻塞；

1.  关于插入死锁；正是MySQL这种唯一键冲突时的insert插入获取锁的规则，导致了在\[case2: 并发插入事务中的顺序顺序不一致\]、\[case3: 删除提交但还未purge\]、或\[case4: 并发插入事务中单条回滚\]的case下，会出现并行的事务先拿到S锁（共享锁），但在执行insert加X锁时，发现阻塞，构成死锁；这个问题在MySQL官方文档也提到了；

**建议**：

*  针对case2，保证各个事务线程执行插入时，插入数据的顺序相同，避免死锁；

*  针对case3和case4，保证各个事务线程执行含唯一键的数据插入前，先对数据加X锁，即跳过insert加锁机制的先尝试获取S锁这一阶段；如select ... for update；如果查到了，则跳过插入；

## 有哪些提升MySQL插入性能的方法？ ##

今天有个同事问我，有个业务场景，需要提升MySQL插入的性能，问我有哪些思路；

我的第一反应是，大部分的互联网场景都是读多写少，很少见写入密集型的业务场景，高并发写本就不适合用MySQL；

大量的写操作会产生大量日志，消耗IO与带宽资源，同时会一定程度影响查询性能；此外，在读写分离的架构下，会导致主从延迟升高，从库难以追上主库，导致一些查询场景查到的数据一直是是"旧的"，引起业务问题；

不过还是从MySQL的角度，提供了几点优化思路：

**1. 使用批量插入替代逐条插入**

批量插入意味着多条插入语句放入了一个事务里；而单条插入时，每条语句都会生成一条事务；

优点是通过合并批量插入语句到单个事务，可以减少创建事务的消耗；

缺点也很明显，这个事务的会随着批次bitchSize的增加，变成一个长事务，会导致较大的回滚成本以及主从延迟增加；因此需要权衡这个bitchSize的大小，建议不要超过100；

**2. 修改SQL语句的写法**

将事务内多条insert values语句合并起来；如下，

INSERT INTO `t` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);  
    INSERT INTO `t` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);

改为：

INSERT INTO `t` (`datetime`, `uid`, `content`, `type`) VALUES
     ('0', 'userid_0', 'content_0', 0),
     ('1', 'userid_1', 'content_1', 1);

这种方法优化的效果理论上还是有的，执行SQL产生的binlog大小减少了，降低日志刷盘的数据量和频率；并且通过合并SQL语句，减小了SQL语句的传输大小，减少网络传输的IO；同时也能减少SQL语句解析的次数；

**3. 尽量保证数据有序插入**

如果插入的表上存在索引，可以在插入前尽量让插入的数据按索引排序，如在业务代码内做一下手动排序；如果有多个索引，则选择索引树较大的那个索引，对齐字段做排序；

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表；每一个索引在InnoDB里面对应一棵B+树，所有叶子节点构成一个有序链表，也就是说是有序的；

数据库插入时，需要维护索引数据，而无序的记录会增大维护索引的成本；

试想如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；

如果插入的记录在索引中间，插入记录的索引定位效率会下降；并且数据插到叶子节点时，可能引起页分裂/页合并，数据量较大时会有频繁的IO操作，会消耗比较多CPU和IO资源；

**4. 修改innodb\_flush\_log\_at\_trx\_commit参数**

InnoDB提供了innodb\_flush\_log\_at\_trx\_commit参数，来控制redolog的写入策略：

*  设置为0，表示每次事务提交时都只是把redolog留在redolog buffer中；

*  设置为1，表示每次事务提交时都将redolog直接持久化到磁盘；

*  设置为2，表示每次事务提交时都只是把redolog写到page cache；

InnoDB有一个后台线程，默认每隔1秒，就会把redolog buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘；

为了缓解IO压力，可以让redolog的落盘不要那么频繁，完全依赖后台线程，则可以将innodb\_flush\_log\_at\_trx\_commit修改为0；

缺点也很明显，但Sever断电时有丢失数据风险，会丢失这个落盘任务执行间隔之间变更的数据；一般作为业务侧，是用不到这个方法的，也是不推荐的；

**这里提一下问题本身：一个处理流程中，包含一次insert和一次RPC调用，而insert的性能瓶颈成为了这个流程执行效率的瓶颈；**

同事一开始的思路是优化插入操作的性能，但在我询问了这条insert与RPC是否有数据强一致性关联时，他捋了捋发现并没有；

所以解决问题的方案很简单——将insert从处理流程中去掉就行了！

是不是很简单，提前用任务插入，或者流程中异步插入，控制插入执行的速率，如通过MQ；哪怕真的有数据强一致性关联，也应该使用其他写性能很高的存储引擎，如redis；

所以解决问题的思路要视野宏观一段，切勿一叶障目；不过也借此机会，梳理了下MySQL插入相关的知识点；

本篇参考：

[我的文章——幻读/间隙锁/Next-Key Lock][Next-Key Lock]

[S锁与X锁 / 当前读与快照读][S_X_ _]

[读MySQL源码再看INSERT加锁流程][MySQL_INSERT]

[MySQL插入死锁套路][MySQL 2]

[MySQL 死锁套路：一次诡异的批量插入死锁问题分析][MySQL 3]

[修改MySQL配置来提高mysql插入速度的方法][MySQL_mysql]

[MySQL批量SQL插入性能优化][MySQL_SQL]

[解决死锁之路 - 常见 SQL 语句的加锁分析][- _ SQL]

[a9679ed1c87843809090cfefac2fa97d.png]: https://img-blog.csdnimg.cn/img_convert/a9679ed1c87843809090cfefac2fa97d.png
[f75d8ae64f0a43d09b0c0b980e0cdd03.png]: https://img-blog.csdnimg.cn/img_convert/f75d8ae64f0a43d09b0c0b980e0cdd03.png
[MySQL_purge_truncate_delete]: https://blog.csdn.net/minghao0508/article/details/128899104
[c3377c70c0c34126b7a3b37bea6c1114.png]: https://img-blog.csdnimg.cn/img_convert/c3377c70c0c34126b7a3b37bea6c1114.png
[4a644d5212fe412eb3e507a705e21027.png]: https://img-blog.csdnimg.cn/img_convert/4a644d5212fe412eb3e507a705e21027.png
[MySQL]: https://blog.csdn.net/u011334954/article/details/121350197
[MySQL 1]: https://blog.csdn.net/lkforce/article/details/108781691?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-0-108781691-blog-121350197.pc_relevant_3mothn_strategy_recovery&spm=1001.2101.3001.4242.1&utm_relevant_index=3
[Next-Key Lock]: https://blog.csdn.net/minghao0508/article/details/128179144
[S_X_ _]: https://www.51cto.com/article/704270.html
[MySQL_INSERT]: https://mp.weixin.qq.com/s?__biz=MzI4NjE4NTUwNQ==&mid=2247495043&idx=6&sn=23ce2c41c0c7533144d0aa3e6f6937f5&chksm=ebe26ccddc95e5dbbc2ee7c4ab393e311ed94ed96a37d3247e3a50515502d6ef91adb214d877&scene=27
[MySQL 2]: https://heapdump.cn/article/2343842
[MySQL 3]: https://juejin.cn/post/6844903854165721101#heading-0
[MySQL_mysql]: https://www.modb.pro/db/106963
[MySQL_SQL]: https://www.51cto.com/article/632579.html
[- _ SQL]: https://www.cnblogs.com/jkin/p/16613054.html