【高并发基础】MySQL不同事务隔离级别下的并发隐患及解决方案

前言

本项目聚焦高并发的基础知识，至底向上得研究：

• 单机事务模型（Mysql事务） <===== 本文坐标
• 单机事务模型（Spring事务传播 ）
• 单机高并发 （多线程 -> 缓存）
• 集群 ( 集群 -> 分布式 -> 微服务 -> 服务治理)
• 读密集（数据仓库、搜索引擎）
• 写密集（TODO）
  

项目介绍

github 仓库 (分支 master / isolation / propagation / multithreading)

理念：

• 使用Spring Boot 和基于Groovy的测试框架Spock编写测试用例。
• 证实并发隐患后(脏读、更新丢失、不可重复读、幻读等)，用官方文档加以说明解释，并沉淀用例。
• 不合理的解决方案造成死锁，沉淀用例
• 较合理的解决方案，沉淀用例

分支组成

• Spock 与 Spring Boot 集成 master 分支
• Mysql-Innodb 的事务隔离及常用锁 isolation 分支
• Spring 事务传播行为 propagation 分支
• Java JUC 多线程编程（Doing）multithreading 分支
• 升级项目为MySQL集群（Todo）

技术栈使用

• Spring Boot 2.5.14
• Spock
• MySQL 5.7
• Mybatis 2.2.2 （starter）
• Druid 1.2.6 (starter)

1. MySQL 事务隔离级别

• SQL92标准 规定了四种隔离级别，与具体的数据库底层实现无关。如果数据库引擎能完全规避掉脏读，那么它对外就可以声明自己为RC的隔离级别。
• 值得注意的是，并不是隔离级别越高越好。对于 MySQL 的Innodb引擎，若使用Serializable，底层用两阶段加锁，会把所有读都隐式添加上lock in share mode，两阶段加锁会造成大量死锁。
• MySQL 采用 RR 时，底层用next-key 解决了幻读问题。换言之， MySQL 的 RR 是逼近Serializable的能力，又压低了死锁的概率，能应对大部分应用场景。
  

---

2. MySQL 的 RR 已经够用了，为什么用RC?

• 禁用了gap锁，减少了锁的作用范围，即减少了死锁风险

• 使用了"半一致读"降低了锁等待，即减少了死锁风险，也提高了读效率

官方原文：https://dev.mysql.com/doc/refman/5.7/en/glossary.html
semi-consistent read
A type of read operation used for UPDATE statements, that is a combination of READ COMMITTED and consistent read. When an UPDATE statement examines a row that is already locked, InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again, and this time InnoDB either locks it or waits for a lock on it. This type of read operation can only happen when the transaction has the READ COMMITTED isolation level, or when the innodb_locks_unsafe_for_binlog option is enabled. innodb_locks_unsafe_for_binlog was removed in MySQL 8.0.

大致意思是，"semi-consistent read"只出现在update语句中。区别于其他隔离级别，update是加排他锁，RC中的"semi-consistent read"在遇到锁的时候会先去按序读一下where命中条件是否冲突，不冲突则可以更新。这个阐述其实有点矛盾，为什么即“锁等待”又“不冲突”？网上有篇很不错的文章可以了解

3. RC 的并发隐患

3.1 RC 不可重复读

• 现象：同一个事务中，两次读取同一行内容返回值不一样。
• 原因：普通select 读的是当前版本，在事务的不同时间点，一行记录的版本会被其他事务升级。而RC每次的读操作都会拿到最新已经提交的版本。
• 解决：加锁保证同一个事务都是读同一个版本

3.1.1 RC 解决不可重复读，使用 lock in share mode ，但放大了死锁隐患

项目isolation分支 ：

• 示例代码 NonRepeatableReadFixByLockSpec
• 示例代码 DeadLockSpec 体现了同样条件下，使用lock in share mode 更容易造成死锁。
  参考: MySQL5.7 lock in share mode 的死锁例子

                按下面顺序加锁会导致死锁
                A事务 lock in share mode =》 获取读锁成功
                B事务 lock in share mode =》 获取读锁成功
                B事务 update 期望获得锁升级，尝试获取写锁，被A事务的读锁阻塞。 =》 等待A读锁释放
                A事务 update 期望获得锁升级，尝试获取写锁，被B事务的读锁阻塞。 =》 等待B读锁释放

                整理一下上述顺序:
                A事务持有读锁不释放，等待B事务释放读锁
                B事务持有读锁不释放，等待A事务释放读锁
                死锁发生。
