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

问题描述

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

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

解决方案

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

经验总结

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