MysqlInnoDB事务模式与锁定.doc

Mysql InnoDB 事务模式与锁定8 InnoDB事务模式与锁定在InnoDB事务处理模式中，the goal has been to combine the best properties of amultiversioning 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个不同的事务隔离级。你可以f的mysqld区中设置所有连接的默认事务隔离级：transaction-isolation=READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE用户也可以通过下面的SQL语句为单个连接或所有新建的连接改变隔离级：SETSESSION|GLOBALTRANSACTION ISOLATION LEVELREAD UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE注意在这个SQL语句的语法中没有连字符。如果你在上述语句中详细指定关键字GLOBAL，它将决定新建连接的初始隔离级，但不会改变已有连接的隔离级。任何用户均可以更改自身会话的隔离级，即使是在一个事务处理过程中。在3.23.50以前的版本中SET TRANSACTION对InnoDB表无任何效果。在4.0.5以前的版本中只有REPEATABLE READ和SERIALIZABLE可用。可以通过下列语句查询全局和当前会话的事务隔离级：SELECTglobal.tx_isolation；SELECTtx_isolation；在InnoDB的行锁中使用所谓的next-key locking。这就意味着，除了索引记录外，InnoDB还可以锁定该索引记录前部间隙(gap)以阻塞其它用户在索引记录前部的直接插入。next-key lock意思是锁定一个索引记录以及该记录之前的间隙(gap)。gap lock就是只锁定某些索引记录之前的间隙。InnoDB中的隔离级详细描述：READ UNCOMMITTED这通常称为dirty read：non-locking SELECTs的执行使我们不会看到一个记录的可能更早的版本；因而在这个隔离度下是非consistentreads；另外，这级隔离的运作如同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 readConsistent read就是InnoDB使用它的多版本(multiversioning)方式提供给查询一个数据库在一个时间点的快照。查询将会检查那些在这个时间点之前提交的事务所做的改动，以及在时间点之后改变或未提交的事务?与这个规则相例外的是查询将检查查询自身发出的事务所做的改变。如果以默认的REPEATABLE READ隔离级，那么所有在同一事务中的consistent reads只读取同一个在事务中第一次读所确定的快照。你可以通过提交当前事务并发出一个新的查询以获得新的数据快照。Consistent read在InnoDB处理SELECT中的默认模式是READ COMMITTED和REPEATABLE READ隔离级。Consistent read对其所访问的表不加任何锁定，因而其它任何用户均可以修改在consistent read被完成之前自由的修改这些表。8.2 Locking readsConsistent read在某些情况下是不太方便的。假设你希望在表CHILD中插入一个新行，而这个子表已有一个父表PARENT。假设你使用consistent read了读取表PARENT并查看子表中对应记录。你真的能安全地在表CHILD中加入一个子行?不可能，因为在此期间可能有其它用户删除了表PARENT中的父行，而你并不知道它。解决的办法就是在锁定的方式LOCK IN SHARE MODE下运行一个SELECT。SELECT*FROM PARENT WHERE NAME=JonesLOCK 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 thephantom 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 TSELECT.FROM SWHERE.在已插入到表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 adeadlock,that is,a cycle in the waits-for graph of transactions.InnoDB不能检测出由MySQL的LOCK TABLES语句引起的死锁，或其它的表类型中的锁定所引起的死锁。你不得不通过在f中设置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 AUser Bset autocommit=0；set autocommit=0；SELECT*FROM t；empty set INSERT INTO tVALUES(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 tLOCK 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 COMMI