• InnoDB 事务模型
  • 事务隔离级别
    • REPEATABLE READ
    • READ COMMITTED
    • READ UNCOMMITTED
    • SERIALIZABLE
  • 不加锁的一致性读取
    • Consistent Nonlocking Reads
  • 加锁的读取
    • 锁定读取示例
      • 共享模式读取
      • 加锁读取
    • 使用 NOWAIT 和 SKIP LOCKED 锁定并发读取

InnoDB 事务模型

InnoDB 事务模型旨在将多版本（multi-versioning） 数据库的最佳属性与传统的两阶段锁定结合起来。

InnoDB 在行的级别执行锁定，并默认以 Oracle 的风格通过 不加锁的一致性读取（consistent read） 的方式执行查询。

InnoDB 中的锁信息通过空间高效的方式存储，因此，不需要锁升级。通常，InnoDB 允许多个用户锁定表中的每一行或所有行的任意子集，而不会导致 InnoDB 内存耗尽。

事务隔离级别

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

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

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

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

InnoDB 使用不同的锁定策略支持每个事务的隔离级别：

• 对于对 ACID 合规性很重要的关键数据的操作，可以强制执行与默认 REPEATABLE READ 级别的高度一致性。

• 也可以使用 READ COMMITTED 甚至 READ UNCOMMITTED 来放宽一致性规则，例如，在批量报告等情况下，精确的一致性和可重复的结果，并不比最小化锁定开销重要。

• SERIALIZABLE 强制执行比 REPEATABLE READ 更严格的规则，主要用于特殊情况，例如，XA 事务以及解决并发和死锁问题。

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

REPEATABLE READ

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

对于锁定读取（SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE）、 UPDATE 和 DELETE 语句，加锁取决于该语句是否在唯一索引上使用等值查询或者范围查询：

• 对于在唯一索引上执行等值查询，InnoDB 仅锁定匹配的索引记录，而不会锁定该行之前的间隙（gap）。

• 对于在唯一索引上执行范围查询，InnoDB 会锁定扫描的索引范围，使用间隙锁（gap locks）或下一键锁（next-key locks），来防止其他会话插入该范围所覆盖的间隙。

READ COMMITTED

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

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

由于间隙锁定被禁用，因此可能会出现幻读问题，因为其他会话可以将新行插入间隙中。

READ COMMITTED 隔离级别仅支持行级的bin log。如果 READ COMMITTED 隔离级别下设置 binlog_format = MIXED，服务器会自动使用基于行的日志记录。

使用 READ COMMITTED 还有额外的效果：

• 对于 UPDATE 或 DELETE语句，InnoDB仅对其更新或删除的行持有锁。

  MySQL 评估 WHERE条件后，将释放不匹配行的记录锁。这大大降低了死锁的可能性，但死锁仍然可能发生。

• 对于 UPDATE 语句，如果某行已经被锁定，InnoDB 则执行“半一致性”读取，将最新提交的版本返回给 MySQL，以便 MySQL 判断该行是否符合 UPDATE 语句的 WHERE 条件。

  如果该行匹配，必须执行更新操作，MySQL 会再次读取该行，此时，InnoDB 要么立即锁定改行，要么等待改行的锁。

例如，假设有两个会话A、B，其执行 SQL 语句的时序如下：

注意，此时，由于表 t 没有索引，搜索和索引扫描使用隐藏的聚集索引来进行记录锁定而不是索引列。

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 执行 “半一致性”读（“semi-consistent” read），将读取的每一行的最新提交版本返回给 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 使用索引，则在获取和保留记录锁时只考虑索引列。

例如，对于如下示例：

会话 A 的 UPDATE 语句会在 b = 2 的每一行上获取并保留一个 X 锁；会话 B 的 UPDATE 语句会在尝试获取同一记录上的 X 锁时会阻塞，因为它也使用 b 列上定义的索引。

隔离 READ COMMITTED 级别可以在启动时设置或在运行时更改。在运行时，可以为所有会话全局设置它，也可以为每个会话单独设置。

READ UNCOMMITTED

SELECT 语句以不加锁的方式执行，但可能会使用到某些行的早期版本。因此，使用此隔离级别的读取是不一致的，这也称为脏读（dirty read）。除此以外，该隔离级别的工作方式类似于 READ COMMITTED。

SERIALIZABLE

• 如果 autocommit 被禁用，InnoDB 会将所有的普通 SELECT 语句隐式转换为 SELECT ... FOR SHARE，此时，串行化（SERIALIZABLE）级别类似于 REPEATABLE READ。

• 如果 autocommit 被启用，则 SELECT 语句是它自己的事务，因此，它是只读的，如果作为一致（不加锁）读取执行，则可以序列化，并且不需要阻止其他事务。

注意，要在其他事务修改了所选行的情况下强制阻止普通的 SELECT 语句，需要禁用 autocommit。

从 MySQL 授权表读取数据（通过联接列表或子查询）但不修改数据的 DML 操作不会获取 MySQL 授权表上的读锁，无论隔离级别如何。

不加锁的一致性读取

Consistent Nonlocking Reads

