InnoDB锁和事务模型 —— 事务模型

在InnoDB事务模型中，目标是将多版本（multi-versioning）数据库的最佳属性与传统的两阶段锁定相结合。InnoDB执行行级别锁定，默认情况下将查询作为非锁定一致性读取运行。

下面是几个经常遇到概念的解释。

MVCC

多版本并发控制(multiversion concurrency control)的缩写。这种技术使具有特定隔离级别的InnoDB事务执行一致的读操作; 也就是说，查询其他事务正在更新的行，并看到这些更新发生之前的值。这是一种增强并发性的强大技术，允许查询继续进行而无需等待其他事务持有的锁。

这种技术在数据库世界中并不普遍。其他一些数据库产品和其他一些MySQL存储引擎不支持它。

一致性读取(consistent read)

一种读取操作，它使用快照信息基于某个时间点显示查询结果，而不管同时运行的其他事务所执行的更改。如果查询的数据已被另一个事务更改，则基于undo log的内容重建原始数据。这种技术避免了一些锁定问题，这些问题通过强制事务等待其他事务完成来减少并发性。

事务隔离级别

事务隔离是数据库处理的基础之一。隔离是缩略词ACID中的I; 隔离级别是在多个事务进行更改并同时执行查询时，对结果的性能和可靠性，一致性和可重现性之间的平衡进行微调的设置。

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

下面是sql标准中每个隔离级别存在的问题，可以让我们首先了解每个隔离级别的优劣势：

首先我们先介绍几个概念：

• 脏读(Dirty Read) —— 事务T1对数据就行了修改，但是修改后事务尚未提交。而另一个事务T2会读取到这个修改并使用该数据。
• 不可重复读(NonRepeatable Read) —— 事务T1内多次读取同一数据期间，另一个事务T2修改了这一数据，造成事务T1多次读取到到的结果不一致。
• 幻读(Phantom Read) —— 事务T1涉及了对全表的数据进行修改，T2插入了新的一行。虽然T1查询是无法感知新的行，但是在此执行全表数据修改，发现还会有新的修改，就好像产生幻觉。

Isolation Level	Dirty Read	NonRepeatable Read	Phantom Read
Read Uncommitted	Y	Y	Y
Read Committed	N	Y	Y
Repeatable Read	N	N	Y
Serializable	N	N	N

• REPEATABLE READ

  同一事务中的一致性读会读取第一次读是建立的快照。这意味着如果在同一事务中发出几个普通（非锁定）SELECT语句，这些SELECT语句也相互一致。

  对于locking read(SELECT … FOR UPDATE或者LOCK IN SHARE MODE)，UPDATE和DELETE语句，锁取决于语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。

  • 对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不是之前的间隙。
  • 对于其他搜索条件，InnoDB锁定扫描的索引范围，使用间隙锁或Next-Key锁来阻止其他会话插入范围所覆盖的间隙。

• READ COMMITTED

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

  对于locking read(SELECT … FOR UPDATE或者LOCK IN SHARE MODE)，UPDATE和DELETE语句，InnoDB仅锁定索引记录，而不锁定它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。因为禁用了间隙锁定，可能会出现幻像问题，因为其他会话可以在间隙中插入新行。

  如果使用READ COMMITTED，则必须使用row-based二进制日志记录。

  使用READ COMMITTED会产生额外的影响：

  • 对于UPDATE或DELETE语句，InnoDB仅为其更新或删除的行保留锁定。MySQL评估WHERE条件后，将释放没有匹配行的记录锁。这大大降低了死锁的可能性，但它们仍然可以发生。
  • 对于UPDATE语句，如果一行已被锁定，InnoDB执行“半一致”读取，将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。如果行匹配（必须更新），MySQL再次读取该行，这次InnoDB将其锁定或等待锁。

• READ UNCOMMITTED

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

• SERIALIZABLE

  这个级别就像REPEATABLE READ，但是如果禁用自动提交，InnoDB会隐式地将所有普通的SELECT语句转换为SELECT … LOCK IN SHARE MODE。如果启用了自动提交，则SELECT是其自己的事务。因此，已知它是只读的，并且如果作为一致（非锁定）读取执行则可以序列化，并且不需要阻止其他事务。要强制普通SELECT阻止其他事务已修改所选行，请禁用自动提交。

一致的非锁定读取(Consistent Nonlocking Reads)

一致读取意味着InnoDB使用多版本控制在某个时间点向查询提供数据库的快照。查询会看到在那个时间点之前提交的事务所做的更改，而不会看到以后或未提交的事务所做的更改。此规则的例外是查询可以看到同一事务中交早语句所做的更改。此异常导致以下异常：如果更新表中的某些行，SELECT将查看更新行的最新版本，但它也可能会看到任何行的旧版本。如果其他会话同时更新同一表，则该异常意味着您可能看到该表处于数据库中从未存在的状态。

如果事务的隔离级别是REPEATABLE READ，同一事务中的所有一致读由该事务中第一次读取所建立的快照。您可以通过提交当前事务以及在发出新查询之后为查询获取更新的快照。

使用READ COMMITTED隔离级别，事务中的每次一致性读都会设置，读取自己最新的快照。一致性读取是InnoDB在READ COMMITTED和REPEATABLE READ隔离级别中处理SELECT语句的默认模式。一致读取不会对其访问的表设置任何锁定，因此其他会话可以在对表执行一致读取的同时自由修改这些表。

假设您在默认的可重复读隔离级别上运行。当您发出一致读取（即普通的SELECT语句）时，InnoDB会为您的事务提供一个时间点，您的查询将根据该时间点查看数据库。如果另一个事务删除了一行并在分配了时间点后提交，不会看到被删除的行。插入和修改是一样的。

注意

如果插入或修改某些行然后提交该事务，则从另一个并发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来提前您的时间点。

这称为多版本并发控制。

在以下示例中，会话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隔离级别或锁定读取：

SELECT * FROM t FOR SHARE;

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

一致的读取对某些DDL语句不起作用：

• 一致读取不适用于DROP TABLE，因为MySQL无法使用已删除的表并且InnoDB会破坏该表。
• 一致性读取不适用于ALTER TABLE，因为该语句生成原始表的临时副本，并在构建临时副本时删除原始表。在事务中重新发出一致读取时，新表中的行不可见，因为在获取事务的快照时这些行不存在。在这种情况下，事务返回错误：ER_TABLE_DEF_CHANGED，“表定义已更改，请重试事务”。

读取类型因INSERT INTO … SELECT，UPDATE …（SELECT）和CREATE TABLE … SELECT不指定FOR UPDATE或LOCK IN SHARE MODE等子句中的选择而异：

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

锁定读取

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

• SELECT … LOCK IN SHARE MODE

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

• SELECT … FOR UPDATE

  对于搜索遇到的索引记录，锁定行和任何关联的索引条目，就像为这些行发出UPDATE语句一样。阻止其他事务更新这些行，从进行SELECT … LOCK IN SHARE MODE，或从某些事务隔离级别读取数据。一致性读取将忽略在读取视图中存在的记录上设置的任何锁定。旧版本的记录无法锁定; 它们通过在记录的内存中副本上应用undo log来重建。

锁定读取例子

假设你要在表child中插入新行，并确保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表中会出现duplicate-key错误。

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

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

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