【MySQL】可重复读模式下 unique key失效案例
2023-09-14 08:57:29 时间
一 【背景】
今天上午文能提笔安天下,武能上马定乾坤的登博给团队出了一道题目,谁先复现问题,奖励星巴克一杯。激起了一群忙碌的屌丝DBA的极大热情。问题是这样滴,如下图
登博提示了几个细节:
1. code上的uk并未失效。
2. rr隔离级别。
3. 有并发线程的操作。
二 【原理分析】
1 事务隔离级别的基础知识:
未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据。 提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。 可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读。 串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞。 这里重点说一下RR 模式:可重复读 是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,即使第二个事务对数据进行修改,第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的,因此称为是可重复读。
2 MVCC 的读操作
在MVCC并发控制中,读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)。快照读,读取的是记录的可见版本 (有可能是历史版本),不用加锁。
当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。
快照读:简单的select操作,属于快照读,不加锁。
select * from table where ?;
当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。
注意:insert操作可能会触发Unique Key的冲突检查,也会进行一个当前读。
三【解决.复现】
测试版本: 5.5.18 5.6.16 均可复现。
现在我们根据上述理论信息进行复现问题,具体的实现步骤如下:
注意 数据库的隔离级别为RR
session 1 session 2
root@test 08:47:41 set global tx_isolation=REPEATABLE-READ;
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:16 set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:22 insert into yy values(1,20,13);
Query OK, 1 row affected (0.00 sec)
root@test 08:53:31 commit;
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:39 select * from yy;
+----+------+------+
| id | code | val |
+----+------+------+
| 1 | 20 | 13 |
+----+------+------+
1 row in set (0.00 sec)
root@test 08:53:53 delete from yy where id=1;
Query OK, 1 row affected (0.00 sec)
root@test 08:53:59 commit;
root@test 08:54:10 insert into yy values(2,20,13);
Query OK, 1 row affected (5.59 sec)
root@test 08:54:23 select * from yy;
+----+------+------+
| id | code | val |
+----+------+------+
| 1 | 20 | 13 |
| 2 | 20 | 13 |
+----+------+------+
2 rows in set (0.00 sec)
当session 2中将id=1 的删除之后,session1 进行insert操作时,触发unique key冲突检查,此时因为id=1 code=20的数据已经被物理删除了,MySQL 检查无冲突,进行insert insert into yy values(2,20,13); 便成功了。
四【结果展示】
今天上午文能提笔安天下,武能上马定乾坤的登博给团队出了一道题目,谁先复现问题,奖励星巴克一杯。激起了一群忙碌的屌丝DBA的极大热情。问题是这样滴,如下图
登博提示了几个细节:
1. code上的uk并未失效。
2. rr隔离级别。
3. 有并发线程的操作。
二 【原理分析】
1 事务隔离级别的基础知识:
未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据。 提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。 可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读。 串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞。 这里重点说一下RR 模式:可重复读 是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,即使第二个事务对数据进行修改,第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的,因此称为是可重复读。
2 MVCC 的读操作
在MVCC并发控制中,读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)。快照读,读取的是记录的可见版本 (有可能是历史版本),不用加锁。
当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。
快照读:简单的select操作,属于快照读,不加锁。
select * from table where ?;
当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。
注意:insert操作可能会触发Unique Key的冲突检查,也会进行一个当前读。
三【解决.复现】
测试版本: 5.5.18 5.6.16 均可复现。
现在我们根据上述理论信息进行复现问题,具体的实现步骤如下:
注意 数据库的隔离级别为RR
session 1 session 2
root@test 08:47:41 set global tx_isolation=REPEATABLE-READ;
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:16 set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:22 insert into yy values(1,20,13);
Query OK, 1 row affected (0.00 sec)
root@test 08:53:31 commit;
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:39 select * from yy;
+----+------+------+
| id | code | val |
+----+------+------+
| 1 | 20 | 13 |
+----+------+------+
1 row in set (0.00 sec)
root@test 08:53:53 delete from yy where id=1;
Query OK, 1 row affected (0.00 sec)
root@test 08:53:59 commit;
root@test 08:54:10 insert into yy values(2,20,13);
Query OK, 1 row affected (5.59 sec)
root@test 08:54:23 select * from yy;
+----+------+------+
| id | code | val |
+----+------+------+
| 1 | 20 | 13 |
| 2 | 20 | 13 |
+----+------+------+
2 rows in set (0.00 sec)
当session 2中将id=1 的删除之后,session1 进行insert操作时,触发unique key冲突检查,此时因为id=1 code=20的数据已经被物理删除了,MySQL 检查无冲突,进行insert insert into yy values(2,20,13); 便成功了。
四【结果展示】
相关文章
- navicat mysql导出数据 批量插入的形式
- FlinkCDC读取MySQL并写入Kafka案例(com.alibaba.ververica)
- Mybatis+mysql动态分页查询数据案例——工具类(MybatisUtil.java)
- Mybatis+mysql动态分页查询数据案例——分页工具类(Page.java)
- Mysql分页查询通用存储过程 - 阿影的黄金时代 - 博客频道 - CSDN.NET
- MySQL运维---用户权限及管理
- Mysql一个非常有用的内置函数今天碰到要把MySQL数据库中的varchar转换成date类型进
- Mysql:Error Code 1235,This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME 错误解决
- MySQL运维---从库线程管理及故障案例
- Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引
- Linux重置Mysql密码_解决MySQL for Linux错误 ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost
- MyBatis JdbcType 与Oracle、MySql数据类型对应关系详解
- Python:mysql-replication监控MySQL的binlog变动
- mysql-connector-java与Mysql、Java的对应版本
- 安全测试===Mysql 注入技巧学习 MySQL注入技巧(2)
- mysql 数据库 分表后 怎么进行分页查询?Mysql分库分表方案?
- Mysql安装多台mysql
- 【MySQL笔记】MySQL数据库之存储过程、异常处理、事务管理的使用
- 【MySql】MySQL数据库--什么是MySQL的回表 ?
- MySQL 一文带你详解 mysql binlog