一致性读（consistent read）：一种读操作，它使用快照信息基于某个时间点呈现查询结果，而不管同时运行的其他事务执行的更改如何。如果查询的数据被另一个事务更改，则根据 undo log 的内容重建原始数据。这种技术通过强制事务等待其他事务完成，避免了一些可能降低并发性的锁定问题。

一致性读取意味着 InnoDB 使用多版本控制（multi-versioning）向查询呈现数据库在某个时间点的快照。

该查询会看到该时间点之前提交的事务所做的更改，而不会看到稍后或未提交的事务所做的更改，但是，该查询会看到同一事务中较早语句所做的更改。此时，会导致以下异常：如果更新表中的某些行，则会出现 SELECT 会看到已更新行的最新版本，但也可能会看到任何行的旧版本。如果其他会话同时更新同一个表，导致该事务可能会看到该表处于数据库中从未存在的状态。

不同隔离级别下的一致性读：

• 如果事务隔离级别为 REPEATABLE READ，则同一事务中的所有一致读取都会读取该事务中第一个此类读取建立的快照；

  此时，快照为第一次读操作的时间。我们可以通过提交当前事务并在之后执行新的查询来获取更新的查询快照。

• 如果事务隔离级别为 READ COMMITTED，事务中的每个一致读取都会设置并读取其自己的最新快照。

  此时，快照将重置为每个一致读操作的时间。

一致性读是 InnoDB 处理 READ COMMITTED 和 REPEATABLE READ 隔离级别下的 SELECT 语句的默认模式。由于一致性读不会对它访问的表加锁，因此，在对表执行一致性读时，其他会话可以自由修改这些表。

在 REPEATABLE READ 隔离级别下运行，当执行一致读取时，即普通的 SELECT语句时， InnoDB 为当前事务分配一个时间点（timepoint），事务中的查询语句将根据该时间点来查看数据库。

如果另一个事务删除一行并在分配您的时间点后提交，您不会看到该行已被删除。插入和更新的处理方式类似。

数据库状态的快照适用于事务内的 SELECT 语句，而不一定适用于 DML 语句。如果插入或修改某些行，然后提交该事务，则从另一个并发事务执行的 DELETE 或 UPDATE 语句，可能会影响那些刚刚提交的行，即使当前会话无法查询它们。如果一个事务确实更新或删除了由不同事务提交的行，则这些更改对当前事务确实可见。

例如，可能会遇到如下情况：

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

我们可以通过提交事务，然后执行另一个 SELECT 或 START TRANSACTION with CONSISTENT SNAPSHOT 来提前时间点，这称为多版本并发控制。

例如，对于如下示例：

仅当 B 提交了插入事务，并且 A 也提交了当前事务时，会话 A 才能看到 B 插入的行，因此，时间点提前超过了 B 的提交。

如果需要查看数据库的“最新”状态，请使用 READ COMMITTED 隔离级别或锁定读取：

SELECT * FROM t FOR SHARE;

通过 READ COMMITTED 隔离级别，事务中的每个一致性读都会设置并读取其自己的最新快照。使用 FOR SHARE 时，会发生锁定读取：SELECT 会阻塞，直到包含最新行的事务结束。

一致性读不适用于某些 DDL 语句：

• 一致性读不适用于 DROP TABLE 语句，因为 MySQL 无法使用已删除的表，此时，InnoDB 已经破坏了该表；

• 一致性读不适用于 ALTER TABLE 语句，它会创建原始表的临时副本，并在创建临时副本时删除原始表。

  当在事务中重新执行一致读取时，新表中的行对事务不可见，因为在拍摄事务快照时，这些行不存在。这种情况下，事务会返回如下错误：

  ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”

对于带有 SELECT 子句，但未指定 FOR UPDATE 或者 FOR SHARE 的读取操作，例如，INSERT INTO ... SELECT、 UPDATE ... (SELECT) 和 CREATE TABLE ... SELECT

• 默认情况下，InnoDB 对这些语句使用更强的锁，并且该 SELECT 部分的行为类似于 READ COMMITTED，其中每个一致读取（即使在同一事务中）也会设置并读取其自己的新快照。

• 要在这种情况下执行非锁定读取，需要将事务的隔离级别设置为 READ UNCOMMITTE D或 READ COMMITTED，以避免对表中读取的行加锁。

加锁的读取

如果查询数据然后在同一事务内插入或更新相关数据，则常规 SELECT 语句无法提供足够的保护。其他事务可以更新或删除刚刚查询的相同记录。InnoDB支持两种类型的锁定读取，提供额外的安全性：

• SELECT ... FOR SHARE

  它会对读取的指定行设置共享锁，其他会话可以读取这些行，但当前事务提交之前，其他事务无法修改它们。如果其中任何行被尚未提交的另一个事务更改，您的查询将等待该事务结束，然后使用最新值。

  SELECT ... FOR SHARE 需要 SELECT特权。SELECT ... FOR SHARE 语句不会获取 MySQL 授权表上的读锁。

