mysql 锁及解决.docx

1、mysql 锁及解决15.2.10.InnoDB事务模型和锁定15.2.10.1. InnoDB锁定模式15.2.10.2. InnoDB和AUTOCOMMIT15.2.10.3. InnoDB和TRANSACTION ISOLATION LEVEL15.2.10.4. 持续非锁定读15.2.10.5. 锁定读SELECT . FOR UPDATE和SELECT . LOCK IN SHARE MODE15.2.10.6. Next-Key锁定：避免匪夷所思的问题15.2.10.7. 持续读如何在InnoDB中作用的例子15.2.10.8. 在InnoDB中用不同的SQL语句设定锁15.2.1

2、0.9. MySQL什么时候提交或回滚一个事务？15.2.10.10. 死锁检测回滚15.2.10.11. 如何应对死锁在InnoDB事务模式中，目的是把多版本数据库的最好特性与传统的二相锁定合并起来。InnoDB以Oracle的风格，对行级进行锁定，并且默认运行查询作为非锁定持续读。在InnoDB中锁定的表被存储得如此节省空间，以至于不需要锁定增大：典型地，数个用户被允许在数据库中锁定每一行，或者行的任何随机子集，而InnoDB不会耗尽内存。 15.2.10.1.InnoDB锁定模式InnoDB实现标准行级锁定，在这里有两种类型的锁： locks: 共享的(S)锁允许一个事务去读一行（tup

3、le）。 独占的锁(X)允许一个事务更新或删除一行。 如果事务A 在tuple t上持有独占锁定，来自不同事务B的对t上任一类型的锁的请求不被马上许可，取而代之地，事务B 不得不等待事务t释放在tuple t上的锁。 如果事务 A 在tuple t上持有一个共享的锁(S)，那么 来自不同的事务B对在t上X的锁定请求不能被马上许可。 来自不同的事务B对在t 上S的锁定请求可以被马上获准。因此A和B持有t上的S锁定。 不仅如此，InnoDB支持多间隔尺寸锁定，它允许记录锁和对整个表的锁共存。要使得多间隔尺寸级别的锁定实际化，额外类型的锁，被称为intention locks被使用。在InnoDB中

4、，意图锁定是表锁定。 对于一个事务，意图锁定之后理想的是指明在该表中对一个行随后需要哪一类型的锁定（共享还是独占）。有两种意图锁被用在InnoDB中（假设事务T 在表R中要求一个已指出的类型的锁）： 意图共享(IS)：事务T 意图给表T上单独的tuple设置S 锁定。 意图独占(IX)：事务T 意图给这些tuple设置X锁定。 意图锁协议如下： 在假设的事务可以获得对某假定行的S 锁定之前，它必须首先获得对包含该行的表的一个IS 或者更强的锁定。 在假设的事务可以获得对某假定行的X 锁定之前，它必须首先获得对包含该行的表的一个IX 锁定。 这些结果可以方便地用一个锁类型兼容矩阵来总结： XIX

5、SISX冲突 冲突冲突冲突IX冲突兼容冲突兼容S冲突冲突兼容兼容IS冲突兼容兼容兼容如果一个锁定与现在锁定兼容的话，它被授给一个委托事务。如果一个锁定与现存锁定冲突，它就不被授予一个委托事务。事务等待着直到冲突的现存锁定被释放掉。如果一个锁定请求与现存锁定相冲突，且不能被授予，因为它可能会导致死锁，一个错误产生。 因此，意图锁定不阻碍任何东西，除了完全表请求（比如LOCK TABLES . WRITE）。IX 和IS锁定的主要目的是显示某人正锁定一行，或将要在表中锁定一行。 下列的例子演示当锁定请求可能会导致死锁之时一个错误会如何发生。例子中包括两个客户端A和B。 首先客户端A创建一个包含一个

6、行的表，然后开始一个事务。在这个事务内，A通过在共享模式选择行获得对行的S 锁定： mysql CREATE TABLE t (i INT) ENGINE = InnoDB;Query OK, 0 rows affected (1.07 sec)mysql INSERT INTO t (i) VALUES(1);Query OK, 1 row affected (0.09 sec)mysql START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql SELECT * FROM t WHERE i = 1 LOCK IN SHARE

7、MODE;+-+| i |+-+| 1 |+-+1 row in set (0.10 sec)接着，客户端B开始一个事务并尝试从该表删除行： mysql START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql DELETE FROM t WHERE i = 1;删除操作要求一个X 锁定。因为这个锁定不兼容客户端A持有的S锁定，所以X 锁定不被允许，所以请求进入对行及客户端阻挡的锁定请求队列。 最后，客户端A也试图从表中删除该行： mysql DELETE FROM t WHERE i = 1;ERROR 1213 (40001):

