mysql8事务级别_mysql8 参考手册--InnoDB 事务隔离级别

在InnoDB事务模型中，目标是将多版本数据库的最佳属性 与传统的两阶段锁定相结合。InnoDB在默认情况下，采用Oracle风格，在行级别执行锁定并以非锁定一致读取的形式运行查询 。锁信息以 InnoDB节省空间的方式存储，因此不需要锁升级。通常，允许多个用户锁定InnoDB表中的每一行或该行的任何随机子集，而不会导致 InnoDB内存耗尽。

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

InnoDB报价由SQL描述的所有四个事务隔离级别：1992标准： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默认隔离级别InnoDB是 REPEATABLE READ。

用户可以使用该SET TRANSACTION语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，请--transaction-isolation在命令行或选项文件中使用该 选项。有关隔离级别和级别设置语法的详细信息，请参见 第13.3.7节“ SET TRANSACTION语句”。

InnoDB支持使用不同的锁定策略在此描述的每个事务隔离级别 。您可以在默认数据REPEATABLE READ级别上实现高度一致性 ，以便对ACID合规性很重要的关键数据进行操作 。或者，在批量报告之类的情况下，精确的一致性和可重复的结果比最小化锁定开销不那么重要时，也可以放宽READ COMMITTED甚至 使用一致性规则 READ UNCOMMITTED。 SERIALIZABLE实施比甚至更严格的规则REPEATABLE READ，并且主要用于特殊情况下(例如XA)事务以及用于解决并发和死锁问题 。

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

REPEATABLE READ

这是的默认隔离级别 InnoDB。 同一事务中的一致读取读取由第一次读取建立的 快照。这意味着，如果您SELECT 在同一事务中发出多个普通(非锁定)语句，则这些 SELECT语句彼此之间也是一致的。请参见 第15.7.2.3节“一致的非锁定读取”。

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

对于具有唯一搜索条件的唯一索引， InnoDB仅锁定找到的索引记录，而不锁定其 前的空白。

对于其他搜索条件，InnoDB 使用间隙锁定或 下一键锁定 来锁定扫描的索引范围， 以阻止其他会话插入该范围所覆盖的间隙。

READ COMMITTED

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

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

由于禁用了间隙锁定，因此可能会产生幻影问题，因为其他会话可以在间隙中插入新行。

READ COMMITTED隔离级别 仅支持基于行的二进制日志记录 。如果READ COMMITTED与配合 使用binlog_format=MIXED，服务器将自动使用基于行的日志记录。

使用READ COMMITTED具有其他效果：

对于UPDATE或 DELETE语句， InnoDB仅对其更新或删除的行持有锁。MySQL评估WHERE条件后，将释放不匹配行的记录锁 。这大大降低了死锁的可能性，但是仍然可以发生。

对于UPDATE语句，如果某行已被锁定，则InnoDB 执行“ 半一致 ”读取，将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否符合 WHERE条件 UPDATE。如果该行匹配(必须更新)，则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;

在这种情况下，表没有索引，因此搜索和索引扫描将隐藏的聚集索引用于记录锁定(请参见第15.6.2.1节“聚集索引和二级索引”)，而不是使用索引列。

假设一个会话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执行each时 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相反， 如果使用if ，则第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可以确定该行是否符合以下 WHERE条件 UPDATE：

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隔离级别可以在启动时设置或在运行时更改。在运行时，可以为所有会话全局设置，也可以为每个会话单独设置。

READ UNCOMMITTED

SELECT语句以非锁定方式执行，但是可能会使用行的早期版本。因此，使用此隔离级别，此类读取不一致。这也称为 脏读。否则，此隔离级别的工作方式类似于 READ COMMITTED。

SERIALIZABLE

此级别类似于REPEATABLE READ，但是InnoDB将所有普通SELECT 语句隐式转换为SELECT ... FOR SHAREif autocommit禁用。如果 autocommit启用，则 SELECT是其自身的事务。因此，它被认为是只读的，并且如果以一致的(非锁定)读取方式执行并且不需要阻塞其他事务就可以序列化。(SELECT如果其他事务已修改所选行，则要强制平原 阻止，请禁用 autocommit。)