8 InnoDB 事务模式与锁定

在 InnoDB 事务处理模式中， the goal has been to combine the best properties of a multiversioning database to traditional two-phase locking. InnoDB 进行行级的锁定，并以与 Oracle 非锁定读取(non-locking)类似的方式读取数据。 InnoDB 中的锁定表的存储是如此(space-efficiently)而不再需要扩大锁定： 典型特色是一些用户可能锁定数据库中的任意行或任意行的子集，而不会引起 InnoDB 内存运行溢出。

在 InnoDB 中，所有的用户操作均是以事务方式处理的。如果 MySQL 使用了自动提交(autocommit)方式，每个 SQL 语句将以一个单独的事务来处理。MySQL 通常是以自动提交方式建立一个服务连接的。

如果使用 SET AUTOCOMMIT = 0 关闭自动提交模式，就认为用户总是以事务方式操作。如果发出一个 COMMIT 或 ROLLBACK 的 SQL 语句，它将停止当前的事务而重新开始新事务。两个语句将会释放所有在当前事务中设置的 InnoDB 锁定。COMMIT 意味着永久改变在当前事务中的更改并为其它用户可见。ROLLBACK 正好相反，它是取消当前事务的所有更改。

如果以 AUTOCOMMIT = 1 建立一个连接，那么用户仍然可以通过以 BEGIN 开始和 COMMIT 或 ROLLBACK 为语句结束的方式来执行一个多语句的事务处理。

在 SQL-1992 事务隔离级(transaction isolation levels)规定的条款中，InnoDB 默认为 REPEATABLE READ。从 4.0.5 开始， InnoDB 提供了 SQL-1992 标准中所有的 4 个不同的事务隔离级。你可以 my.cnf 的 [mysqld] 区中设置所有连接的默认事务隔离级： 

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                           | REPEATABLE-READ | SERIALIZABLE}

　

用户也可以通过下面的 SQL 语句为单个连接或所有新建的连接改变隔离级： 

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                           | REPEATABLE READ | SERIALIZABLE}
注意在这个 SQL 语句的语法中没有连字符。如果你在上述语句中详细指定关键字 GLOBAL ，它将决定新建连接的初始隔离级，但不会改变已有连接的隔离级。任何用户均可以更改自身会话的隔离级，即使是在一个事务处理过程中。在 3.23.50 以前的版本中 SET TRANSACTION 对 InnoDB 表无任何效果。在 4.0.5 以前的版本中只有 REPEATABLE READ 和SERIALIZABLE 可用。

　

可以通过下列语句查询全局和当前会话的事务隔离级： 

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

　

在 InnoDB 的行锁中使用所谓的 next-key locking。这就意味着，除了索引记录外，InnoDB 还可以锁定该索引记录前部“间隙” ('gap') 以阻塞其它用户在索引记录前部的直接插入。next-key lock 意思是锁定一个索引记录以及该记录之前的间隙(gap)。gap lock 就是只锁定某些索引记录之前的间隙。

InnoDB 中的隔离级详细描述：

　

8.1 Consistent read

Consistent read 就是 InnoDB 使用它的多版本(multiversioning)方式提供给查询一个数据库在一个时间点的快照。 查询将会检查那些在这个时间点之前提交的事务所做的改动，以及在时间点之后改变或未提交的事务? 与这个规则相例外的是查询将检查查询自身发出的事务所做的改变。

如果以默认的 REPEATABLE READ 隔离级，那么所有在同一事务中的 consistent reads 只读取同一个在事务中第一次读所确定的快照。 你可以通过提交当前事务并发出一个新的查询以获得新的数据快照。

Consistent read 在 InnoDB 处理 SELECT 中的默认模式是 READ COMMITTED 和 REPEATABLE READ 隔离级。Consistent read 对其所访问的表不加任何锁定，因而其它任何用户均可以修改在 consistent read 被完成之前自由的修改这些表。

8.2 Locking reads

Consistent read 在某些情况下是不太方便的。 假设你希望在表 CHILD 中插入 一个新行，而这个子表已有一个父表 PARENT。

假设你使用 consistent read 了读取表 PARENT 并查看子表中对应记录。你真的能安全地在表 CHILD 中加入一个子行？不可能，因为在此期间可能有其它用户删除了表 PARENT 中的父行，而你并不知道它。

解决的办法就是在锁定的方式 LOCK IN SHARE MODE 下运行一个 SELECT 。

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

在共享模式下执行读取的意思就是读取最新的现有资料，并在所读取的行上设置一个共享模式的锁定。如果最新的数据属于其它用户仍未提交的事务，那将不得不等到这个事务被 提交 。共享模式的可以防止其它用户更新或删除我们当前所读取的行。当查询获得 'Jones'后，就可以安全地向子表 CHILD 中加入子行，然后提交事务。 这个例子显示如何在应用程序代码中实现参照完整性。

另外一个例子： 在表 CHILD_CODES 有一个整型计数字段用于给在表 CHILD 中加入的每个子行赋于一个唯一的标识符。 显而易见地，用一个 consistent read 来读取父表中的值并不是一个好的主意，因两个用户有可能会读取出同一个计数值，当以同一个标识符插入两个字行时将会产生一个重复键值(duplicate key)的错误。如果两个用户同时读取了计数器，当尝试更新计数器时，他们中的一个必将在死锁中结束，所以在读取时使用 LOCK IN SHARE MODE 也并不是一个好的解决办法。