1
2
3
4
5
6
7
8
9
10

3.1.2 RC 解决不可重复读，使用for update 加锁

项目isolation分支 ：

• 示例代码 NonRepeatableReadFixByLockSpec
• 同样条件下，相比 lock in share mode , 排他锁牺牲了并发度，提升了一致性。

3.2 RC 幻读

项目isolation分支 ：

• 示例代码 PhantomRowSpec
• 由于RC不支持gap锁，所以当出现范围查询（更新）时，只会在原有的记录上加行锁。如果存在以下并发顺序：

-- id 序列: 1 2 3 5 
-- 事务A
update `bank_account` set `balance` = `balance` + 1  where id > 2; //更新两行 只锁住了 id = 3  和 id = 5 的行
-- 事务B
-- 插入id = 4 的记录成功
INSERT INTO `bank_account` (id, balance) valuse (4, 5);
-- 事务A
update `bank_account` set `balance` = `balance` - 1  where id > 2; // 同样的where条件却更新了三行, 造成幻读
1
2
3
4
5
6
7
8

3.2.1 解决幻读

• RC 禁用了GAP锁（外键约束除外）理论上是不能解决幻读问题的。
• RR启用了GAP锁如果RR级别的并发量可以满足目前使用，把隔离级别升级到RR即可。
• 事务A可以把id查询先查出来，第二次更新使用 id = 命中条件，绕开了id=4的记录
• 把两次更新操作写成原子提交（下文会提到）

4. RR（及以下） 的并发隐患

4.1 写倾斜（write skew）

• 上文提到的更新丢失是因为对同对象的读写，可以很方便的进行原子提交和加锁，但是对于多对象的读写就更为复杂了

4.1.1 写倾斜现象

摘自《数据密集型应用系统设计》

-- 业务逻辑：保证有两名医生目前在值班，则自己可以请假

-- 1234值班表中正在值班的医生人数
-- 事务A
select count(*) as currentlyOncall from doctor where on_call = true and shift_id = 1234;
-- 事务B
select count(*) as currentlyOncall from doctor where on_call = true and shift_id = 1234;
-- 事务A
if (currentlyOncall >= 2) {
	update doctoer set on_call = false where name = 'Alice' and and shift_id = 1234;
}
-- 事务B
if (currentlyOncall >= 2) {
	update doctoer set on_call = false where name = 'Bob' and and shift_id = 1234;
}

-- Alice 和 Bob 都觉得自己请假不会影响到值班，事实是他们互相都认为对方不会请假，所以都请假了，导致无人值班。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

解决写倾斜

• 加锁
• 换序，先更新后查询，如果查询的结果有误则回滚。由happen-before原则保证有一次查询一定是最新的值。

5. 任意隔离级别的并发隐患

5.1 非原子提交造成的更新丢失

• 隐患: 先查再改，属于业务代码的疏忽，使用ORM框架其实很容易写出这样的逻辑

select `balance` as currentBalance from  `bank_account` where id = 1;
update `balance` set `balance` = currentBalance + 1 where id = 1;
1
2

5.1.2 解决更新丢失

• 修复：数据库级别的原子更新 (最优)

update `balance` set `balance` = `balance` + 1 where id = 1;
1

• 修复：无法使用原子更新，则加互斥锁

select `balance` as currentBalance from  `bank_account` where id = 1 for update;
update `balance` set `balance` = currentBalance + 1 where id = 1;
1
2

5.2 缺少实体冲突

• 大多数场景可以用加 for update 锁解决，那么缺少这种行数据怎么办呢? 如：同一个会议室不能在同一个整点被预定，这时候预定记录是不存在的

5.2.1 解决缺少实体冲突

• 加入唯一索引
• 不够优雅的做法是把 时间-会议室 做成个组合表，for update 加在这个表的记录上，起到互斥的作用。

6. 脚本化执行实现原子提交

• 使用LUA / Groovy 脚本让MySQL按严格顺序执行脚本里的语句
• 存储过程

7. 未来

对于MySQL而言，RR也无法做到解决所有隐患。而使用Serializable又大大增加了死锁几率。如果既能获得RR的快照隔离及Serializable的一致性保证那就太好了，有幸的是，Serializable Snapshot Isolation (SSI) 被提出。

• PostgreSQL9.1 后使用了 SSI。相比于其他并发控制机制，SSI尚需在实践中证明其性能。即使如此，它很有可能成为未来数据库的标配 —— 《数据密集型应用系统设计》

参考资料

MySQL 5.7 Manual
《数据密集型应用系统设计》