MysqlInnoDB事务模式与锁定

1、.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内存运行溢

2、出。在InnoDB中，所有的用户操作均是以事务方式处理的。假设MySQL使用了自动提交autocommit方式，每个SQL语句将以一个单独的事务来处理。MySQL通常是以自动提交方式建立一个效劳连接的。假设使用SET AUTOCOMMIT=0关闭自动提交形式，就认为用户总是以事务方式操作。假设发出一个COMMIT或ROLLBACK的SQL语句，它将停顿当前的事务而重新开场新事务。两个语句将会释放所有在当前事务中设置的InnoDB锁定。COMMIT意味着永久改变在当前事务中的更改并为其它用户可见。ROLLBACK正好相反，它是取消当前事务的所有更改。假设以AUTOCOMMIT=1建立一个连接，那

3、么用户仍然可以通过以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语句为单个

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

5、IALIZABLE可用。可以通过以下语句查询全局和当前会话的事务隔离级：SELECTglobal.tx_isolation；SELECTtx_isolation；在InnoDB的行锁中使用所谓的next-key locking。这就意味着，除了索引记录外，InnoDB还可以锁定该索引记录前部间隙gap以阻塞其它用户在索引记录前部的直接插入。next-key lock意思是锁定一个索引记录以及该记录之前的间隙gap。gap lock就是只锁定某些索引记录之前的间隙。InnoDB中的隔离级详细描绘：READ UNCOMMITTED这通常称为dirty read：non-locking SELECT

6、s的执行使我们不会看到一个记录的可能更早的版本；因此在这个隔离度下是非consistentreads；另外，这级隔离的运作如同READ COMMITTED。READ COMMITTED有些类似Oracle的隔离级。所有SELECT.FOR UPDATE和SELECT.LOCK IN SHARE MODE语句只锁定索引记录，而不锁定之前的间隙，因此允许在锁定的记录后自由地插入新记录。以一个唯一地搜索条件使用一个唯一索引unique index的UPDATE和DELETE，仅仅只锁定所找到的索引记录，而不锁定该索引之前的间隙。但是在范围型的UPDATE and DELETE中，InnoDB必须设置

7、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，这些以唯一条件搜索唯一索引的，只锁定所找到的索引记录，而不锁定

8、该索引之前的间隙。否那么这些操作将使用next-key锁定，以next-key和gap locks锁定找到的索引范围，并阻塞其它用户的新建插入。在consistent reads中，与前一个隔离级相比这是一个重要的差异：在这一级中，同一事务中所有的consistent reads均读取第一次读取时已确定的快照。这个约定就意味着假设在同一事务中发出几个无格式plain的SELECTs，这些SELECTs的互相关系是一致的。SERIALIZABLE这一级与上一级相似，只是无格式plain的SELECTs被隐含地转换为SELECT.LOCK IN SHARE MODE。8.1 Consistent

9、readConsistent read就是InnoDB使用它的多版本multiversioning方式提供给查询一个数据库在一个时间点的快照。查询将会检查那些在这个时间点之前提交的事务所做的改动，以及在时间点之后改变或未提交的事务?与这个规那么相例外的是查询将检查查询自身发出的事务所做的改变。假设以默认的REPEATABLE READ隔离级，那么所有在同一事务中的consistent reads只读取同一个在事务中第一次读所确定的快照。你可以通过提交当前事务并发出一个新的查询以获得新的数据快照。Consistent read在InnoDB处理SELECT中的默认形式是READ COMMITTE

10、D和REPEATABLE READ隔离级。Consistent read对其所访问的表不加任何锁定，因此其它任何用户均可以修改在consistent read被完成之前自由的修改这些表。8.2 Locking readsConsistent read在某些情况下是不太方便的。假设你希望在表CHILD中插入一个新行，而这个子表已有一个父表PARENT。假设你使用consistent read了读取表PARENT并查看子表中对应记录。你真的能平安地在表CHILD中参加一个子行?不可能，因为在此期间可能有其它用户删除了表PARENT中的父行，而你并不知道它。解决的方法就是在锁定的方式LOCK IN

11、SHARE MODE下运行一个SELECT。SELECT*FROM PARENT WHERE NAME=JonesLOCK IN SHARE MODE；在共享形式下执行读取的意思就是读取最新的现有资料，并在所读取的行上设置一个共享形式的锁定。假设最新的数据属于其它用户仍未提交的事务，那将不得不等到这个事务被提交。共享形式的可以防止其它用户更新或删除我们当前所读取的行。当查询获得Jones后，就可以平安地向子表CHILD中参加子行，然后提交事务。这个例子显示如何在应用程序代码中实现参照完好性。另外一个例子：在表CHILD_CODES有一个整型计数字段用于给在表CHILD中参加的每个子行赋于一个唯

12、一的标识符。显而易见地，用一个consistent read来读取父表中的值并不是一个好的主意，因两个用户有可能会读取出同一个计数值，当以同一个标识符插入两个字行时将会产生一个重复键值duplicate key的错误。假设两个用户同时读取了计数器，当尝试更新计数器时，他们中的一个必将在死锁中完毕，所以在读取时使用LOCK IN SHARE MODE也并不是一个好的解决方法。在这和情况下有两种方法来实现读取并增加计数器：1首先更新计数器然后再读取它；2首先以一个FOR UPDATE方式锁定后再读取，然后再增加它：SELECT COUNTER_FIELD FROM CHILD_CODES FOR

