Mysql 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 中的隔离级详细描述：

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，不以锁定方式读取数据库的快照，除非事务的隔离级被设置为 SERIALIZABLE，在这种情况下将在它所读取的记录索引上设置共享的 next-key locks。
SELECT ... FROM ... LOCK IN SHARE MODE : 在所读取的所有记录索引上设置同享的锁定。
SELECT ... FROM ... FOR UPDATE : 在所读取的所胡记录索引上设置独占地(exclusive)锁定。
INSERT INTO ... VALUES (...) : 在插入的记录行上设置一个独占地锁定；注意这个锁定并不是一个 next-key lock ，并不会阻止其它用户在所插入行之前的间隙(gap)中插入新记录。如果产生一个重复键值错误，在重复索引记录上设置一个共享的锁定。
如果在一个表中定义了一个 AUTO_INCREMENT 列，InnoDB 在初始化自增计数器时将在与自增列最后一个记录相对应的索引上设置一个独占的锁定。在访问自增计数器时， InnoDB 将设置一个特殊的表锁定模式 AUTO-INC ，这个锁定只持续到该 SQL 语句的结束而不是整个事务的结束。
INSERT INTO T SELECT ... FROM S WHERE ... 在已插入到表 T 中的每个记录上设置一个独占的(无 next-key)锁定。以一个 consistent read 搜索表 S ，但是如果 MySQL 打开了日志开关将在表 S 上设置一个共享的锁定。在从备份中进行前滚(roll-forward)修复时，每个 SQL 语句必须严格按照原先所执行的顺序运行，所以 InnoDB 不得不设置锁定。
CREATE TABLE ... SELECT ... 与上项相似，以 consistent read 或锁定方式完成 SELECT 。
REPLACE 如果没有一个 unique key 冲突，它的执行与 insert 一致。否则将在它所要更新的记录上设置一个独占的锁定。
UPDATE ... SET ... WHERE ... : 在搜索时所遭遇到的记录上设置一个独占的锁定。
DELETE FROM ... WHERE ... : 在搜索时所遭遇到的每一个记录上设置一个独占的锁定。
如果一个表上有 FOREIGN KEY 约束，所有需要检查约束条件的 insert, update, 或 delete 将在它所要检查约束的记录上设置记录共享级的锁定。同样在约束失败时，InnoDB 也设置这个锁定。
LOCK TABLES ... : 设置表锁定。在 MySQL 的代码层(layer of code)设置这些锁定。InnoDB 的自动死锁检测无法检测出有关下列情形的表锁定：查看下面的一个章节。同时查看第 14 章节 'InnoDB 限制与不足' 有关下列内容：自从 MySQL 提供行锁以来，将有可能发生当其他用户设置了行级锁定时你又对该表设置了锁定。But that does not put transaction integerity into danger.
在 3.23.50 版本以前， SHOW TABLE STATUS 应用于一个自增表时将在自增列的最大记录索引上设置一个独占的行级锁定。这就意味着 SHOW TABLE STATUS 可能会引起一个事务的死锁，这可能是我们所意想不到的。从 3.23.50 开始，在读取自增列值时将不再设置任何锁定，除非在某些情况下，比如在数据库启动后没有任何记录。

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

如果你不使用 SET AUTOCOMMIT=0，MySQL 将会在一个会话中打开自动提交模式。在自动提交模式下，如果一条 SQL 语句没有返回任何错误，MySQL 将在这条 SQL 语句后立即提交。
如果一条 SQL 语句返回一个错误，那么 commit/rollback 依赖于这个错误。查看第国家13 章节详细描述。
下列的 SQL 语句在 MySQL 引起中当前事务的隐含提交：CREATE TABLE (如果使用了 MySQL 二进制日志'binlogging'), ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES。在 InnoDB 中 CREATE TABLE 语句是作为一个单独的事务来处理的。这就意味着一个用户无法在他的事务中使用 ROLLBACK 撤销CREATE TABLE 语句操作。
如果你关闭了自动提交模式，而在关闭一个连接之前又未使用 COMMIT 提交你的事务，那么 MySQL 将回滚你的事务。

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 行的索引记录(可能有几个)。

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

在 MySQL >=3.23.52 和 >= 4.0.3 的版本中使用 SHOW INNODB STATUS 来确定引起最后一个死锁的原因。这可以帮助你调整你的应用程序来避免死锁。
总是准备在因死锁而发生错误时重新发出一个事务。死锁并不危险。仅仅只需重试一遍。
经常提交你的事务。小的事务有较少的碰撞可能。
如果使用锁定读取 SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE，尽量使用较低的隔离级 READ COMMITTED。
以一个固定秩序(a fixed order)访问你的表和记录。这样事务将形成一个较精细的队列，而避免死锁。
为你的表添加合适的索引。那么你的查询只需要扫描较少的索引，因而设置较少的锁定。使用 EXPLAIN SELECT 来确定 MySQL 为你的查询挑选的适当的索引。
尽量少用锁定：如果可以通过一个 SELECT 在一个较老的数据快照中获得所需数据，就不要再添加子句 FOR UPDATE 或 LOCK IN SHARE MODE 。在这时使用 READ COMMITTED 隔离级是较好的主意，因为在同一个事务中的每个 consistent read 只读取它最先确定的数据快照。
如果仍然没有什么补救效果，使用表级锁定连载你的事务(serialize transactions)：LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES。表级锁定可以使你的事务形成精细的队列。注意 LOCK TABLES 隐含地启动一个事务，就如同命令 BEGIN，UNLOCK TABLES 如同 COMMIT 一样隐含地结束一个事务。
连载事务(serialize transactions)的另一个解决办法就是建立一个仅有一行记录的辅助“信号量(semaphore)” 表。每一个事务在访问其它表之前均更新这个记录。通过这种方式所有的事务将持续执行。注意同时 InnoDB 实时死锁检测算法也在工作着，因为这个持续锁定(serializing lock)是一个行锁定。在 MySQL 中对于表级锁定我们必须采取超时方式。

引自：http://imysql.cn/docs/innodb/8.htm

posted on 2011-08-31 16:33 zhizhesky 阅读(955) 评论(0) 编辑收藏举报

刷新页面返回顶部

半夜ATM机前看书的那位

导航

公告