InnoDB：InnoDB 事务模型

来自Wikioe
Eijux（讨论 | 贡献）2024年1月18日 (四) 22:30的版本 （→‎“READ COMMITTED”）
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)
跳到导航 跳到搜索


关于

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

事务隔离级别

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


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

  • InnoDB 的默认隔离级别是“REPEATABLE READ”。
  • 用户可以使用“SET TRANSACTION”语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，请在命令行或选项文件中使用“--transaction-isolation”选项。


InnoDB 使用不同的锁定策略支持此处描述的每个事务隔离级别。

您可以对“ACID”合规性很重要的关键数据进行操作，与默认“REPEATABLE READ”级别保持高度一致性。
或者，您可以使用“READ COMMITTED”甚至是“READ UNCOMMITTED”放宽一致性规则，例如在批量报告中，精确的一致性和可重复的结果不如最小化锁定开销重要。
“SERIALIZABLE”强制执行比“REPEATABLE READ”更为严格的规则，并且主要用于特殊情况下，例如 XA 事务以及对并发和死锁进行故障排除。

“REPEATABLE READ”

这是 InnoDB 的默认隔离级别。同一事务中的 Consistent reads（一致性读）读取由第一次读取构建的 snapshot（快照）。

  1. 【非锁定读】这意味着，如果您在同一事务中发出几个简单的（非锁定）SELECT语句，则这些 SELECT 语句彼此之间也是一致的。
  2. 【锁定读、更新】对于锁定读取（带有“FOR UPDATE”或“LOCK IN SHARE MODE”的SELECT），“UPDATE”和“DELETE”语句，锁定取决于该语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。【？？】
    1. 对于具有唯一搜索条件的唯一索引，InnoDB 仅锁定找到的索引记录，而不锁定之前的间隙。
    2. 对于其他搜索条件，InnoDB 锁定扫描的索引范围，使用“gap locks”（间隙锁）或“next-key locks”（下一键锁）阻止其他会话插入该范围所覆盖的间隙。

“READ COMMITTED”

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

  1. 【锁定读、更新】对于锁定读取（带有“FOR UPDATE”或“LOCK IN SHARE MODE”的SELECT），“UPDATE”和“DELETE”语句，InnoDB 仅锁定索引记录，而不锁定它们之间的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。
    • 由于禁用了间隙锁定，因此可能会产生幻影问题，因为其他会话可以在间隙中插入新行。
  2. “READ COMMITTED”隔离级别仅支持基于行的二进制日志记录。如果将“READ COMMITTED”与“binlog_format=MIXED”一起使用，则服务器将自动使用基于行的日志记录。【？】


使用“READ COMMITTED”具有其他效果：

  1. 对于“UPDATE”和“DELETE”语句，InnoDB 仅对其更新或删除的行保持锁定。 MySQL 评估“WHERE”条件后，将释放不匹配行的记录锁。（这大大降低了死锁的可能性，但是仍然可以发生。）
  2. 对于“UPDATE”语句，如果某行已被锁定，则 InnoDB 执行“半一致”读取，将最新的提交版本返回给 MySQL，以便 MySQL 可以确定该行是否与“UPDATE”的“WHERE”条件匹配。如果该行匹配（必须更新），则 MySQL 再次读取该行，这一次 InnoDB 要么锁定它，要么 await 对其进行锁定。【？】


示例 1：【？】
有如下表，表没有索引，因此搜索和索引扫描将隐藏的聚集索引用于记录锁定，而不是使用索引列。 

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 保留锁，直到事务结束。这会影响事务处理，如下所示。

  1. 使用默认的“REPEATABLE READ”隔离级别时：
    第一个“UPDATE”会在其读取的每一行上获得一个 x 锁，并且不会释放其中的任何一个：【！因为没有使用索引，所以导致全表扫面，而又因为 RR 使用“临键锁”，所以每一条记录的索引都被使用“临键锁”，相当于“全表锁定”】
    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
  2. 如果改为使用“READ COMMITTED”：
    则第一个“UPDATE”会在其读取的每一行上获取一个 x 锁，并释放其未修改的行的 x 锁：【！因为没有使用索引，所以导致全表扫面，而又 RC 不使用“临键锁”（间隙锁），所以只会锁定有对应值的记录】
    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


