事务隔离级别 - MySQL 8.0官方文档笔记（二）

文档版本：8.0
来源：transaction isolation levels
上一篇：InnoDB中的锁

本篇主要介绍InnoDB的事务隔离级别。

事务隔离级别

事务隔离是数据库发展的基础之一。隔离性（Isolation）是ACID中的I；不同的隔离级别用于在性能和多事务并行查询时的可靠性、一致性、再现性之间微调。
InnoDB完整实现了SQL:1992标准中描述的四个隔离级别：读未提交，读已提交，可重复读，序列化。InnoDB的默认级别是可重复读。
用户可以通过SET TRANSACTION语句设置本会话或后续所有连接使用的隔离级别。如果要设置服务器针对所有连接的默认级别，在命令行或配置文件中使用--transaction-isolation选项。关于隔离级别和设置级别的语法可在 Section 13.3.7, “SET TRANSACTION Statement” 中详细了解。
针对在此描述的这些隔离级别，InnoDB支持对它们使用不同的加锁策略。例如，对于一些重要数据，需要严格遵循ACID，那么可以使用默认级别可重复读以确保更高的一致性；相反地，对于批量报告数据，精确的一致性与可重放的结果显然没有最小化锁开销重要，那么可以使用读已提交，甚至是读未提交来放宽一致性规则。序列化比可重复读更加严格，一般用于特殊场景，例如XA事务，或者排查并发与死锁问题。
下面列举MySQL是如何支持不同的隔离级别的。按照使用频率从高到低依次排序。

可重复读

可重复读是InnoDB的默认隔离级别。在这个级别下，同一事务内的快照读会使用第一次读取时生成的快照。这意味着如果你在同一事务内执行多条普通（无锁）SELECT语句，这些语句的结果是一致的。详见Section 15.7.2.3, “Consistent Nonlocking Reads”。
对于加锁读（SELECT FOR SHARE 或 SELECT FOR UPDATE），UPDATE语句和DELETE语句，加锁情况取决于语句是使用了唯一查询（使用了唯一索引），还是使用了范围条件查询。

对于唯一查询，InnoDB只会锁住命中的索引值，不会锁住之前的间隙。
对于其他查询，InnoDB锁住扫描到的索引，使用间隙锁或临键锁阻塞其他事务在间隙内的插入操作。对于间隙锁和临键锁，详见Section 15.7.1, “InnoDB Locking”。(也可以看我的翻译笔记：InnoDB中的锁)

读已提交

在这个级别下，每一次快照读，甚至在同一事务内，都会设置一次新快照来读取。对于快照读，详见Section 15.7.2.3, “Consistent Nonlocking Reads”。
对于加锁读（SELECT FOR SHARE 或 SELECT FOR UPDATE），UPDATE语句和DELETE语句，InnoDB只锁定索引值，不会锁住之前的间隙，因此其他事务可以在索引值旁进行插入。间隙锁只会用于外键约束检查和重复键检查。
因为间隙锁被禁用，其他会话可以在间隙中插入新行，所以可能会发生幻行。对于幻行，详见Section 15.7.4, “Phantom Rows”。
读已提交级别只支持数据行binlog。如果设置为混合模式，MySQL服务器会自动使用数据行binlog。
使用读已提交还会有以下影响：

对于UPDATE语句和DELETE语句，InnoDB只会锁住将要更新或删除的行。在MySQL计算出WHERE条件后，不匹配行的行锁会被释放。这使得死锁发生的概率大大降低，但仍无法杜绝。
对于UPDATE语句，如果行已经被锁住，InnoDB会执行一个“半快照读”，返回最后提交的版本给MySQL从而决定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”）。
让一个会话用下列语句执行UPDATE：

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

接着让一个会话用下列语句执行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

第二个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，从而决定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使用了这个索引，那么只会针对索引列进行获取、保持锁。在下面的例子中，第一个UPDATE会获取所有b=2的行的独占锁。第二个UPDATE在试图获取同一条记录的独占锁时被阻塞，并且也会使用b列索引。

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;

读已提交隔离级别可以在MySQL启动时和运行中设置，如果在运行中设置，可以设置为全局生效，或单个会话生效。

读未提交

SELECT语句将以无锁方式执行，但可能会使用行的旧版本数据。因此在这个级别下，读语句可能不一致，这也称作脏读。在其他方面，读未提交与读已提交表现相同。

序列化

这个级别与可重复读类似，但如果autocommit被禁用，InnoDB会隐式地将所有普通SELECT语句转换为SELECT ... FOR SHARE。如果autocommit被开启，SELECT语句自成一条事务。因此它是只读的，并且在快照（无锁）读时能够被序列化，也不会被其他事务阻塞。（如果要使普通SELECT语句在其它事务修改了行后阻塞，禁用autocommit。）

posted @ 2020-11-12 21:52 d1zzyboy 阅读(222) 评论(0) 编辑收藏举报

刷新页面返回顶部

well_tempered

事务隔离级别 - MySQL 8.0官方文档笔记（二）