在这和情况下有两种方法来实现读取并增加计数器：(1) 首先更新计数器然后再读取它；(2) 首先以一个 FOR UPDATE 方式锁定后再读取，然后再增加它：

SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

SELECT ... FOR UPDATE 将读取最新的现有数据，并在所读取的行上设置排它的锁定。同样在 SQL UPDATE 所访问的行上也设置此锁定。

8.3 Next-key locking: avoiding the 'phantom problem'

在 InnoDB 的行级锁定上使用一个称作 next-key locking 算法。在 InnoDB 在搜索或扫描表的索引时将进行行锁，它将在所访问到的索引上设置共享或排它的锁定。因而行锁是更加精确地而又称为索引记录锁定。

InnoDB 在索引记录上设置的锁同样会影响索引记录之前的“间隙(gap)”。如果一个用户对索引记录 R 加了一个共享或排它的锁定，那其它用户将不能在 R 之前立即插入新的记录。这种间隙锁定用于防止所谓的“phantom problem”。假设需读取和锁定表 CHILD 中标识符大于 100 的子行，并更新所搜索到的记录中某些字段。

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

假设表 CHILD 中有一个索引字段 ID。我们的查询将从 ID 大于100的第一条记录开始扫描索引记录。 现在，假设加在索引记录上的锁定不能阻止在间隙处的插入，一个新的子记录将可能在事务处理中被插入到表中。 如果现在在事务中再次执行

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

在查询返回的记录集中将会有一个新的子记录。这与事务的隔离规则相违背的：一个事务必须能够顺串(run)， 因而在事务处理中所读取的数据将不会发生改变。而新的 'phantom' 子记录将会打破这个隔离规则。

当 InnoDB 扫描索引时，它同样会锁定在索引中在结尾记录(the last record)之后的间隙。这仅仅在上例中会发生： InnoDB 设置的锁定将阻止任何 ID 大于 100 的插入。

在应用程序中可以通过一个 next-key locking 来实现一个唯一性(uniqueness)检查：如果以一个共享模式读取数据并没有发现与将要插入的数据存在重复值， 那么在读取过程中 next-key lock 将被设置在你的记录的后继者(successor)上，这将阻止其它用户在期间插入相同的记录，因而你可以安全地插入你的记录。 所以， next-key locking 可以允许你 'lock' 你的表中并不存在的记录。

8.4 InnoDB 中各 SQL 语句的锁定设置

8.5 MySQL 什么时候隐含地提交(commit)或回滚(rollback)事务？

8.6 死锁检测与回滚

InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。从 4.0.5 版开始，InnoDB 将设法提取小的事务来进行回滚。一个事务的大小由它所插入(insert)、更新(update)和删除(delete)的数据行数决定。 Previous to 4.0.5, InnoDB always rolled back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the waits-for graph of transactions.

InnoDB 不能检测出由 MySQL 的 LOCK TABLES 语句引起的死锁，或其它的表类型中的锁定所引起的死锁。你不得不通过在 my.cnf 中设置 innodb_lock_wait_timeout 参数来解决这些情形。

当 InnoDB 执行一个事务完整的回滚，这个事务所有所加的锁将被释放。然而，如果只一句的 SQL 语句因结果返回错误而进行回滚的，由这条 SQL 语句所设置的锁定可能会被保持。这是因为 InnoDB r的行锁存储格式无法知道锁定是由哪个 SQL 语句所设置。

8.7 consistent read 在 InnoDB 运作示例

假设你以默认的 REPEATABLE READ 事务隔离级水平运行。当你发出一个 consistent read 时，即一个普通的 SELECT 语句，InnoDB 将依照你的查询检查数据库给你的事务一个时间点(timepoint)。因而，如果事务 B 在给你指定的时间点后删除了一行并提交，那么你并不能知道这一行已被删除。插入(insert)与更新(update)也是一致的。

你可以通过提交你的事务并重新发出一个 SELECT 来将你的时间点提前。

这就叫做 multiversioned 并发控制。 



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

 ---------------------
 |    1     |    2     | 
 ---------------------
  
因而，只有当用户 B 提交了他的插入，并且用户 A 也提交了他的事务从而使时间点越过 B 提交时的时间点之后，用户 A 才能看到用户 B 所插入的新行。

如果你希望查看数据库“最新的(freshest)”状态，你必须使用 READ COMMITTED 事务隔离级，或者你可以使用读锁： 

SELECT * FROM t LOCK IN SHARE MODE;

　

8.8 如何应付死锁？

死锁是事务处理型数据库系统的一个经典问题，但是它们并不是很危险的， 除非它们如此地频繁以至于你根本处理不了几个事务。 当因死锁而产生了回滚时，你通常可以在你的应用程序中重新发出一个事务即可。

InnoDB 使用自动地行级锁定。你可能恰好在插入或删除单一一条记录时产生死锁。 这是因为这些操作并不是真正“原子(atomic)”级的：他们会自动地在锁定 inserted/deleted 行的索引记录(可能有几个)。

可以通过下面所示的技巧来应付死锁或减少死锁的次数：