示例 2：【？】
但是，如果“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;

在上面的示例中，第一个“UPDATE”在 b = 2 的每一行上获取并保留一个 x 锁。第二个“UPDATE”尝试获取同一记录上的 x 锁时将阻塞，因为它也使用在 b 列上定义的索引。


使用“READ COMMITTED”隔离级别的效果与启用已弃用的“innodb_locks_unsafe_for_binlog”配置选项相同，但以下情况除外：

  • 启用“innodb_locks_unsafe_for_binlog”是全局设置，会影响所有会话，而隔离级别可以针对所有会话全局设置，也可以针对每个会话单独设置。
  • “innodb_locks_unsafe_for_binlog”只能在服务器启动时设置，而隔离级别可以在启动时设置或在运行时更改。

因此“READ COMMITTED”比“innodb_locks_unsafe_for_binlog”提供了更好，更灵活的控制。

“READ UNCOMMITTED”

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

“SERIALIZABLE”

此级别类似于“REPEATABLE READ”，但是

  1. InnoDB 如果禁用了autocommit，则将所有普通 SELECT 语句隐式转换为“SELECT ... LOCK IN SHARE MODE”。
  2. 如果启用了autocommit，则 SELECT 是其自身的事务。

因此，它被认为是只读的，并且如果以一致的（非锁定）读取方式执行并且不需要阻塞其他事务就可以序列化。

  • （如果其他事务已修改所选行，则要强制普通 SELECT 阻止，请禁用autocommit。）

自动提交，提交和回滚

在 InnoDB 中，所有用户活动都发生在事务内部。如果启用了 autocommit 模式，则每个 SQL 语句将自己形成一个事务。默认情况下，MySQL 为启用了 autocommit 的每个新连接启动会话，因此，如果每个 SQL 语句未返回错误，则 MySQL 都会在该 SQL 语句之后进行提交。如果一条语句返回错误，则提交或回滚行为取决于该错误。


  1. 启用了 autocommit 的会话可以通过以显式“START TRANSACTION”或“BEGIN”语句开始，并以“COMMIT”或“ROLLBACK”语句结束来执行多语句事务。【！】
  2. 如果在具有“SET autocommit = 0”的会话中禁用了 autocommit 模式，则该会话始终具有打开的事务。“COMMIT”或“ROLLBACK”语句结束当前事务，然后开始新的事务。【！】
    • 如果禁用了 autocommit 的会话在没有显式提交最终事务的情况下结束，则 MySQL 将回滚该事务。【！】
  3. 某些语句隐式结束事务，就像您在执行该语句之前已经完成“COMMIT”一样。


“COMMIT”表示在当前事务中所做的更改将成为永久性的，并在其他会话中可见。另一方面，“ROLLBACK”语句会取消当前事务所做的所有修改。“COMMIT”和“ROLLBACK”都释放在当前事务期间设置的所有 InnoDB 锁。


将 DML 操作与事务分组

默认情况下，与 MySQL 服务器的连接从启用 autocommit 模式开始，此模式会在您执行时自动提交每个 SQL 语句。如果您有其他数据库系统的经验，则可能不熟悉这种操作模式，在标准实践中，发出一系列 DML 语句并将其提交或一起回滚。


要使用多语句事务：

  • 请使用 SQL 语句“SET autocommit = 0”关闭自动提交，并在适当时以“COMMIT”或“ROLLBACK”结束每个事务。
  • 要保留自动提交功能，请以“START TRANSACTION”开始每个事务，并以“COMMIT”或“ROLLBACK”结束它。


示例：显示了两个事务。第一个被提交；第二个被回滚。 

shell> mysql test

mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do a transaction with autocommit turned on.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do another transaction with autocommit turned off.
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- Now we undo those last 2 inserts and the delete.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

Client 端语言的事务

在诸如 PHP，Perl DBI，JDBC，ODBC 或 MySQL 的标准 C 调用接口之类的 API 中，您可以像其他任何 SQL 语句（如“SELECT”或“INSERT”）一样，将事务控制语句（如“COMMIT”）作为字符串发送到 MySQL 服务器。

  • 一些 API 还提供了单独的特殊事务提交和回滚功能或方法。

