事务隔离等级提交读与可重复读的区别

翻译自Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels

作为Percona的讲师，我有时会被问及已提交读和可重复读事务隔离级别之间的区别，它们存在一些差异，但都与锁定有关。

额外锁定 (非间隙锁定)

请记住，InnoDB实际上锁定了索引条目（index entries），而不是行（rows），这很重要。在执行语句期间，InnoDB必须锁定它遍历的索引中的每个条目，以查找它正在修改的行。他必须这样做以防止死锁并保持隔离级别。

若执行一句未被良好索引的UPDATE语句，则会锁定很多行：

update employees set store_id = 0 where store_id = 1;
---TRANSACTION 1EAB04, ACTIVE 7 sec
-- 633 lock struct(s), <strong>heap size 96696</strong>, 218786 row lock(s), undo log entries 1
-- MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 47 localhost root
-- show engine innodb status

在employee表中，列store_id没有被索引。请注意，UPDATE已经完成运行（我们现在正在运行SHOW ENGINE …）但是我们持有218786个行锁并且只有一个撤销条目（undo entry）。这意味着只有一行被更改，但我们仍然持有额外的锁。堆大小表示已为锁分配的内存量。

在可重复读中，在事务期间保持的每个锁都在事务期间保持。（In REPEATABLE READ every lock acquired during a transaction is held for the duration of the transaction.）
在已提交读中，与扫描不匹配的锁会在语句完成后被释放。

下面是在已提交读下的UPDATE语句执行：

---TRANSACTION 1EAB06, ACTIVE 11 sec
-- 631 lock struct(s), <strong>heap size 96696</strong>, 1 row lock(s), undo log entries 1
-- MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 62 localhost root
-- show engine innodb status

您会注意到堆大小是相同的，但现在只持有一个锁。在所有事物隔离级别中，InnoDB会对扫描的每个索引锁定。级别之间的差异是，一旦语句在已提交读模式下完成，就会针对扫描不匹配的条目释放锁。请注意，InnoDB在释放锁之后不会立即释放堆内存，因此堆大小与可重复读的堆大小相同，但保持的锁数量较少（仅仅一个）。

这意味着在已提交读中，一旦UPDATE语句完成，其他事务可以自由更新他们在可重复读中无法更新的行。

一致读视图

可重复读中，在事务开始时创建了“读取视图（read view）”（trx_no没有看到trx_id >= ABC，看到< ABB），并且此读取视图（Oracle术语中的consistent snapshot）在事务的持续时间内保持打开状态。同一个事务中，凌晨5点执行SELECT语句，与在下午5点执行的结果集相同。这称为MVCC（多版本并发控制），它使用行版本控制（row versioning）和撤销信息（undo information）来完成。

在可重复读中，InnoDB还未范围扫描创建间隙锁（gap locking）：

select * from some_table where id > 100 FOR UPDATE；

上面这条会创建一个间隙锁，以阻止任何id > 100的行插入，直至事务回滚或提交。

在同一事务中，若SELECT ... FOR UPDATE在凌晨5点运行，UPDATE在下午5点运行（UPDATE some_table，其中id > 100）则UPDATE将改变SELECT FOR UPDATE在凌晨5点锁定的相同行。不可能更改其他行，因为在100之后的间隙先前已被锁定。

不可重复读（已提交读）

已提交读中，在每个语句的开头创建一个读取视图。这意味着在同一个事务中，凌晨5点与下午5点执行的相同的SELECT语句结果集有可能是不同的。这是因为在已提交读中，事务的读取视图只会在语句执行期间持续，因此，连续执行的相同的语句可能会显示不同结果。

这称为幻行（phantom row）问题。

此外，已提交读中，永远不会创建间隙锁。由于没有间隙锁定，上面的实例SELECT … FOR UPDATE不会读之其他事务将新行插入。因此，使用SELECT … FOR UPDATE锁定行（即"where id > 100"）并随后使用"where id > 100"（即使在统一事务中）更新行可能会导致更新的行比先前锁定的行更多。这是因为在语句之间的表中可能插入了新行，因为没有为SELECT … FOR UPDATE创建间隙锁。