13、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在索引记录上设置的锁同

14、样会影响索引记录之前的间隙gap。假设一个用户对索引记录R加了一个共享或排它的锁定，那其它用户将不能在R之前立即插入新的记录。这种间隙锁定用于防止所谓的phantom problem。假设需读取和锁定表CHILD中标识符大于100的子行，并更新所搜索到的记录中某些字段。SELECT*FROM CHILD WHERE ID 100 FOR UPDATE；假设表CHILD中有一个索引字段ID。我们的查询将从ID大于100的第一条记录开场扫描索引记录。如今，假设加在索引记录上的锁定不能阻止在间隙处的插入，一个新的子记录将可能在事务处理中被插入到表中。假设如今在事务中再次执行SELECT*FROM C

15、HILD WHERE ID 100 FOR UPDATE；在查询返回的记录集中将会有一个新的子记录。这与事务的隔离规那么相违犯的：一个事务必须可以顺串run，因此在事务处理中所读取的数据将不会发生改变。而新的phantom子记录将会打破这个隔离规那么。当InnoDB扫描索引时，它同样会锁定在索引中在结尾记录the last record之后的间隙。这仅仅在上例中会发生：InnoDB设置的锁定将阻止任何ID大于100的插入。在应用程序中可以通过一个next-key locking来实现一个唯一性uniqueness检查：假设以一个共享形式读取数据并没有发现与将要插入的数据存在重复值，那么在读取过

16、程中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：在所读取的所有记录索引上设置同享的锁定。SEL

17、ECT.FROM.FOR UPDATE：在所读取的所胡记录索引上设置独占地exclusive锁定。INSERT INTO.VALUES.：在插入的记录行上设置一个独占地锁定；注意这个锁定并不是一个next-key lock，并不会阻止其它用户在所插入行之前的间隙gap中插入新记录。假设产生一个重复键值错误，在重复索引记录上设置一个共享的锁定。假设在一个表中定义了一个AUTO_INCREMENT列，InnoDB在初始化自增计数器时将在与自增列最后一个记录相对应的索引上设置一个独占的锁定。在访问自增计数器时，InnoDB将设置一个特殊的表锁定形式AUTO-INC，这个锁定只持续到该SQL语句的完毕

18、而不是整个事务的完毕。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一致。否那么将在它所要更

19、新的记录上设置一个独占的锁定。UPDATE.SET.WHERE.：在搜索时所遭遇到的记录上设置一个独占的锁定。DELETE FROM.WHERE.：在搜索时所遭遇到的每一个记录上设置一个独占的锁定。假设一个表上有FOREIGN KEY约束，所有需要检查约束条件的insert,update,或delete将在它所要检查约束的记录上设置记录共享级的锁定。同样在约束失败时，InnoDB也设置这个锁定。LOCK TABLES.：设置表锁定。在MySQL的代码层layer of code设置这些锁定。InnoDB的自动死锁检测无法检测出有关以下情形的表锁定：查看下面的一个章节。同时查看第14章节Inno

20、DB限制与缺乏有关以下内容：自从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什么时候隐含地提交c

21、ommit或回滚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,

22、TRUNCATE,LOCK TABLES,UNLOCK TABLES。在InnoDB中CREATE TABLE语句是作为一个单独的事务来处理的。这就意味着一个用户无法在他的事务中使用ROLLBACK撤销CREATE TABLE语句操作。假设你关闭了自动提交形式，而在关闭一个连接之前又未使用COMMIT提交你的事务，那么MySQL将回滚你的事务。8.6死锁检测与回滚InnoDB会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。从4.0.5版开场，InnoDB将设法提取小的事务来进展回滚。一个事务的大小由它所插入insert、更新update和删除delete的数据行数决定。Previous

23、 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执行一个事务完好的回滚，这个事务所有所加的锁将被释放。然而，假

24、设只一句的SQL语句因结果返回错误而进展回滚的，由这条SQL语句所设置的锁定可能会被保持。这是因为InnoDB r的行锁存储格式无法知道锁定是由哪个SQL语句所设置。8.7 consistent read在InnoDB运作例如假设你以默认的REPEATABLE READ事务隔离级程度运行。当你发出一个consistent read时，即一个普通的SELECT语句，InnoDB将按照你的查询检查数据库给你的事务一个时间点timepoint。因此，假设事务B在给你指定的时间点后删除了一行并提交，那么你并不能知道这一行已被删除。插入insert与更新update也是一致的。你可以通过提交你的事务并重

25、新发出一个SELECT来将你的时间点提早。这就叫做multiversioned并发控制。time|v User AUser Bset autocommit=0；set autocommit=0；SELECT*FROM t；empty set INSERT INTO tVALUES1,2；SELECT*FROM t；empty set COMMIT；SELECT*FROM t；empty set；COMMIT；SELECT*FROM t；-|1|2|-因此，只有当用户B提交了他的插入，并且用户A也提交了他的事务从而使时间点越过B提交时的时间点之后，用户A才能看到用户B所插入的新行。假设你希望查看

26、数据库最新的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