一致的非锁定读取

consistent read（一致性读取）表示 InnoDB 使用多版本控制在某个时间点向查询呈现数据库快照：

该查询将看到在该时间点之前提交的事务所做的更改，而看不到以后或未提交的事务所做的更改。【该规则的例外是查询可以看到同一事务中较早的语句所做的更改。】

此异常导致以下异常：如果更新表中的某些行，则“SELECT”将看到已更新行的最新版本，但也可能会看到任何行的旧版本。如果其他会话同时更新同一张表，则异常意味着您可能会以数据库中不存在的状态查看该表。


隔离级别与一致性读取：

  1. 如果事务隔离级别为“REPEATABLE READ”（默认级别），则同一事务中的所有一致读取将读取由该事务中的第一个此类读取构建的快照。
    • 您可以通过提交当前事务并在此之后发出新查询来获取查询的更新快照。
  2. 使用“READ COMMITTED”隔离级别，则事务中的每个一致读取都会设置并读取其自己的新快照。

一致读取是 InnoDB 处理“READ COMMITTED”和“REPEATABLE READ”隔离级别的“SELECT”语句的默认模式。一致读取不会在它访问的表上设置任何锁，因此其他会话可以自由地在对表执行一致读取的同时修改这些表。

假设您以默认的“REPEATABLE READ”隔离级别运行。当您发出一致的读取（即普通的“SELECT”语句）时，InnoDB 为您的事务提供一个时间点，根据该时间点您的查询可以看到数据库。如果另一个事务删除了一行并在分配了时间点后提交，则看不到该行已被删除。插入和更新的处理方式相似。


NOTE：
数据库状态的快照适用于事务中的“SELECT”条语句，而不必适用于 DML 语句。如果您插入或修改某些行，然后提交该事务，则从另一个并发“REPEATABLE READ”事务发出的“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”，即使用“START TRANSACTION”等语句开始新事务）来推进时间。这称为多版本并发控制。【？？？】


示例：
会话 A 仅在“ B 提交了插入，并且 A 也提交了”时，才看到 B 插入的行，因此时间点比 B 的提交提前了。 

           Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                           INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                COMMIT;

          SELECT * FROM t;
           empty set

           COMMIT;

          SELECT * FROM t;
          ---------------------
          |    1    |    2    |
          ---------------------


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

SELECT * FROM t FOR SHARE;
  1. 使用“READ COMMITTED”隔离级别，事务中的每个一致读取都会设置并读取其自己的新快照。
  2. 使用“LOCK IN SHARE MODE”时，将发生锁定读取：“SELECT”阻塞，直到包含最新行的事务结束。


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

  • 一致读取无法在“DROP TABLE”上进行，因为 MySQL 无法使用已删除的表，而 InnoDB 会破坏该表。
  • 一致读取在“ALTER TABLE”上不起作用，因为该语句将创建原始表的临时副本，并在构建该临时副本时删除该原始表。当您在事务内重新发出一致的读取时，新表中的行不可见，因为在获取事务快照时这些行不存在。在这种情况下，事务返回错误：“ER_TABLE_DEF_CHANGED”（“table 定义已更改，请重试事务”）。


对于未指定“FOR UPDATE”或“LOCK IN SHARE MODE”的“INSERT INTO ... SELECT”、“UPDATE ... (SELECT)”和“CREATE TABLE ... SELECT”之类的“select”子句，读取的类型有所不同：

  • 默认情况下，InnoDB 使用更强的锁定，而 SELECT 部分的作用类似于“READ COMMITTED”，即使在同一事务中，每个一致的读取也会设置并读取自己的新快照。
  • 要在这种情况下使用一致的读取，请启用“innodb_locks_unsafe_for_binlog”选项，并将事务的隔离级别设置为“READ UNCOMMITTED”，“READ COMMITTED”或“REPEATABLE READ”（即“SERIALIZABLE”以外的任何值）。在这种情况下，不会对从所选表读取的行设置锁定。

锁定读取

如果查询数据，然后在同一事务中插入或更新相关数据，则常规“SELECT”语句不能提供足够的保护。其他事务可以更新或删除刚查询的相同行。

