MySQL: InnoDB: READ COMMITTED vs REPEATABLE READ

如果购买阿里云的云数据库 RDS，会发现其默认隔离级别是 READ COMMITTED，而不是 MySQL 默认的 REPEATABLE READ，那么 READ COMMITTED 相比 REPEATABLE READ 有哪些优点呢？

SELECT @@GLOBAL.transaction_isolation;
+--------------------------------+
| @@GLOBAL.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
1 row in set (0.034 sec)

避免一致性读（consistent read）时出现异常

InnoDB 在 REPEATABLE READ 隔离级别下，处理 SELECT 时，会使用一致性读，事务每次 SELECT 读取的都是第一次 SELECT 时间点数据库的快照。但是如果事务对数据库做出了变更，事务就可以读到被它所更改的记录的最新数据，对于事务未更改的记录，事务仍旧读到这些记录的旧值，这意味着事务可能读取到异常的数据库状态^1。

有这样一张表，两用户的初始余额都为 20：

CREATE TABLE `balance` (
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `balance` int NOT NULL DEFAULT '0' COMMENT '账户余额',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_0900_ai_ci COMMENT '用户账户余额';

-- 用户 1 和 2 初始余额都为 20
INSERT INTO `balance` VALUES (1, 20), (2, 20);

先试验 REPEATABLE READ 隔离级别：

sequenceDiagram
    participant A as Session A
    participant B as Session B

    NOTE left of A: 用户 1 和用户 2 的初始余额都为 20

    NOTE left of A: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ#59;

    NOTE left of A: START TRANSACTION#59;

    NOTE left of A: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |      20 |<br/>|       2 |      20 |<br/>+---------+---------+

    NOTE right of B: 用户 1 转移 20 的余额给用户 2

    NOTE right of B: START TRANSACTION#59;

    NOTE right of B: UPDATE `balance` SET `balance` = `balance` - 20 WHERE `user_id` = 1#59;

    NOTE right of B: UPDATE `balance` SET `balance` = `balance` + 20 WHERE `user_id` = 2#59;

    NOTE right of B: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |       0 |<br/>|       2 |      40 |<br/>+---------+---------+

    NOTE right of B: COMMIT#59;

    NOTE left of A: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |      20 |<br/>|       2 |      20 |<br/>+---------+---------+

    NOTE left of A: 用户 2 存款 20

    NOTE left of A: UPDATE `balance` SET `balance` = `balance` + 20 WHERE `user_id` = 2#59;

    NOTE left of A: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |      20 |<br/>|       2 |      60 |<br/>+---------+---------+

    NOTE left of A: COMMIT#59;

    NOTE left of A: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |       0 |<br/>|       2 |      60 |<br/>+---------+---------+

在 REPEATABLE READ 隔离级别下，事务 A 先后读取到了数据库的 3 种状态：

	正/异常	用户1	用户2
状态1	正常	20	20
状态2	异常	20	60
状态3	正常	0	60

因为事务 A 更新了用户 2 的余额，所以事务 A 可以读到用户 2 的最新余额，但却读不到用户 1 的最新余额，这是一种异常的状态。要解决这个问题，可以在事务 A 中使用 locking read（SELECT ... FOR UPDATE/SHARE）或者设置事务 A 的隔离级别为 READ COMMITED。

再试验 READ COMMITED 隔离级别：

sequenceDiagram
    participant A as Session A
    participant B as Session B

    NOTE left of A: 用户 1 和用户 2 的初始余额都为 20

    NOTE left of A: START TRANSACTION#59;

    NOTE left of A: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |      20 |<br/>|       2 |      20 |<br/>+---------+---------+

    NOTE right of B: 用户 1 转移 20 的余额给用户 2

    NOTE right of B: START TRANSACTION#59;

    NOTE right of B: UPDATE `balance` SET `balance` = `balance` - 20 WHERE `user_id` = 1#59;

    NOTE right of B: UPDATE `balance` SET `balance` = `balance` + 20 WHERE `user_id` = 2#59;

    NOTE right of B: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |       0 |<br/>|       2 |      40 |<br/>+---------+---------+

    NOTE right of B: COMMIT#59;

    NOTE left of A: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |       0 |<br/>|       2 |      40 |<br/>+---------+---------+

    NOTE left of A: 用户 2 存款 20

    NOTE left of A: UPDATE `balance` SET `balance` = `balance` + 20 WHERE `user_id` = 2#59;

    NOTE left of A: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |       0 |<br/>|       2 |      60 |<br/>+---------+---------+

    NOTE left of A: COMMIT#59;

    NOTE left of A: SELECT * FROM `balance`#59;<br/>+---------+---------+<br/>| user_id | balance |<br/>+---------+---------+<br/>|       1 |       0 |<br/>|       2 |      60 |<br/>+---------+---------+

在 READ COMMITTED 隔离级别下，事务 A 先后读取到了数据库的 3 种状态：

	正/异常	用户1	用户2
状态1	正常	20	20
状态2	正常	0	40
状态3	正常	0	60

因为能读取到事务 B 已提交的最新数据，因此在 READ COMMITTED 隔离级别下，不会读取到异常数据。

并发、锁、死锁

READ COMMITTED 在很多情况下会不加锁，增加并发，且减少死锁的可能性：

没有 gap lock
Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.
For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.
INSERT INTO ... SELECT, UPDATE ... SELECT, and CREATE TABLE ... SELECT https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

dushaoshuai / dushaoshuai.github.io

MySQL: InnoDB: READ COMMITTED vs REPEATABLE READ #103

避免一致性读（consistent read）时出现异常

并发、锁、死锁