MySQL8中文手册-事务隔离级别

更多内容请点击MySQL8中文手册

15.7.2.1 事务隔离级别

事务隔离是数据处理工作的基础之一。隔离性表示的是ACID中的I，事务隔离级别主要用来平衡在多个事务同时进行查询和变更时结果的性能、可靠性、一致性和可重复性之间的关系。

InnoDB提供了在SQL:1992标准中定义的四种隔离级别，未提交可读（READ_UNCOMMITED）、提交可读（READ_COMMITTED）、可重复读（REPEATABLE_READ）和串行访问（SERIALIZABLE）。InnoDB的默认隔离级别是可重复读。

用户可以使用SET TRANSACTION命令修改单个session或后续所有连接的隔离级别。在命令行中使用–transaction-isolation选项可以修改默认隔离级别。关于隔离级别的设置方法可以参考：Section 13.3.7, “SET TRANSACTION Statement”.

InnoDB使用不同的锁策略来支持每种不同的事务隔离级别。对于满足ACID非常重要的关键数据，可以使用默认的可重复读隔离级别，这个级别可以保证高度的一致性。在最大化的减少锁的开销比精确的一致性和可重复的结果更重要的场景下，比如批量报表，可以使用已提交可读或未提交可读级别，来放宽一致性规则。串行访问隔离级别执行比可重复读更加严格的一致性规则，主要用在特殊的场景下，比如XA事务或并发和死锁的问题排查中。

下面说明MySQL时如何支持不同事务隔离级别的，按照最常用的级别到最不常用的级别进行排序。

可重复读

这是InnoDB的默认隔离级别，在同一个事务中的一致性读，读取的是事务中第一次读时创建的快照。在这种隔离级别下，在事务中执行多个非阻塞的SELECT 语句，每个SELECT 结果与其他SELECT 是一致的。可参考：Section 15.7.2.3, “Consistent Nonlocking Reads”。对于需要锁的读操作（SELECT FOR UPDATE/FOR SHARE）、UPDATE、DELETE语句，锁策略取决于语句是否使用有唯一搜索条件的唯一索引，还是使用范围搜索条件。

• 对于唯一搜索条件的唯一索引，InnoDB只锁查询到的索引记录，而不锁前面的间隙。

• 对于其他的搜索条件，InnoDB会对扫描过的索引进行加锁，使用间隙锁和键前锁（next-key locks），来阻止其他session向扫描范围中的间隙中插入数据。对于间隙锁和键前锁，可参考：Section 15.7.1, “InnoDB Locking”.

提交可读

每次一致性读，即使是在同一个事务中，都会创建自己的快照，并从自己的快照中读取。对于一致性读的详细信息可参考：Section 15.7.2.3, “Consistent Nonlocking Reads”.

对于需要锁的读操作（SELECT FOR UPDATE/FOR SHARE）、UPDATE、DELETE语句，InnoDB只锁索引记录，不对前面的间隙上锁，因此允许对锁定记录前面的间隙中插入新数据。间隙锁只用在外键一致性检查和唯一键冲突检查上。

由于禁用间隙锁，其他session可以向间隙中插入数据，因此可能会出现幻读问题。对于幻读问题，可参考：Section 15.7.4, “Phantom Rows”.在读已提交隔离级别下，binlog只能使用row-based格式，如果设置了binlog_formate=MIXED，MySQL会自动使用row-based格式来记录。

使用提交可读隔离级别还有附加的影响：

• 对于UPDATE或DELETE语句，InnoDB只会对需要更新或删除的记录加锁。在MySQL执行WHERE条件后没有匹配的行，会释放这些记录上的锁。虽然这样可以大大的减少死锁的可能性，但仍然可能会出现死锁情况。

• 对于UPDATE语句，如果锁定了一行数据，InnoDB会执行半一致性读，返回最新提交的数据版本给MySQL，MySQL来判断这行数据是否满足UPDATE语句的WHERE条件。如果该行数据满足条件，MySQL会再次读取该行，这时InnoDB要么加锁或等待锁释放。

以下表为例：

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

在这种情况下，表没有索引，所以查询和索引扫描在主键聚簇索引上进行加锁（参考：Section 15.6.2.1, “Clustered and Secondary Indexes”）。

假如一个session执行了一个UPDATE语句：

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

同时，另外一个session执行UPDATE语句：

# Session B
UPDATE t SET b = 4 WHERE b = 2;

当InnoDB执行每个UPDATE语句时，首先在各自的行上加独占锁，然后确定是否需要修改行数据。如果InnoDB不修改行数据，就会释放锁。否则，InnoDB会占有锁直到事务结束。这会影响事务的处理过程，如下：

在默认的可重复读隔离级别下，第一个UPDATE语句会对查找过程中的遇到的每行加独占锁。
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
The second UPDATE blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back:
第二个UPDATE语句在获取锁时会被阻塞（第一个update语句已经获取了所有行的锁），在第一个UPDATE提交或者回滚之前，不能继续执行。
x-lock(1,2); block and wait for first UPDATE to commit or roll back

在已提交可读隔离模式下，第一个UPDATE语句会对查找过程中的每行加独占锁，随后，如果不需要对该行进行操作，就释放锁。
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

对于第二个UPDATE语句，InnoDB会进行半一致性读，返回最新的提交版本的行数据给MySQL，MySQL会判断这行数据是否满足UPDATE语句的WHERE条件。
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

但是，如果WHERE条件使用了有索引的列，InnoDB也使用了索引，在获取和保持记录锁时，只使用索引列。

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

已提交可读隔离级别可以在启动时设置，也可以在运行时进行修改。在运行时可以对所有session进行全局配置，也可以每个session单独进行配置。

未提交可读

SELECT语句执行过程中不需要加锁，但是用的数据可能是早版本的旧数据。因此这种隔离级别下，读取的数据是不一致的，也被称为脏读。其他方面，这种隔离级别跟已提交可读类似。

串行访问

这种隔离级别类似可重复读，但是在禁用autocommit的情况下，InnoDB会隐式将所有SELECT语句转换未SELECT … FOR SHARE语句。如果autocommit启用，每个SELECT语句独立成为一个事务。因此事务是只读的，如果进行不需要加锁的一致读，不需要被其他事务阻塞，可以序列化操作。

说明

从MySQL 8.0.22开始，从MySQL 授权表中读取数据（通过join查询或子查询）但不修改数据的DML操作，不会获取MySQL 授权表上的读锁，不受隔离级别的影响。