记一次MySQL重复插入问题可重复读和读提交间隙锁

墨蓝 2022-11-27 06:54 158阅读 0赞

## 问题描述 ##

MySQL 5.7 有表t2：

CREATE TABLE `t2` (
    	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`idx_f` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    	PRIMARY KEY (`id`),
    	INDEX `idx_f` (`idx_f`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    ;
    mysql> select * from t2;
    +----+-------+
    | id | idx_f |
    +----+-------+
    |  1 |     1 |
    |  2 |     5 |
    |  3 |    10 |
    +----+-------+
    3 rows in set (0.00 sec)

有以下业务SQL，先判断目标行是否存在，不存在才插入，如果已存在则不插入：

（真实SQL自然不是这样的，而且因为其他原因不能在**idx\_f**建唯一索引）

insert into t2(idx_f) select 4 where not exists (select * from t2 where idx_f = 4);

SIT测试同事测试时候发现在一次操作后，发现库中存在两条**idx\_f=4**的行。

## 最初猜想 ##

这个现象大家第一反应可能就是并发导致的：

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xj4kNrJH-1597326687198)(最初想法.jpg)\]

于是尝试在开发环境通过多线程进行复现，5个线程同时插入然后检查是否仅插入一行，测试重复100万次，最终这种暴力尝试的方法未能复现！

然后手动控制事务测试：

注意如果使用HeidiSQL，一个应用窗口实际是一个会话，一个查询面板不是一个会话。

保证测试环境如下：

mysql> select * from t2;
    +----+-------+
    | id | idx_f |
    +----+-------+
    |  1 |     1 |
    |  2 |     5 |
    |  3 |    10 |
    +----+-------+
    3 rows in set (0.00 sec)

会话1执行：

begin;
    insert into t2(idx_f) select 4 where not exists (select * from t2 where idx_f = 4);    
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0

会话2执行：

insert into t2(idx_f) select 4 where not exists (select * from t2 where idx_f = 4);

然后可发现会话2被阻塞，如果会话1未能及时commit，会导致会话2加锁超时，

会话1及时commit后会话2输出如下：

Query OK, 0 rows affected (7.08 sec)
    Records: 0  Duplicates: 0  Warnings: 0

结果显示会话2并未插入成功。

于是百度发现**insert into select**这种插入语句会对访问过的所有行加锁！因此上图中主动加for update的方式根本没有意义，因为加不加都会加锁，**既然加锁了那应该能互斥呀！？难道不是因为并发导致的么**

## 弯路走起 ##

考虑到测试环境使用了mgr集群，而开发环境是MySQL单点，所以怀疑是不是mgr集群导致的呢?通过研究mgr集群，最后也无功而返。

## 请教同事 ##

因为始终无法复现，导致不能定位原因，于是请教DBA同事，但是诡异的现象出现了，一样的复现方法问题竟然复现了，复现过程如下：

保证测试环境如下：

mysql> select * from t2;
    +----+-------+
    | id | idx_f |
    +----+-------+
    |  1 |     1 |
    |  2 |     5 |
    |  3 |    10 |
    +----+-------+
    3 rows in set (0.00 sec)

会话1执行：

mysql> begin;insert into t2(idx_f) select 4 where not exists (select * from t2 where idx_f = 4); 
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0

会话2执行：

insert into t2(idx_f) select 4 where not exists (select * from t2 where idx_f = 4);
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0

诡异的现象发生了，会话2根本没有被阻塞，直接插入成功！

然后会话1 commit 并执行查询结果如下：

mysql> commit; 
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from t2;
    +----+-------+
    | id | idx_f |
    +----+-------+
    |  1 |     1 |
    |  5 |     4 |
    |  6 |     4 |
    |  2 |     5 |
    |  3 |    10 |
    +----+-------+
    5 rows in set (0.00 sec)

问题就这么复现了，但是脑袋更懵了，可喜的是更接近了真相！

## 幡然醒悟 ##

突然想到DBA同事的测试环境并不是开发环境，是不是两次测试的环境不一致导致一个能一个不能呢？

开发环境MySQL是5.7.18，DBA环境是5.7.30，这相差也不大呀，

那是事务隔离级别么。。。。看到DBA环境的事务隔离级别是 **read-committed**！

额，就是事务隔离级别！因为开发环境的事务隔离级别是**repeatable-read**！

于是也才知道测试环境和生产环境也是 **read-committed**！

## 原理解读 ##

**repeatable-read**事务隔离级别为了防止幻读问题，引入了间隙锁。间隙锁可理解为某个值**左边的锁**。

首先理解一下这条插入语句怎么加的锁：

首先idx\_f上有索引，因此能在idx\_f索引树上直接定位到索引值=5的叶子节点，所以会对(1,5)范围加锁，这个就是间隙锁。

当第一个事务加锁成功后，第二个事务当执行【select \* from t2 where idx\_f = 4)】时，发现(1,5)上有锁，因此只能等待，当事务1提交后，事务2执行【select \* from t2 where idx\_f = 4)】重新读且返回事务1插入的行，所以not exists条件不成立。

所以在可重复读隔离级别下，不可能同时两个事务插入成功。

但是 **read-committed**是没有间隙锁的！读提交隔离级别不解决幻读问题，不会加间隙锁，因此这里压根没有锁，所以都能插入成功。

加锁过程请参考：[https://blog.csdn.net/zhouwenjun0820/article/details/107851595][https_blog.csdn.net_zhouwenjun0820_article_details_107851595]

注：在一个事务中前后两次查询，第二次查询结果多了其他事务**新插入**的行叫做幻读。

## 解决方案 ##

因为只能在读提交隔离级别下，所以通过升级为唯一索引来防止重复。

## 经验总结 ##

*  遇到问题，应该在问题发生处进行排查，越靠近现场，越接近真相。
 *  遇到瓶颈，互相协作，教学式排查反而更有灵感。
 *  写的SQL需要清楚需要在什么事务隔离级别下执行，比如上面问题需要在可重复读隔离级别。

[https_blog.csdn.net_zhouwenjun0820_article_details_107851595]: https://blog.csdn.net/zhouwenjun0820/article/details/107851595