8、 Deadlock found when trying to get lock;try restarting transaction因为客户端A需要一个X 锁定来删除该行，所以在这里发生死锁。尽管如此，锁定请求不被允许，因为客户端B已经有一个对X锁定的请求并且它正等待客户端A释放S锁定。因为客户端B之前对X 锁定的请求，被客户端A持有的S锁定也不能升级到X锁定。因此，InnoDB对客户端A产生一个错误，并且释放它的锁定。在那一点上，客户端B的锁定请求可以被许可，并且客户端B从表中删除行。 15.2.10.2.InnoDB和 AUTOCOMMIT在InnoDB中，所有用户行为都在事务内发生。如果

9、自动提交模式被允许，每个SQL语句在它自己上形成一个单独的事务。MySQL总是带着允许自动提交来开始一个新连接。 如果自动提交模式被用SET AUTOCOMMIT = 0关闭，那么我们可以认为一个用户总是有一个事务打开着。一个SQL COMMIT或ROLLBACK语句结束当前事务并且一个新事务开始。两个语句都释放所有在当前事务中被设置的InnoDB锁定。一个COMMIT语句意味着在当前事务中做的改变被生成为永久的，并且变成其它用户可见的。一个ROLLBACK语句，在另一方面，撤销所有当前事务做的修改。 如果连接有被允许的自动提交，通过用明确的START TRANSACTION或BEGIN语句来

10、开始一个事务，并用COMMIT或者ROLLBACK语句来结束它，这样用户仍旧可以执行一个多重语句事务。 15.2.10.3.InnoDB和TRANSACTION ISOLATION LEVEL按照SQL:1992 事务隔离级别，InnoDB默认是可重复读的（REPEATABLE READ）。MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别。你可以在命令行用-transaction-isolation选项，或在选项文件里，为所有连接设置默认隔离级别。例如，你可以在my.inf文件的mysqld节里类似如下设置该选项：globally mysqldtransaction-iso

11、lation = READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下： SET SESSION | GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE注意，对-transaction-isolation选项的级别名中有连字符，但在对SET TRANSACTION语句的级

12、别名中没有。 默认的行为是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER全县来做这个。使用SESSION 关键字集为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。 你可以用下列语句查询全局和会话事务隔离级别： SELECT global.tx_isolation;SELECT tx_isolation;在行级锁定中，InnoDB使用next-key锁定。这意味着除了索引记录，InnoDB也可以把索引

13、记录前的间隙锁定到其它用户所做的紧接该索引记录之前的块插入上。一个next-key锁定指向一个锁定住一个索引记录和它之前的间隙的锁定。一个间隙锁定指仅锁住一些索引记录之前的间隙的锁定。 InnoDB中每个隔离级别的详细描述如下： READ UNCOMMITTED SELECT语句以非锁定方式被执行，但是一个可能更早期版本的记录会被用到。因此，使用这个隔离级别，比如，读是不连贯的。着也被称为“脏读”（dirty read）。另外，这个隔离级别象READ COMMITTED一样作用。 READ COMMITTED 一个有些象Oracle的隔离级别。所有SELECT . FOR UPDATE和SEL

14、ECT . LOCK IN SHARE MOD语句仅锁定索引记录，而不锁定记录前的间隙，因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录，而不包括记录前的间隙。在范围类型UPDATE和DELETE语句，InnoDB必须对范围覆盖的间隙设置next-key锁定或间隙锁定以及其它用户做的块插入。这是很必要的，因为要让MySQL复制和恢复起作用，“幽灵行”必须被阻止掉。 持续读行为如同在Oracle中：即使在同一事务内， 每个持续读设置并读取它自己的新快照。请参阅15.2.10.4节，“持续非锁定读”。 REPEATABL

15、E READ 这是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT . FOR UPDATE, SELECT . LOCK IN SHARE MODE, UPDATE 和DELETE语句只锁定找到的索引记录，而不锁定记录前的间隙。用其它搜索条件，这些操作采用next-key锁定，用next-key锁定或者间隙锁定锁住搜索的索引范围，并且阻止其它用户的新插入。 在持续读中，有一个与之前隔离级别重要的差别：在这个级别，在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句，这些SELECT语句对相互之间也是持续的，请

16、参阅15.2.10.4节，“持续非锁定读”。 SERIALIZABLE 这个级别类似REPEATABLE READ，但是所有无格式SELECT语句被隐式转换成SELECT . LOCK IN SHARE MODE。 15.2.10.4. 持续非锁定读持续读意味着InnoDB使用它的多版本化来给一个查询展示某个时间点处数据库的快照。查询看到在那个时间点之前被提交的那些确切事务做的更改，并且没有其后的事务或未提交事务做的改变。这个规则的例外是，查询看到发布该查询的事务本身所做的改变。 如果你运行在默认的REPEATABLE READ隔离级别，则在同一事务内的所有持续读读取由该事务中第一个这样的读所

