InnoDB：InnoDB 事务模型

来自Wikioe
Eijux（讨论 | 贡献）2021年4月20日 (二) 18:29的版本 （→‎事务隔离级别）
跳到导航 跳到搜索


关于

在 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 锁，并且不会释放其中的任何一个：【！】
    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 锁：【！】
    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。）

自动提交，提交和回滚

一致的非锁定读取

锁定读取

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