InnoDB 中文参考手册 --- 8 InnoDB 事务模式与锁定

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}
 

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

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

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

InnoDB 中的隔离级详细描述：

• READ UNCOMMITTED 这通常称为 ''''dirty read''''：non-locking SELECTs 的执行使我们不会看到一个记录的可能更早的版本；因而在这个隔离度下是非 ''''consistent'''' reads；另外，这级隔离的运作如同 READ COMMITTED。
• READ COMMITTED 有些类似 Oracle 的隔离级。所有 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 语句只锁定索引记录，而不锁定之前的间隙，因而允许在锁定的记录后自由地插入新记录。以一个唯一地搜索条件使用一个唯一索引(unique index)的 UPDATE 和 DELETE，仅仅只锁定所找到的索引记录，而不锁定该索引之前的间隙。但是在范围型的 UPDATE and DELETE 中，InnoDB 必须设置 next-key 或 gap locks 来阻塞其它用户对范围内的空隙插入。 自从为了 MySQL 进行复制(replication)与恢复(recovery)工作''''phantom rows''''必须被阻塞以来，这就是必须的了。Consistent reads 运作方式与 Oracle 有点类似： 每一个 consistent read，甚至是同一个事务中的，均设置并作用它自己的最新快照。
• REPEATABLE READ 这是 InnoDB 默认的事务隔离级。. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, 和 DELETE ，这些以唯一条件搜索唯一索引的，只锁定所找到的索引记录，而不锁定该索引之前的间隙。 否则这些操作将使用 next-key 锁定，以 next-key 和 gap locks 锁定找到的索引范围，并阻塞其它用户的新建插入。在 consistent reads 中，与前一个隔离级相比这是一个重要的差别： 在这一级中，同一事务中所有的 consistent reads 均读取第一次读取时已确定的快照。这个约定就意味着如果在同一事务中发出几个无格式(plain)的 SELECTs ，这些 SELECTs 的相互关系是一致的。
• SERIALIZABLE 这一级与上一级相似，只是无格式(plain)的 SELECTs 被隐含地转换为 SELECT ... LOCK IN SHARE MODE。

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 语句的锁定设置

• SELECT ... FROM ... : 这是一个 consistent read，不以锁定方式读取数据库的快照，除非事务的隔离级被设置为

  [1] [2]  下一页

  
  [办公软件]用Ctrl+Shift+8快速选择Excel连续的块状数据  [聊天工具]Windows Live Messenger 8 Beta1高清图赏__天极Ye…
  [聊天工具]MSN Messenger 8 Beta简单试用报告__天极Yesky  [聊天工具]Windows Live Messenger 8 截图欣赏__天极Yesky
  [聊天工具]MSN Messenger8 超酷头像任你选__天极Yesky  [常用软件]painter 8 菜单中英文对照
  [VB.NET程序]VB.NET实现DirectSound9 (8) 音效控制器  [VB.NET程序]几个 WMI 的例子（初级） - 1
  [VB.NET程序]几个 WMI 的例子（初级） - 2  [VB.NET程序]几个 WMI 的例子（初级） - 3
  

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18