17、确立的快照。你可以通过提交当前事务并在发布新查询的事务之后，为你的查询获得一个更新鲜的快照。 持续读是默认模式，在其中InnoDBzai在READ COMMITTED和REPEATABLE READ隔离级别处理SELECT语句。持续读不在任何它访问的表上设置锁定，因此，其它用户可自由地在持续读在一个表上执行的同一时间修改这些表。 注意，持续读不在DROP TABLE和ALTER TABLE上作用。持续读不在DROP TABLE上作用，因为MySQL不能使用已经被移除的表，并且InnoDB 破坏了该表。持续读不在ALTER TABLE上作用，因为它在某事务内执行，该事务创建一个新表，并且从旧表往

18、新表中插入行。现在，当你重新发出持续读之时，它不能在新表中看见任何行，因为它们被插入到一个在持续读读取的快照中不可见的事务里。 15.2.10.5. 锁定读SELECT . FOR UPDATE和SELECT . LOCK IN SHARE MODE在一些环境中，一个持续读是不方便的。比如，你可能想要往表的子表里添加一个新行，并确信该子表在父表中有一个根。下列例子显示如何在你应用程序代码中实现参考的完整性。 假设你使用一个持续读去读取父表并且看到表中子表的根。不能安全地往子表添加子行吗？不，因为可能同时发生一些其它用户从父表删除父行，而你没有注意到它的情况。 解决办法是在使用LOCK IN S

19、HARE MODE的锁定模式执行SELECT： SELECT * FROM parent WHERE NAME = Jones LOCK IN SHARE MODE;在共享模式执行一个读意味着我们读最新的可用数据，并在我们读的行设置一个共享锁定。共享模式锁防止其它人更新或删除我们已读的行。同时，如果最新的数据属于其它客户端尚未提交的事务，我们等着知道那个事务被提交。我们看到前述的查询返回父Jones，我们可以安全地 往子表添加子记录并提交我们的事务。 让我们看另外一个例子：我们在表child_codes 中有一个整数计数器域，我们用该表给每个添加到子表里的子项指派一个唯一的识别符。显然，使用持

20、续读或者共享模式读去读取当前计数器的值并是一个好主意， 因为数据库的两个用户可能看到计数器的同一个值，如果两个用户试着用同一识别符往该表添加子项，就会发生一个重复键（duplicate-key）错误。 在这里，如果两个用户同时读计数器，当试图更新计数器之时，至少它们中有一个会发生死锁错误并终止，因此LOCK IN SHARE MODE并不是一个好的解决方法。 在这种情况下，有两个好方法去实现读计数器和增长计数器值：(1)先更新计数器，让计数器值增1，之后读计数器，或者(2)用锁定模式FOR UPDATE先读计数器，之后计数器值增加。后一个途径可被如下实现： SELECT counter_fie

21、ld FROM child_codes FOR UPDATE;UPDATE child_codes SET counter_field = counter_field + 1;SELECT . FOR UPDATE读最新的可见数据，在每个它读取的行设置独占锁定。因此，它设置与搜索的SQL UPDATE可能会在行上设置的锁定同样的锁定。 请注意，以上仅是一个SELECT . FOR UPDATE如何起作用的例子。在MySQL中，事实上生成一个唯一识别符的特殊任务可被用对该表的单独访问来完成： UPDATE child_codes SET counter_field = LAST_INSERT_I

22、D(counter_field + 1);SELECT LAST_INSERT_ID();SELECT语句仅仅取回识别符信息（专门对当前连接）。它不访问任何表。 15.2.10.6.Next-Key锁定：避免幽灵问题在行级锁定中，InnoDB 使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定：当它搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁定事实上是索引记录锁定。 InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定，另一个用户 不能紧接在R之前以索引的顺序插入一

23、个新索引记录。这个间隙的锁定被执行来防止所谓的“幽灵问题”。假设你想要从有一个标识符值大于100的子表读并锁定所有子记录，并想着随后在选定行中更新一些列： SELECT * FROM child WHERE id 100 FOR UPDATE;假设在id列有一个索引。查询从id大于100的第一个记录开始扫描。如果设置在索引记录上的锁定不把在间隙生成的插入排除在外，一个新行可能与此同时被插进表中。如果你在同一事务内执行同样的SELECT，你可能会在该查询返回的结果包里看到一个新行。这与事务的隔离原则是相反的：一个事务应该能够运行，以便它已经读的数据在事务过程中不改变。如果我们把一套行视为数据项，