• SELECT ... FOR UPDATE

  对于通过索引查询索引记录，SELECT ... FOR UPDATE 会锁定匹配的行及其关联的索引条目，就像在这些行执行 UPDATE 语句一样加锁行为一样，它将阻止其他事务更新这些行、执行 SELECT ... FOR SHARE 语句或读取相关的数据。

  一致性读会忽略对读取视图中存在的记录设置的任何锁定。

  SELECT ... FOR UPDATE 需要 SELECT 权限以及至少 DELETE、LOCK TABLES 或 UPDATE 权限之一。

这些子句主要在处理单个表中或跨多个表中的树结构或图形结构数据时有用。您将边缘或树枝从一个地方遍历到另一个地方，同时保留返回并更改任何这些 “指针”值的权利。

当事务提交或回滚时，FOR SHARE 和 FOR UPDATE 查询设置的所有锁都会被释放。

注意，仅当禁用自动提交时才可以锁定读取（通过使用 START TRANSACTION 开始事务或将自动提交设置为 0）。

外部语句中的锁定读取子句不会锁定嵌套子查询中表的行，除非子查询中也指定了锁定读取子句。

例如，对于如下两个示例：

• 下面的查询语句不会锁定表 t2 中的行：

  SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;
  

• 下面的查询语句会锁定表 t2 中的行：

  SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;
  

锁定读取示例

共享模式读取

假设您要向表 child 中新插入一行，并确保子行在表 parent 中具有父行，应用程序代码需要确保整个操作序列中的引用完整性。

首先，使用一致性读来查询表 PARENT 并验证父行是否存在。您可以安全地将子行插入表中吗 CHILD？SELECT不，因为某些其他会话可能会在您和您之间删除父行 INSERT，而您却没有意识到。

如果要安全地在子表 CHILD 中插入一行记录，我们需要避免在同一时刻，其他的会话在父表 PARENT 中删除或者插入一行记录。

为了避免这种潜在问题，我们需要使用下面的语句在父表中执行查询：

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

FOR SHARE 查询返回父记录 'Jones' 后，我们可以安全地将子记录添加到 CHILD 表中并提交事务。期间，其他任何尝试获取表 PARENT 中对应行的排他锁的事务都会等待，直到当前事务完成，即直到所有表中的数据处于一致状态。

加锁读取

再举一个例子，考虑表中的一个整数计数器字段 CHILD_CODES，用于为添加到表中的每个子项分配唯一标识符 CHILD。不要使用一致性读或共享模式读来读取计数器的当前值，因为数据库的两个用户可能会看到相同的计数器值，并且如果两个事务尝试将具有相同标识符的行添加到 CHILD 表，则会发生重复键错误。

在这里，这 FOR SHARE 不是一个好的解决方案，因为如果两个用户同时读取计数器，则至少其中一个用户在尝试更新计数器时会陷入死锁。

要实现计数器的读取和递增，可以先使用 FOR UPDATE 执行计数器的锁定读取，然后递增计数器。如下面的查询语句所示：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE 可以读取最新的可用数据，并在其读取的每一行上设置排它锁。因此，SELECT ... FOR UPDATE 设置的锁与其他SQL语句中的 UPDATE 在这个行上设置的锁相同。

前面的示例描述了是 SELECT ... FOR UPDATE 如何工作的，实际上，在 MySQL 中，生成唯一标识符的具体任务实际上只需对表进行一次访问即可完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

其中，上述 SELECT 语句仅检索标识符信息（特定于当前连接），它不访问任何表。

使用 NOWAIT 和 SKIP LOCKED 锁定并发读取

如果某行被事务锁定，则请求同一锁定行的 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 事务必须等待，直到阻塞事务释放行锁，这种行为可以防止事务更新或删除其他事务查询更新的行。但是，如果我们希望查询在请求的行被锁定时立即返回，或者，如果可以接受从结果集中排除锁定的行，则无需等待行锁被释放。

为了避免等待其他事务释放行锁，NOWAIT 和 SKIP LOCKED 选项可以与 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 锁定读取语句一起使用。

• NOWAIT

  使用 NOWAIT 的锁定读取不需要等待获取行锁，查询会立即执行，如果请求的行被锁定，则会失败并出现错误。

• SKIP LOCKED

  使用 SKIP LOCKED 的锁定读取不会等待获取行锁，查询会立即执行，最后，它会从结果集中删除锁定的行。

  注意，跳过锁定行的查询会返回不一致的数据视图。因此，SKIP LOCKED 不适合一般事务性工作。但是，当多个会话访问同一个队列状表时，它可用于避免锁争用。

NOWAIT 和 SKIP LOCKED 仅适用于行级锁，使用 NOWAIT 或 SKIP LOCKED 的语句对于基于语句的复制来说是不安全的。

NOWAIT 和SKIP LOCKED 的用法，参考以下示例：

执行结果如下：

• 会话 1 启动一个在单个记录上获取行锁的事务；

• 会话 2 尝试使用该选项对同一记录进行锁定读取 NOWAIT，由于请求的行已被会话 1 锁定，因此，锁定读取会立即返回并出现错误；

• 在会话 3 中，锁定读取返回SKIP LOCKED除会话 1 锁定的行之外的请求行。

参考：

• 15.7.2.4 Locking Reads