InnoDB 支持两种提供额外安全性的locking reads（锁定读取）：

  1. “SELECT ... LOCK IN SHARE MODE”【！！！】
    在读取的任何行上设置共享模式锁定。其他会话可以读取行，但是在事务提交之前不能修改它们。如果这些行中的任何一个被尚未提交的另一个事务更改，则查询将 await 直到该事务结束，然后使用最新值。
  2. “SELECT ... FOR UPDATE”【！！！这个我常用】
    对于索引记录，搜索遇到的情况，锁定行和任何关联的索引条目，就像您为这些行发出“UPDATE”语句一样。


禁止其他事务更新这些行，执行“SELECT ... LOCK IN SHARE MODE”或读取某些事务隔离级别的数据。一致的读取将忽略读取视图中存在的记录上设置的任何锁定。（记录的旧版本无法锁定；可以通过在记录的内存副本上应用undo logs来重构它们。）【？】


NOTE：

  • 提交或回滚事务时，将释放由“LOCK IN SHARE MODE”和“FOR UPDATE”查询设置的所有锁。
  • 只有在禁用自动提交时（通过以“START TRANSACTION”开始事务或将“autocommit”设置为 0），才可以进行锁定读取。
  • 除非在子查询中也指定了锁定读取子句，否则外部语句中的锁定读取子句不会锁定嵌套子查询中表的行。
    例如，以下语句不会锁定表 t2 中的行：
    SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;
    要锁定表 t2 中的行，请向子查询添加一个锁定 read 子句：
    SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

锁定读：“行锁”与“表锁”

InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。【！！！】

  • 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高（比如对一些很小的表）就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
    因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。


锁定读的所情况总结：

  1. “无锁”：根据“主键”、“非主键索引”、“主键 + 非主键索引”、“主键 + 非索引”进行查询，但未查询到数据时。
  2. “行锁”：根据“主键”、“非主键索引”、“主键 + 非主键索引”进行查询，并查询到数据时。
  3. “表锁”：
    1. 只根据“非索引”字段进行查询时（无论是否查询到数据）；【未使用索引】
    2. 只根据“主键”进行查询，条件为“<>”、“like”时（无论是否查询到数据）；【结果集为范围】


  • 根据“主键 + 非索引”进行查询，并且查询到数据时：【？？？】
    1. 如果其他线程按“主键”字段进行查询，则“主键”字段产生行锁；
    2. 如果其他线程按“非主键索引”字段进行查询，则“非主键索引”字段产生行锁；
    3. 如果其他线程按“非索引”字段进行查询，则“非索引”字段产生表锁；
    4. 如果索引值是枚举类型，mysql 也会进行表锁。


  • InnoDB 中，使用“非主键索引”（辅助索引）一定会用到“主键索引”（聚簇索引）【“回表”】

读锁示例

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


首先，使用一致的读取来查询表 parent 并验证父行是否存在。您可以安全地将子行插入表 child 吗？

不可以，因为某些其他会话可能会在“SELECT”和“INSERT”之间的瞬间删除父行，而不会引起您注意。

为避免此潜在问题，请使用“LOCK IN SHARE MODE”执行“SELECT”： 

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

“LOCK IN SHARE MODE”查询返回父级 'Jones' 后，您可以安全地将子记录添加到 CHILD 表中并提交事务。任何尝试获取 PARENT 表中适用行中的排他锁的事务都将等到您完成操作（即所有表中的数据处于一致状态）后再进行。


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

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

要实现读取和递增计数器，请首先使用“FOR UPDATE”对计数器执行锁定读取，然后递增计数器。例如： 

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

“SELECT ... FOR UPDATE”读取最新的可用数据，并在读取的每一行上设置排他锁。因此，它设置了与“对搜索到的 SQL 进行更新”所将在行上设置的相同的锁。


前面的描述仅是“SELECT ... FOR UPDATE”工作方式的示例。在 MySQL 中，生成唯一标识符的特定任务实际上可以仅通过单次访问表来完成： 

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

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

取自“http://wiki.eijux.com/index.php?title=InnoDB：InnoDB_事务模型&oldid=6668”