欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-14 16:54:52
...

问题描述

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需要清楚需要在什么事务隔离级别下执行,比如上面问题需要在可重复读隔离级别。