MySQL InnoDB【事务模型】之【事务隔离级别】全攻略

Coder.Ren

已于 2024-07-17 10:28:35 修改

阅读量827

点赞数 30

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-07-14 22:02:42 首次发布

本文链接：https://blog.csdn.net/qq_22129643/article/details/140423660

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

快速导航

- 事务隔离级别

事务隔离级别

事务隔离是数据库处理的基础之一。隔离是缩写ACID中的I；当多个事务同时进行更改和查询时，隔离级别是微调性能与可靠性、一致性和结果再现性之间平衡的设置。

InnoDB提供SQL:1992标准描述的所有四种事务隔离级别： READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

InnoDB 的默认隔离级别是 REPEATABLE READ。

用户可以使用 SET TRANSACTION 语句更改单个会话或所有后续连接的隔离级别。

要为所有连接设置服务器的默认隔离级别，可以在命令行或选项文件中使用 --transaction-isolation 选项。

InnoDB使用不同的锁定策略支持这里描述的每一种事务隔离级别。

你可以使用默认的REPEATABLE READ级别来执行高度的一致性，用于对ACID合规性非常重要的关键数据的操作。
在批量报告等情况下，精确的一致性和结果的可重复性没有最大限度减少锁定开销那么重要，这时可以使用 READ COMMITTED 或甚至 READ UNCOMMITTED 放宽一致性规则。
SERIALIZABLE 执行比 REPEATABLE READ 更严格的规则，主要用于特殊情况，如 XA 事务以及并发和死锁问题的故障诊断。

下面的列表描述了 MySQL 如何支持不同的事务级别。该列表从最常用的级别到最不常用的级别依次排列。

可重复读（REPEATABLE READ）

这是 InnoDB 的默认隔离级别。同一事务中的一致读取会读取第一次读取建立的快照。这意味着，如果在同一事务中发出多条普通（非锁定）SELECT 语句，这些 SELECT 语句之间也是一致的。

对于锁定读取（带有 FOR UPDATE 或 FOR SHARE 的 SELECT）、UPDATE 和 DELETE 语句，锁定取决于语句是使用带有唯一搜索条件的唯一索引，还是使用范围类型的搜索条件。

对于具有唯一搜索条件的唯一索引，InnoDB 只锁定找到的索引记录，而不锁定之前的空白。
对于其他搜索条件，InnoDB 会锁定所扫描的索引范围，使用间隙锁或下一键锁来阻止其他会话插入该范围所覆盖的间隙。

读提交内容（READ COMMITTED）

即使在同一个事务中，每次一致读取都会设置并读取自己的新快照。

对于锁定读取（带有 FOR UPDATE 或 FOR SHARE 的 SELECT）、UPDATE 语句和 DELETE 语句，InnoDB 只锁定索引记录，而不锁定它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。

由于间隙锁定被禁用，可能会出现幽灵行问题，因为其他会话可以在间隙中插入新记录。

READ COMMITTED 隔离级别仅支持基于行的二进制日志记录。如果使用带有 binlog\_format=MIXED 的 READ COMMITTED，服务器会自动使用基于行的日志记录。

使用 READ COMMITTED 还会产生其他影响：

对于 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;
```
在这种情况下，表中没有索引，因此搜索和索引扫描使用的是用于记录锁定的隐藏聚类索引，而不是索引列。

假设一个会话使用这些语句执行 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 会保留锁，直到事务结束。这对事务处理的影响如下。

在使用默认的 REPEATABLE READ 隔离级别时，第一个 UPDATE 会在读取的每一条记录上获取一个 x 锁，并且不会释放任何一个锁：
```
  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 提交或回滚后才会继续：
```
  x-lock(1,2); block and wait for first UPDATE to commit or roll back
```
如果使用 READ COMMITTED 代替，则第一次 UPDATE 会为读取的每一条记录获取 x 锁，并为未修改的记录释放 x 锁：
```
  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 使用了索引，那么在获取和保留记录锁时，只会考虑索引列。在下面的示例中，第一个 UPDATE 在 b = 2 的每条记录上获取并保留了一个 x 锁。第二个 UPDATE 在试图获取相同记录上的 x 锁时阻塞，因为它也使用了 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;
```
READ COMMITTED 隔离级别可在启动时设置，也可在运行时更改。在运行时，可以对所有会话进行全局设置，也可以对每个会话进行单独设置。