MySQL事务隔离级别

neverstopforcode

2020-03-24

MySQL8中隔离级别的变量跟之前的版本不一样，之前是tx_isolation，MySQL8改成了transaction_isolation。查看当前隔离级别的命令是

mysql> select @@global.transaction_isolation,@@transaction_isolation;

+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+

其它参考：MySQL 四种事务隔离级的说明

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

以下内容参考了维基百科：事务隔离

创建测试表users并插入测试数据

mysql> CREATE TABLE users (id int(11) NOT NULL, name varchar(20), age int(11), PRIMARY KEY(id)) ENGINE=InnoDB;

mysql> INSERT INTO users values (1, ‘Joe‘, 20), (2, ‘Jill‘, 25);

mysql> select * from users;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Joe  |   20 |
|  2 | Jill |   25 |
+----+------+------+

脏读(Dirty reads)

示例1：隔离级别是未提交读(READ UNCOMMITTED)，导致脏读(dirty read)。在我们的例子中，事务2修改了一行，但是没有提交，事务1读了这个没有提交的数据。现在如果事务2回滚了刚才的修改或者做了另外的修改的话，事务1中查到的数据就是不正确的了。在这个例子中，事务2回滚后就没有id是1，age是21的数据行了。

-- 设置隔离级别为未提交读
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

             Session A                              Session B

           START TRANSACTION;                       START TRANSACTION;
time
|          /* Query 1 */
|          SELECT age FROM users WHERE id = 1;
|          /* will read 20 */
|                                                   /* Query 2 */
v                                                   UPDATE users SET age = 21 WHERE id = 1;
                                                    /* No commit here */

           /* Query 1 */
           SELECT age FROM users WHERE id = 1;
           /* will read 21 */
                                                    ROLLBACK; /* lock-based DIRTY READ */

不可重复读(Non-repeatable reads)

示例2：隔离级别是读已提交(READ COMMITTED)，导致不可重复读。在这个例子中，事务2提交成功，因此他对id为1的行的修改就对其他事务可见了。但是事务1在此前已经从这行读到了另外一个“age”的值。在可串行化（SERIALIZABLE）和可重复读的隔离级别，数据库在第二次SELECT请求的时候应该返回事务2更新之前的值。在提交读和未提交读，返回的是更新之后的值，这个现象就是不可重复读。

-- 设置隔离级别为提交读
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

             Session A                              Session B

           START TRANSACTION;                       START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 1;
|          /* will read age=20 */
|                                                   /* Query 2 */
v                                                   UPDATE users SET age = 21 WHERE id = 1;
                                                    COMMIT; /* in multiversion concurrency 
                                                        control, or lock-based READ COMMITTED */

           /* Query 1 */
           SELECT * FROM users WHERE id = 1;
           /* will read age=21 */
           COMMIT; /* lock-based REPEATABLE READ */

有两种策略可以避免不可重复读。一个是要求事务2延迟到事务1提交或者回滚之后再执行。这种方式实现了T1, T2 的串行化调度。串行化调度可以支持可重复读。

另一种策略是多版本并发控制。为了得到更好的并发性能，允许事务2先提交。但因为事务1在事务2之前开始，事务1必须在其开始执行时间点的数据库的快照上面操作。当事务1最终提交时候，数据库会检查其结果是否等价于T1, T2串行调度。如果等价，则允许事务1提交，如果不等价，事务1需要回滚并抛出个串行化失败的错误。

使用基于锁的并发控制，在可重复读的隔离级别中，ID=1的行会被锁住，在事务1提交或回滚前一直阻塞语句2的执行。在提交读的级别，语句1第二次执行，age已经被修改了。

在多版本并发控制机制下，可序列化(SERIALIZABLE)级别，两次SELECT语句读到的数据都是事务1开始的快照，因此返回同样的数据。但是，如果事务1试图UPDATE这行数据，事务1会被要求回滚并抛出一个串行化失败的错误。

在提交读隔离级别，每个语句读到的是语句执行前的快照，因此读到更新前后不同的值。在这种级别不会有串行化的错误（因为这种级别不要求串行化），事务1也不要求重试。

幻影读(Phantom reads)

以下内容参考了：mysql 幻读的详解、实例及解决办法

幻读错误的理解：说幻读是事务A 执行两次 select 操作得到不同的数据集，即 select 1 得到 10 条记录，select 2 得到 11 条记录。这其实并不是幻读，这是不可重复读的一种，只会在 R-U R-C 级别下出现，而在 mysql 默认的 RR 隔离级别是不会出现的。

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

-- 设置隔离级别为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 3;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (3, ‘Woody‘, 28);
                                                        COMMIT;

           /* Query 3 */
           INSERT INTO users values (3, ‘Woody‘, 28);
           /* ERROR 1062 (23000): Duplicate entry ‘3‘ 
              for key ‘PRIMARY‘ */
           
           /* Query 4 */
           SELECT * FROM users WHERE id = 3;
           /* Empty set */
           COMMIT;

会话A ：主事务，检测表中是否有 id 为 3 的记录，没有则插入，这是我们期望的正常业务逻辑。

会话B ：干扰事务，目的在于扰乱会话A 的正常的事务执行。