24、新的“幽灵”子记录可能会违反这一隔离原则。 当InnoDB扫描一个索引之时，它也锁定所以记录中最后一个记录之后的间隙。刚在前一个例子中发生：InnoDB设置的锁定防止任何插入到id可能大过100的表。 你可以用next-key锁定在你的应用程序上实现一个唯一性检查：如果你以共享模式读数据，并且没有看到你将要插入的行的重复，则你可以安全地插入你的行，并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复。因此，the next-key锁定允许你锁住在你的表中并不存在的一些东西。 15.2.10.7.持续读如何在InnoDB中工作的例子假设你正运行在默认

25、的REPEATABLE READ隔离级别。当你发出一个持续读之时，即一个普通的SELECT语句，InnoDB按照你的查询看到的数据库，给你的事务一个时间点。如果另一个事务在你的时间点被指定之后删除一行并提交，你不会看见已被删除的行。插入和更新被相似地处理。 你可以通过提交你的事务来提前你的时间点，然后做另一个SELECT。 这被称为多版本并发控制（multi-versioned concurrency control）。 User A User B SET AUTOCOMMIT=0; SET AUTOCOMMIT=0;time| SELECT * FROM t;| empty set| INS

26、ERT INTO t VALUES (1, 2);|v SELECT * FROM t; empty set COMMIT; SELECT * FROM t; empty set COMMIT; SELECT * FROM t; - | 1 | 2 | - 1 row in set在这个例子中，只有当用户B已经提交插入，并且用户A也已经提交之时，用户A可看见被用户B插入的行，因此时间点是在用户B提交之前。 如果你想看数据库的最新状态，你应该用READ COMMITTED隔离级别或用一个锁定读： SELECT * FROM t LOCK IN SHARE MODE;15.2.10.8.在Inno

27、DB中不同SQL语句设置的锁定在SQL查询处理中，一个锁定读，一个UPDATE或一个DELETE一般地对被扫描的每个索引记录设置记录锁定。如果在某查询中有一个WHERE条件是没什么关系的，而这个查询可能从查询的结果包中排除行。InnoDB不记得确切的WHERE条件，但是仅知道哪个索引范围被扫描。记录锁定是正常的next-key锁定，它也阻止对紧接着记录之前的间隙的插入。 如果锁定被设置为独占，则InnoDB总是取回集束的索引目录并对其设置锁定。 如果你没有适合查询的索引，MySQL不得不扫描整个表来处理查询，表的每行变成被锁定的，这样反过来阻止其它用户的所有对表的插入。创建一个好的索引让你的查

28、询不必要扫描很多行是很重要的。 SELECT . FROM是一个持续读，读取数据库的快照并且设置不锁定，除非事务隔离级别被设为SERIALIZABLE。对于 SERIALIZABLE级别，这个设置对它遇到的索引记录设置共享的next-key锁定。 SELECT . FROM . LOCK IN SHARE MODE对读遇到的所有索引记录设置共享的next-key锁定。 SELECT . FROM . FOR UPDATE对读遇到的所有索引记录设置独占的next-key锁定。 INSERT INTO . VALUES (.)对被插入的行设置独占锁定。注意，这不是一个next-key锁定，并且不阻

29、止其它用户在已插入行之前的间隙插入。如果发生重复键错误，对重复的索引记录设置共享锁定。 在一个表上初始化之前指定的AUTO_INCREMENT列之时，InnoDB在与AUTO_INCREMENT列相关联的索引的末尾设置独占锁定。在访问自动增长计数器中，InnoDB使用专用的表锁定模式AUTO-INC，其中锁定仅持续到当前SQL语句的结束，而不是到整个事务的结束。 请参阅15.2.10.2节，“InnoDB和AUTOCOMMIT”。 InnoDB取回先前初始化的AUTO_INCREMENT列的值而不设定任何锁定。 INSERT INTO T SELECT . FROM S WHERE .对每个插入到T的行设置独占（非next-key）锁定。它在S上把搜索当作一个持续读，但是如果MySQL二进制日志功能被打开，它就对S设置一个共享的next-key锁定。InnoDB在后一种情况不得不设置锁定：在从一个备份的前滚恢复中，每个SQL语句不得不以与它最初被执行的方式完全同样的方式执行。 CREATE TABLE . SELECT .把SELECT当作一个持续读来执行，或者带着共享锁定来执行，如前面的条目所述。 如果唯一键没有冲突，REPLACE象一个插入一样被做。另外，对必须更新的行设置一个独占的next-key锁定。 U