在 RR 隔离级别下，Query 1、Query 2 是会正常执行的，Query 3 则会报错主键冲突，对于会话A 的业务来说是执行失败的，这里会话A 就是发生了幻读，因为会话A 在 Query 1 中读取的数据状态并不能支撑后续的业务操作，会话A：“见鬼了，我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以”。会话A 不敢相信的又执行了 Query 4，发现和 Query 1 读取的结果是一样的（RR下的 MMVC机制）。此时，幻读无疑已经发生，T1 无论读取多少次，都查不到 id = 3 的记录，但它的确无法插入这条他通过读取来认定不存在的记录（此数据已被会话B插入），对于会话A 来说，它幻读了。

其实 RR 也是可以避免幻读的，通过对 select 操作手动加行X锁（SELECT ... FOR UPDATE 这也正是 SERIALIZABLE 隔离级别下会隐式为你做的事情），同时还需要知道，即便当前记录不存在，比如 id = 3 是不存在的，当前事务也会获得一把记录锁（因为InnoDB的行锁锁定的是索引，故记录实体存在与否没关系，存在就加行X锁，不存在就加 next-key lock间隙X锁），其他事务则无法插入此索引的记录，故杜绝了幻读。

在 SERIALIZABLE 隔离级别下，step1 执行时是会隐式的添加行(X)锁 / gap(X)锁的，从而 Query2 会被阻塞，Query3 会正常执行，待 T1 提交后，T2 才能继续执行（主键冲突执行失败），对于 T1 来说业务是正确的，成功的阻塞扼杀了扰乱业务的T2，对于T1来说他前期读取的结果是可以支撑其后续业务的。

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达读-读，幻读则是说读-写，用写来证实读的是鬼影。

RR级别下防止幻读

RR级别下只要对 SELECT 操作也手动加行（X）锁即可类似 SERIALIZABLE 级别（它会对 SELECT 隐式加锁），即大家熟知的：

# 这里需要用 X锁， 用 FOR SHARE 拿到 S锁 后我们没办法做 写操作
SELECT `id` FROM `users` WHERE `id` = 3 FOR UPDATE;

如果 id = 3 的记录存在则会被加行（X）锁，如果不存在，则会加 next-lock key / gap 锁（范围行锁），即记录存在与否，mysql 都会对记录应该对应的索引加锁，其他事务是无法再获得做操作的。

这里我们就展示下 id = 3 的记录不存在的场景，FOR UPDATE 也会对此 “记录” 加锁，要明白，InnoDB 的行锁（gap锁是范围行锁，一样的）锁定的是记录所对应的索引，且聚簇索引同记录是直接关系在一起的。

-- 设置隔离级别为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 3 FOR UPDATE;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (3, ‘Woody‘, 28);
                                                        /* 被阻塞，ERROR 1205 (HY000): Lock wait timeout exceeded; 
                                                           try restarting transaction */

           /* Query 3 */
           INSERT INTO users values (3, ‘Woody‘, 28);
           /* Query OK, 1 row affected */
           
           COMMIT;
           /* Query OK, 0 rows affected */

SERIALIZABLE级别杜绝幻读

在此级别下，我们便不需要对 SELECT 操作显式加锁，InnoDB会自动加锁，事务安全，但性能很低。

-- 设置隔离级别为可串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          select * from users where id = 4;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (4, ‘Bill‘, 29);
                                                        /* 被阻塞，ERROR 1205 (HY000): Lock wait 
                                                           timeout exceeded; try restarting transaction */

           /* Query 3 */
           INSERT INTO users values (4, ‘Bill‘, 29);
           /* Query OK, 1 row affected */
           
           COMMIT;
           /* Query OK, 0 rows affected */

step1: 会话A 查询 id = 4 的记录，InnoDB 会隐式的对齐加 X锁
step2: 会话B 插入 id = 4 的记录，被阻塞
step3: 会话A 插入 id = 4 的记录，成功执行（会话B 依然被阻塞中）
step4: 会话A 成功提交（会话B 此时唤醒但主键冲突执行错误）
会话A事务符合业务需求成功执行，会话B干扰会话A失败。

总结

RR 级别作为 mysql 事务默认隔离级别，是事务安全与性能的折中，可能也符合二八定律（20%的事务存在幻读的可能，80%的事务没有幻读的风险），我们在正确认识幻读后，便可以根据场景灵活的防止幻读的发生。

SERIALIZABLE 级别则是悲观的认为幻读时刻都会发生，故会自动的隐式的对事务所需资源加排它锁，其他事务访问此资源会被阻塞等待，故事务是安全的，但需要认真考虑性能。

InnoDB的行锁锁定的是索引，而不是记录本身，这一点也需要有清晰的认识，故某索引相同的记录都会被加锁，会造成索引竞争，这就需要我们严格设计业务sql，尽可能的使用主键或唯一索引对记录加锁。索引映射的记录如果存在，加行锁，如果不存在，则会加 next-key lock / gap 锁 / 间隙锁，故InnoDB可以实现事务对某记录的预先占用，如果记录存在，它就是本事务的，如果记录不存在，那它也将是本是无的，只要本是无还在，其他事务就别想占有它。

另外可以参考：以后别再说你不懂MySQL中的「幻读」了

事务隔离级别隔离级别 mysql事务数据库事务事务 mysql mysql数据库