原始博文链接

前言

本篇译自Mysql官网参考手册（5.7）的锁和事务章节，集体认为翻译价值还能够，因而翻译如下。浏览须要有一些InnoDB的基本概念，如有须要能够先浏览后面的章节。翻译程度无限，有不当之处还请指出。

InnoDB Locking and Transaction Model

要实现大规模、高并发或高牢靠的数据库利用，或者要从不同的数据库系统移植大量的代码，又或者是要优化MySQL的性能，理解InnoDB的锁和事务的模型是十分重要的。

本章节探讨了几个您应该须要相熟的、与InnoDB锁和InnoDB事务模型相干的几个话题。

  • 14.7.1 “InnoDB锁” 形容了InnoDB所应用的各种锁的类型。
  • 14.7.2 “InnoDB事务模型” 形容了各个事务隔离级别的性质以及各个级别所应用的锁策略。同时探讨了autocommit、一致性非锁定读取（consistent non-locking reads）和锁定读取（Locking reads）的利用。
  • 14.7.3 “InnoDB中由不同的SQL语句设置的锁” 探讨了InnoDB中各种不同语句设置的锁的类型。
  • 14.7.4 “幻读行” 形容了InnoDB如何采纳next-key锁来防止幻读。
  • 14.7.5 “InnoDB中的死锁” 提供一个死锁示例，探讨了死锁的发现和回滚，同时提供了一些在InnoDB中缩小和解决死锁的tips。

InnoDB Locking

本章节形容了InnoDB所应用的锁的类型。

  • Shared and Exclusive Locks（共享锁和排他锁）
  • Intention Locks（意向锁）
  • Record Locks（单个记录锁）
  • Gap Locks（间隙锁）
  • Next-Key Locks
  • Insert Intention Locks（插入意向锁）
  • AUTO-INC Locks
  • Predicate Locks for Spatial Indexes

Shared and Exclusive Locks

InnoDB实现了规范行级锁，有两种类型，别离是共享锁（S锁）和排他锁（X锁）。

  • 一把共享（S）锁容许持有锁的事务读取一行。
  • 一把排他（X）锁容许持有锁的事务更新或删除一行。

如果事务T1在行r上持有一把共享锁，那么来自其余事务T2的对行r的获锁申请将以如下形式解决：

  • 对获取行r共享锁的申请能够被立刻通过。这样的后果就是T1和T2在行r上同时各自持有一把共享锁。
  • 对获取行r排他锁的申请无奈被立刻通过。

如果事务T1在行r上持有一把排他锁，来自其余事务T2对行r的获取任意一种锁申请都无奈被立刻通过。事务T2必须期待事务T1开释它在行r上的锁。

Intention Locks

InnoDB反对多颗粒度的锁定，容许行锁和表锁共存。举例来说，相似LOCK TABLES ... WRITE的语句将在指定的表上获取一把排他锁（X锁）。为了可能在多颗粒度上实现锁定，InnoDB应用意向锁。意向锁是表级锁，用于申明事务稍后须要对表中的一行获取的哪种类型的锁（共享或排他）。有两种意向锁：

  • 一把动向共享（IS）锁表明一个事务试图对表中的某行设置共享锁。
  • 一把动向排他（IX）锁表明一个事务试图对表中的某行设置排他锁。

例如，SELECT ... LOCK IN SHARE MODE设置了一把IS锁，SELECT ... FOR UPDATE设置了一把IX锁。

意向锁协定如下：

  • 在事务可能获取表中某一行的共享锁之前，它必须在相应的表上获取一把IS锁或者更强的锁。
  • 在事务可能获取表中某一行的排他锁之前，它必须在相应的表上获取一把IX锁。

表级锁类型的兼容性总结如下表：

XIXSIS
X不兼容不兼容不兼容不兼容
IX不兼容兼容不兼容兼容
S不兼容不兼容兼容兼容
IS不兼容兼容兼容兼容

如果一个事务申请的锁与现有的锁兼容，那么申请将被容许，如果抵触则将被回绝。事务将期待直到曾经存在的不兼容的锁被开释。如果一个获锁申请与已存在的锁抵触，且可能会引发死锁，将会呈现一个异样。

意向锁不会阻塞除了全表申请（例如LOCK TABLES ... WRITE）之外的操作。意向锁次要的目标是表明某个事务正在或正将要锁定表中的某一行。

一个意向锁的事务数据以相似如下的模式呈现在显示InnoDB状态命令（SHOW ENGINE engine_name {STATUS | MUTEX}）和InnoDB监控的输入中：

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

Record Locks

记录锁是索引记录上的锁。例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;将阻止任何其余事务插入、更新或者删除t.c1的值是10的行。

记录锁锁定的肯定是索引记录，即便在没有定义索引的表上也是如此。在这种状况下，InnoDB创立了一个暗藏的汇集索引，并应用这个索引来锁定记录。见章节 14.6.2.1 “Clustered and Secondary Indexes”。

一个记录锁的事务数据以相似如下的模式呈现在显示InnoDB状态命令和InnoDB监控的输入中：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`trx id 10078 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 8000000a; asc ;;1: len 6; hex 00000000274f; asc 'O;;2: len 7; hex b60000019d0110; asc ;;

Gap Locks

间隙锁是指索引记录之间的间隙上的锁，或者是在第一条索引记录之前或最初一条索引记录之后的间隙上的锁。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;阻止任何其余事务插入t表c1列值为15的数据，无论是否曾经存在此类值的记录，因为所有现有值之间的间隙范畴曾经被锁定。

间隙可能逾越单个索引值、多个索引值，甚至是空的。

间隙锁是性能和并发性之间衡量的一部分，只用于个别事务隔离级别。

对于应用惟一索引锁定行以搜寻惟一行的语句，不须要间隙锁。（这不包含搜寻条件只蕴含多列惟一索引的某几列的状况；在这种状况下会应用间隙锁。）例如，如果id列有一个惟一索引，上面的语句只会对id值为100的行应用一个索引记录锁，而不会影响别的会话在后面的间隙中插入行：

SELECT * FROM child WHERE id = 100;

如果id列没有被索引或者有惟一索引，上述语句将会锁定间隙。值得注意的是，间隙上能够放弃不同事务相冲突的锁。例如，事务A能够在某个间隙上持有共享间隙锁（gap S-lock），同时事务B在同一个间隙上持有排他间隙锁（gap X-lock）。抵触的间隙锁能够共存的起因是如果从索引中革除记录，则必须合并记录上由不同事务持有的间隙锁。

在InnoDB中间隙锁只用于克制操作，这意味着它们的惟一指标就是阻止其余事务在间隙中插入内容。间隙锁能够共存。一个事务持有的间隙锁不会阻止任何其余事务在雷同的间隙上获取间隙锁。共享间隙锁和排他间隙锁并没有区别。它们之间不会互相抵触，执行雷同的性能。

间隙锁能够被显式禁用。当您将隔离级别调整为READ COMMITTED或者启用了innodb_locks_unsafe_for_binlog零碎变量（当初曾经过期）时将会禁用间隙锁。在这些状况下，在搜寻和索引扫描时将不会应用间隙锁，间隙锁仅用于外键束缚检查和反复键查看。

应用READ COMMITTED隔离级别或者启用innodb_locks_unsafe_for_binlog还有一些其余影响。在MySQL执行WHERE条件后，会开释不匹配行的记录锁。对于更新语句，InnoDB将执行半一致性读来返回最新提交的版本给MySQL，MySQL以此决定行是否满足更新语句的WHERE条件。

Next-key Locks

next-key锁是索引上的记录锁和索引记录之前的间隙上的间隙锁的组合。

InnoDB执行行级锁定的形式是，当它搜寻或扫描表索引时，它会在遇到的索引记录上设置共享锁或排他锁。因而，行级锁实际上是索引记录锁。索引记录上的next-key锁同时会影响在该索引记录之前的“间隙”。也就是说，next-key锁是一个索引记录锁加上索引记录之前的间隙上的间隙锁。如果一个会话领有索引中记录R的共享锁或排他锁，其余的会话无奈按索引程序在R之前的间隙中插入新的索引记录。

假如一个索引蕴含了值10、11、13和20。该索引可能蕴含的next-key锁蕴含以下区间，其中圆括号示意对区间端点的排除，方括号示意蕴含端点：

(negative infinity, 10](10, 11](11, 13](13, 20](20, positive infinity)

对于最初一个区间，next-key锁锁定了索引中最大值以上的间隙以及蕴含了大于索引中任何值的“上界”伪记录。上确界（ supremum）不是真正的索引记录，因而，实际上next-key锁只锁定了最大索引值之后的间隙。

默认状况下，InnoDB在REPEATABLE READ事务隔离级别下运作。在这种状况下，InnoDB应用next-key锁进行搜寻和索引扫描，从而避免幻读（见章节14.7.4 “Phantom Rows”）。

一个next-key锁的事务数据以相似如下的模式呈现在显示InnoDB状态命令和InnoDB监控的输入中：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`trx id 10080 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 8000000a; asc ;;1: len 6; hex 00000000274f; asc 'O;;2: len 7; hex b60000019d0110; asc ;;

Insert Intention Locks

插入意向锁是插入操作在插入行之前设置的一种间隙锁。这种锁批示多个事务插入雷同的索引间隙时，如果它们不在间隙中雷同的地位插入则不须要互相期待对方。假如有值为4和7的索引记录。不同的事务别离尝试插入值为5和6的的记录，每个事务在获取插入行的排他锁之前应用插入意向锁锁定4和7之前的间隙，但它们不会互相阻塞，因为要插入的行是不抵触的。

上面的示例演示了事务在获取插入记录的排他锁之前应用插入意向锁。这个例子波及A和B两个客户。

客户A创立了蕴含两个索引记录（90和102）的表，而后开始一个事务，对ID大于100的索引记录设置排他锁。排他锁蕴含了一个102记录之前的间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;mysql> INSERT INTO child (id) values (90),(102);mysql> START TRANSACTION;mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;+-----+| id |+-----+| 102 |+-----+

客户B开始一个事务来向间隙中插入一条记录。当事务期待获取排他锁时应用了一个插入意向锁。

mysql> START TRANSACTION;mysql> INSERT INTO child (id) VALUES (101);

一个插入意向锁的事务数据以相似如下的模式呈现在显示InnoDB状态命令和InnoDB监控的输入中：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`trx id 8731 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000066; asc f;;1: len 6; hex 000000002215; asc " ;;2: len 7; hex 9000000172011c; asc r ;;...

AUTO-INC Locks

AUTO-INC锁是一种非凡的表级锁，由插入具备 AUTO_INCREMENT属性列的表的事务应用。在最简略的状况下，如果一个事务正在向表中插入值，则任何其余事务都必须期待对该表执行本人的插入操作，以便第一个事务插入的揭发接管间断的主键值。

配置项innodb_autoinc_lock_mode管制了主动递增锁定所应用的算法。它容许您抉择如何在可预测的主动增量值序列和插入操作的最大并发性之间进行衡量。

详情请见章节14.6.1.4 “AUTO_INCREMENT Handling in InnoDB”。

Predicate Locks for Spatial Indexes

InnoDB反对蕴含空间列的空间索引（见章节 11.5.8 “Optimizing Spatial Analysis”）。

要解决波及空间索引锁定的操作，next-key锁并不能很好地反对REPEATABLE READ或SERIALIZABLE事务隔离级别。多维数据中没有相对程序的概念，因而无奈明确哪一个是“下一个”键。

为了反对具备空间索引的表的事物隔离级别，InnoDB应用predicate锁。空间索引蕴含了最小矩形边界(MBR)值，因而InnoDB通过对用于查问的MBR值设置predicate锁来强制对索引进行统一的读取。其余事务不能插入或批改与查问条件匹配的行。

InnoDB Transaction Model

InnoDB事务模型的指标是将多版本数据库的长处与传统的二阶段锁联合起来。InnoDB在行级别执行锁定，同时在默认状况下以Oracle格调的非锁定一致性读（nonlocking consistent reads）的模式来运行查问。InnoDB中锁信息存储的空间利用率很高，因而扩充锁不是必要的（这句翻的可能有问题）。通常状况下，容许多个用户锁定InnoDB表中的任何一行，或者是所有行的任意子集，而不会造成InnoDB内存耗尽。

Transaction Isolation Levels

事务隔离是数据库运行的根底之一。隔离是缩写ACID中的 I；隔离级别是在多个事务同时进行批改和执行查问时对性能和后果的可靠性、一致性和可重现性之间均衡的的设定。

InnoDB提供了SQL：1992规范所规定的所有4种隔离级别：READ UNCOMMITTED（未提交读），READ COMMITTED（提交读），REPEATABLE READ（可反复读），和SERIALIZABLE（序列化）。InnoDB默认的隔离级别是REPEATABLE READ。

用户能够更改单个会话的隔离级别，或者通过SET TRANSACTION命令批改所有后续连贯的隔离级别。如果须要为所有连贯设置服务的默认隔离级别，能够在命令行或者配置文件中更改--transaction-isolation选项值。具体的隔离级别信息和隔离级别设置的语法请参考章节13.3.6，“SET TRANSACTION Syntax”。

InnoDB通过应用不同的锁策略来反对列出的这几个事务隔离级别。当解决要害的数据、必须听从ACID个性时，你可应用默认的REPEATABLE READ级别来强化高度的一致性。在精准一致性和可反复的后果没有减小锁开销重要的状况下，比方大批量的报告解决，你也能够不严格听从一致性规定，采纳READ COMMITTED甚至是READ UNCOMMITTED的隔离级别。SERIALIZABLE采纳了比REPEATABLE READ更加严格的规定，通常在特定的状况下应用，例如XA分布式事务以及发现解决并发和死锁的问题。

上面的列表阐明了MySQL是如何反对不同的事务级别的。列表依据罕用性从高到低排列。

  • REPEATABLE READ

    这是InnoDB默认的隔离级别。在同一事务中执行一致性读取由第一次读取建设的快照。这意味着如果你在同一个事务中发动屡次简略（非锁）SELECT语句，这些语句相互之间是统一的。详见章节14.7.2.3 “Consistent Nonlocking Reads”。

    对于锁定读取（SELECT时带上FOR UPDATE或LOCK IN SHARE MODE）、更新和删除语句，加锁取决于语句是否是在惟一查问条件或范畴查问条件下应用了惟一索引。

    • 对于应用惟一索引的繁多查问条件，InnoDB只锁定找到的索引记录，而不锁定它们之间的gap。
    • 对于其余的查问条件，InnoDB将应用gap locks或next-key locks来锁定扫描过的范畴，阻止其余会话在笼罩的范畴内进行插入。对于gap locks和next-key locks的信息，详见章节14.7.1 “InnoDB Locking”。

  • READ COMMITTED

    每一次一致性读取，包含同一次事务中，将会设置并读取它本身的最新快照。对于一致性读的详细信息，见章节14.7.2.3 “Consistent Nonlocking Reads”。

    对于锁定读取（SELECT时带上FOR UPDATE或LOCK IN SHARE MODE）、更新和删除语句，InnoDB只锁定索引记录，不锁定它们之间的gap，因而容许在锁定记录旁边自在插入新的记录。Gap Locking只用于外键束缚检查和反复主键查看。

    因为不应用gap locking，别的会话能够在间隙间插入新的记录，可能呈现幻读的问题。对于幻读的详细信息，见章节14.7.4 “Phantom Rows”。

    READ COMMITTED隔离级别只反对基于行的日志记录。如果你应用READ COMMITTED和选项binlog_format=MIXED，服务将主动应用基于行的日志记录。

    应用READ COMMITTED的其余成果：

    • 对于UPDATE或DELETE语句，InnoDB只持有更新、删除的行的锁。MySQL在执行WHERE条件后，不符合条件的行的Record locks将会被开释。这将极大缩小死锁的可能性，然而仍有可能产生。
    • 对于UPDATE语句，如果行曾经被锁，InnoDB将执行一次半一致性（semi-consistent）读取，返回最新提交的版本给MySQL，MySQL以此判断行记录是否匹配UPDATE语句的WHERE条件。如果行记录匹配（必须是更新的），MySQL将从新读取行，这时InnoDB将锁定记录或者期待锁开释。

    思考上面的例子，以这张表为根底：

    CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);COMMIT;

    在这个示例中，给定的表没有索引，因而搜寻和索引扫描应用暗藏的汇集索引来锁定记录（见章节14.6.2.1， “Clustered and Secondary Indexes”）而不是索引列。

    假如某一会话应用上面的语句执行UPDATE操作：

    # Session ASTART TRANSACTION;UPDATE t SET b = 5 WHERE b = 3;

    同时假如有另一个会话在此之后应用上面的语句执行UPDATE：

    # Session BUPDATE t SET b = 4 WHERE b = 2;

    当InnoDB执行这些UPDATE语句时，它首先为读取的每一行获取独占锁（X锁），而后决定是否批改它。如果InnoDB不批改行，它将开释锁。否则将放弃锁直到事务完结。这将影响事务的解决，如下所示。

    当应用默认的REPEATABLE READ隔离级别时，第一个UPDATE语句获取读取的每一行的独占锁，并且全副不开释：

    x-lock(1,2); retain x-lockx-lock(2,3); update(2,3) to (2,5); retain x-lockx-lock(3,2); retain x-lockx-lock(4,3); update(4,3) to (4,5); retain x-lockx-lock(5,2); retain x-lock

    第二个UPDATE将在其尝试获取锁时立即阻塞（因为第一个更新语句放弃了所有行的锁），不继续执行，直到第一个UPDATE提交或者回滚：

    x-lock(1,2); block and wait for first UPDATE to commit or roll back

    如果应用的是READ COMMITTED，第一个UPDATE为读取的每一行获取独占所，随后开释它进行不批改的行的锁：

    x-lock(1,2); unlock(1,2)x-lock(2,3); update(2,3) to (2,5); retain x-lockx-lock(3,2); unlock(3,2)x-lock(4,3); update(4,3) to (4,5); retain x-lockx-lock(5,2); unlock(5,2)

    然而，如果WHERE条件语句蕴含了索引列，同时InnoDB应用了索引，那么获取和开释记录锁只和索引列相干。在上面的例子中，第一个UPDATE语句获取并放弃b=2的行的独占锁。第二个UPDATE将在它获取雷同记录的独占锁时阻塞，因为它也应用了b列定义的索引。

    CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;INSERT INTO t VALUES (1,2,3),(2,2,4);COMMIT;# Session ASTART TRANSACTION;UPDATE t SET b = 3 WHERE b = 2 AND c = 3;# Session BUPDATE t SET b = 4 WHERE b = 2 AND c = 4;

    应用READ COMMITTED隔离级别造成的影响与启用不举荐的设置选项innodb_locks_unsafe_for_binlog雷同，除了以下几点：

    • 启用innodb_locks_unsafe_for_binlog是一个全局设置，影响所有会话，而隔离级别能够在全局设置或者对每个会话独自设置。
    • innodb_locks_unsafe_for_binlog选项只能在服务启动时设置，而隔离级别能够在启动时设置或者运行时更改。

    因而READ COMMITTED相比于innodb_locks_unsafe_for_binlog提供了更好、更弹性的管制。

  • READ UNCOMMITTED

    SELECT语句以非锁定形式执行，但可能返回行的晚期版本。因而，应用这个隔离级别，这种读取是不统一的。这也被称为脏读（dirty read）。在其余的方面，这个隔离级别的运作与READ COMMITTED类似。

  • SERIALIZABLE

    这个隔离级别与REPEATABLE READ类似，然而当autocommit敞开时InnoDB隐式地将所有一般SELECT语句转换为SELECT … LOCK IN SHARE MODE。如果autocommit开启，SELECT就是在其本身的事务内。因而可认为是只读的而且以统一读（非锁定）执行，并且不须要阻塞其余事务。（如果当其余事务曾经批改了抉择的行时须要强制SELECT语句阻塞，敞开autocommit）

autocommit, Commit, and Rollback

在InnoDB中，所有的用户行为产生在单个事务之中。如果autocommit选项开启，每一个SQL语句将以本身组成一个事务。默认状况下，MySQL对于每个新的连贯开始的会话都会开启autocommit，因而MySQL将在每一个没有返回谬误的SQL语句执行之后进行提交。如果语句返回了一个谬误，提交或是回滚的行为取将取决于谬误。见章节 14.21.4 ，“InnoDB Error Handling”。

一个开启了autocommit的会话能够通过开始前显式指定START TRANSACTIOn或者BEGIN语句来执行一个多语句事务，并且以COMMIT或者ROLLBACK语句完结事务。见章节 13.3.1，“START TRANSACTION, COMMIT, and ROLLBACK Syntax”。

如果在一个会话中应用SET autocommit = 0敞开主动提交模式，会话将总是放弃开启事务。一个COMMIT或者ROLLBACK语句将完结以后事务并且开启一个新的事务。

如果一个敞开autocommit的会话完结时没有显式提交最初的事务，MySQL将回滚该事务。

有些语句隐式地完结了事务，就如同在执行语句之前曾经实现了提交一样。详情见章节13.3.3，“Statements That Cause an Implicit Commit”。

COMMIT意味着以后事务所做的更改将长久化并且对其余会话可见。在另一方面，ROLLBACK语句示意勾销以后事务所做的所有更改。COMMIT和ROLLBACK都会开释以后事务期间设置的所有InnoDB锁。

Grouping DML Operations with Transactions

默认状况下，MySQL服务的连贯开始时autocommit都是开启的，这将会主动提交您执行的每一个SQL语句。如果您有解决其余数据库系统的教训，这种操作模式可能会不太适应，通常规范做法是发动一些列DML语句而后同时提交或者回滚它们。

要应用多语句事务，能够应用SQL语句SET autocommit = 0并且以COMMIT或者ROLLBACK完结每个事务。放弃autocommit开启的状况下，以START TRANSACTION开启事务并且以COMMIT或者ROLLBACK完结。上面的例子显示了两个事务。第一个提交，而第二个回滚。

shell> mysql testmysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));Query OK, 0 rows affected (0.00 sec)mysql> -- Do a transaction with autocommit turned on.mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO customer VALUES (10, 'Heikki');Query OK, 1 row affected (0.00 sec)mysql> COMMIT;Query OK, 0 rows affected (0.00 sec)mysql> -- Do another transaction with autocommit turned off.mysql> SET autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO customer VALUES (15, 'John');Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO customer VALUES (20, 'Paul');Query OK, 1 row affected (0.00 sec)mysql> DELETE FROM customer WHERE b = 'Heikki';Query OK, 1 row affected (0.00 sec)mysql> -- Now we undo those last 2 inserts and the delete.mysql> ROLLBACK;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM customer;+------+--------+| a | b |+------+--------+| 10 | Heikki |+------+--------+1 row in set (0.00 sec)mysql>
Transactions in Client-Side Languages

在MySQL的API中，例如PHP、Perl DBI、JDBC、ODBC或者是规范C调用接口，你能够以字符串发送相似COMMIT的事务管制语句，就如同其余所有SQL语句一样，比方SELECT或者INSERT。某些API还提供了其余非凡的提交或回滚事务的性能和办法。

Consistent Nonlocking Reads

一致性读取意味着InnoDB应用多版本控制将某个工夫点的数据库的快照提供给一个查问。在此之前提交的事务所做的扭转对查问是可见的，而不会看到之后提交的事务和未提交的事务所做的更改。这条规定的例外是查问能够看到同一事务中之前的语句所做的更改。这个例外将导致以下异样：如果你更新了表中的某几行，SELECT语句将看到更新的这几行的最新版本，但同时也可能会看到其余行的旧版本。如果其余会话同时更新了同一张表，异样意味着您可能会看到该表处于数据库中不存在的状态。

如果隔离级别为REPEATABLE READ（默认级别），同一事务内的所有一致性读将读取该事务中第一次读取所建设的快照。你能够通过提交事务，并在此之后发动新的查问来获取一个更加新的快照。

在READ COMMITTED隔离级别下，同一事务内的各个一致性读将建设和读取它本身的最新快照。

一致性读（consistent read）是InnoDB在READ COMMITTED和REPEATABLE READ隔离级别下执行SELECT语句的默认模式。一次一致性读取对它所解决的表不会设置任何锁，因而其余会话在表进行一致性读的时候能够同时任意批改这些表。

假如您正运行默认的REPEATABLE READ隔离级别。当您发动一次一致性读（一般的SELECT语句），InnoDB将依据查问数据的时刻给予事务一个工夫点。如果其余事务删除了某一行，并且在这个工夫点之后提交，您将不会看到行曾经被删除。插入和更新基本上也是如此。

留神：

在同一事务中利用于SELECT语句的数据库快照，对于DML语句（INSERT、UPDATE、DELETE）不是必要的。如果您插入或批改了某些行并且提交了事务，从并发的其余REPEATABLE READ事务中发动的DELETE和UPDATE语句可能影响这些刚提交的行，即便会话无奈查问它们。如果一个事务尝试更新或删除被不同的事务更改提交的记录，那么这些更改将对以后事务可见。例如，您可能会遇到相似上面的状况：

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';-- Returns 0: no rows match.DELETE FROM t1 WHERE c1 = 'xyz';-- Deletes several rows recently committed by other transaction.SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';-- Returns 0: no rows match.UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';-- Returns 10: this txn can now see the rows it just updated.

您能够通过提交事务而后发动其余的SELECT或者开启事务来减少工夫点。

这被称为多版本并发管制（MVCC）。

在上面的的例子中，会话A只有在B提交插入且A也提交了事务的状况下才可能看到B所插入的记录，因而（能够阐明）工夫点在B提交后减少。

            Session A                 Session B        SET autocommit=0;                SET autocommit=0;time|        SELECT * FROM t;|         empty set|                                 INSERT INTO t VALUES (1, 2);|v         SELECT * FROM t;        empty set                                COMMIT;        SELECT * FROM t;        empty set        COMMIT;        SELECT * FROM t;        ---------------------        | 1 | 2 |        ---------------------

如果您心愿看到数据库的“最新”状态，应用READ COMMITTED隔离级别或者锁定读：

SELECT * FROM t FOR SHARE;

在READ COMMITTED隔离级别下，事务内的每次一致性读将创立并读取它本身的最新快照。在LOCK IN SHARE模式下，取而代之产生的是锁定读：SELECT将会阻塞直到蕴含最新行的事务完结（见章节14.7.2.4 “Locking Reads”）。

一致性读不适用于某些DDL语句：

  • 一致性读不作用于DROP TABLE，因为MySQL不能应用一个曾经删除的表并且InnoDB毁坏了表。
  • 一致性读不作用于ALTER TABLE，因为这个语句对原始表做了一次长期拷贝并且在长期拷贝创立后删除原表。当您在一个事务中从新发动一次一致性读，新表中的即将不可见，因为这些行在事物的快照创立时并不存在。在这个状况下，事务将返回一个谬误：ER_TABLE_DEF_CHANGED，“Table definition has changed, please retry transaction”。

在子句中相似INSERT INTO ... SELECT，UPDATE ... (SELECT)和CREATE TABLE ... SELECT这样并没有指定FOR UPDATE或者LOCK IN SHARE MODE的抉择，读取类型将会发生变化：

  • 默认状况下，InnoDB应用更强的锁，SELECT局部体现的行为相似READ COMMITTED，包含同一事务中的每个一致性读设置并读取本身最新的快照。
  • 为了在这种状况下应用一致性读，能够开启innodb_locks_unsafe_for_binlog选项并且将事务的隔离级别设置为READ UNCOMMITTE、READ COMMITTED或者REPEATABLE READ（除了 SERIALIZABLE）。这种状况下，从抉择的表中读取的即将不会加锁。

Locking Reads

如果您在同一个事务中查问而后插入或批改相干的数据，惯例的SELECT语句无奈提供足够的爱护。其余的事务能够更新或删除您刚刚查问的行。InnoDB反对两种提供了额定的安全性的锁定读：

  • SELECT ... LOCK IN SHARE MODE

    对于正在读取的所有行设置共享模式的锁。其余会话能够读取行，然而在您的事务提交前无奈批改他们。如果这些行中的任意一行被其余尚未提交的事务更改，则您的事务将始终期待直到该事务完结，而后应用最新的值做解决。

  • SELECT ... FOR UPDATE

    对于查问中遇到的索引记录，锁定行和任何相干的索引项，就如同您对这些行发动了UPDATE语句一样。其余事务被阻止更新这些行、执行SELECT ... LOCK IN SHARE MODE或在某些隔离级别下读取数据。一致性读取疏忽在读取视图中存在的在记录上设置的任何锁。（记录的旧版本无奈被锁定；它们通过对记录在内存中的备份利用undo log来重构）

这些子句次要在解决树结构或图构造的数据时十分有用，无论是在单个表中还是拆分在多个表中。您从一个地位到另一个地位遍历边（图构造的edge）或树的分支时，同时保留返回和更改这些“指针”的值的权力。

当事务提交或回滚时，所有由LOCK IN SHARE MODE和FOR UPDATE查问所设置的锁都会被开释。

留神：

锁定读只在不启用主动提交的状况下失效（应用START TRANSACTION开始事务或设置autocommit为0）。

除非子查问中同时也指定了锁读子句，否则内部语句中的锁定读不会锁定嵌套子查问中表的行。例如，上面的语句不会锁定t2表中的行。

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

如果要锁定t2表中的行，须要给子查问增加锁定后缀：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;
Locking Read Examples

假如您想要向child表中插入新的一行，同时保障该行在parent表中有一个父级行。您的利用程序代码须要保障整个操作序列的援用完整性。

首先，应用一致性读查问parent表确认父级行存在。您是否平安地在child表中插入子行？并不能，因为其余会话可能在您SELECT和INSERT之间的工夫外在您不知情的状况下删除父级行。

为了防止这种可能的状况，应用LOCK IN SHARE MODE来执行SELECT：

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

在LOCK IN SHARE MODE查问返回父级‘Jones’之后，您能够平安地向child表中插入子记录，而后提交事务。任何试图在parent表的相应行中获取排他锁的的事务都将期待直到您（的事务）实现，或者说是期待直到所有表中的数据处于统一状态。

另外一个例子，思考在CHILD_CODES表中有个整形统计字段，该字段用于为增加到CHILD表中的每个子节点调配惟一标识。不要应用一致性读或共享模式读取计数器的以后值，因为数据库的两个用户在能够看到计数器雷同的值，如果两个事务试图向CHILD表中增加具备雷同标识的行，将会呈现键值反复的谬误。

在这种状况下，LOCK IN SHARE MODE并不是一个好的解决方案，因为两个用户同时读取计数器，至多其中一个将在其尝试更新计数器时以死锁终止。

为了实现读取并减少计数器，首先应用FOR UPDATE来对计数器执行锁定读，而后减少计数器。例如：

SELECT counter_field 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_ID(counter_field + 1);SELECT LAST_INSERT_ID();

SELECT语句仅检索标识信息（特定于以后链接）。它不拜访任何表。（注：这里翻的可能有问题）

Locks Set by Different SQL Statements in InnoDB

锁定读取、更新或删除通常在SQL语句执行的时候对其扫描过的所有索引记录设置记录锁。语句中是否存在排除行的条件并不重要。InnoDB不记录确切的WHERE条件，只记录被扫描的索引范畴。这些锁通常是同时阻止插入记录之前“间隙”的next-key锁。然而，间隙锁能够显式禁用，这将导致不应用next-key锁定。详情见章节14.7.1 “InnoDB Locking”。事务隔离级别也会影响设置的锁的类型，见章节14.7.2.1 “Transaction Isolation Levels”。

如果在搜寻中应用了辅助索引，而要设置的索引记录锁是独占的，则InnoDB将同时检索相应的汇集索引记录并在其上设置锁。

如果您的语句没有实用的索引，MySQL必须扫描整个表以解决该语句，则表的每一行都会被锁定，这将阻止其余用户对该表的所有插入操作。创立好的索引十分重要，这样您的查问就不会不必要地扫描过多的行。

InnoDB设置的锁的类型如下所示：

  • SELECT ... FROM采纳一致性读，读取数据库的快照，除非隔离级别设置为SERIALIZABLE，不会设置任何锁。在SERIALIZABLE级别下，搜寻将在它遇到的索引记录上设置共享的next-key锁。然而，对于应用惟一索引锁定行以搜寻惟一行的语句，只设置索引记录锁。
  • 对于SELECT ... FOR UPDATE或SELECT ... IN SHARE MODE，将在扫描的行获取锁，对于不符合条件不蕴含在后果集中的行，它们的锁将被开释（例如，它们不合乎在WHERE语句中的条件）。然而，在某些状况下，行可能不会立刻被解锁，因为后果行与它原始数据之间的关系在查问执行过程中失落。例如，在UNION语句中，表中被扫描（同时被锁定）的行可能会被插入长期表中，而后再计算它们是否合乎后果集的条件。在这种状况下，长期表中的行与原始表中的行之间的关系将失落，而原始表中的行直到查问执行完结时才会被解锁。
  • SELECT ... LOCK IN SHARE MODE为所有搜寻过程中遇到的索引记录设置共享的next-key锁。然而，对于应用惟一索引锁定行以搜寻惟一行的语句，只设置索引记录锁。

  • SELECT ... FOR UPDATE为所有搜寻过程中遇到的索引记录设置独占的next-key锁。然而，对于应用惟一索引锁定行以搜寻惟一行的语句，只设置索引记录锁。

    对于在搜寻中遇到的索引记录，SELECT ... FOR UPDATE阻止其余会话进行SELECT ... LOCK IN SHARE MODE以及在某些事务隔离级别下的读取操作。一致性读取将疏忽读取视图中存在的记录上的锁。

  • UPDATE ... WHERE ...为搜寻时遇到的所有记录设置独占的next-key锁。然而，对于应用惟一索引锁定行以搜寻惟一行的语句，只设置索引记录锁。
  • 当UPDATE批改汇集索引记录时，将对受影响的主要索引记录进行隐式锁定。在插入新的辅助索引记录和插入新的辅助索引记录之前执行的反复查看扫描时，UPDATE操作还会对受影响的辅助索引记录设置共享锁。
  • DELETE FROM ... WHERE ...为搜寻时遇到的所有记录设置独占的next-key锁。然而，对于应用惟一索引锁定行以搜寻惟一行的语句，只设置索引记录锁。

  • INSERT对于插入行设置排他锁。这个锁是一个索引记录锁，而不是next-key锁（即没有间隙锁），不会阻止其余会话在插入行之前的间隙进行插入。

    在插入行之前，将设置一种称为插入动向间隙锁的间隙锁。这种锁批示多个事务插入雷同的索引间隙时，如果它们不在间隙中雷同的地位插入则不须要互相期待对方。假如有值为4和7的索引记录。不同的事务别离尝试插入值为5和6的的记录，每个事务在获取插入行的排他锁之前应用插入意向锁锁定4和7之前的间隙，但它们不会互相阻塞，因为要插入的行是不抵触的。

    如果产生键值反复的谬误，则在反复索引记录上设置共享锁。如果有多个会话试图插入同一行(如果另一个会话曾经具备独占锁)，此时共享锁的这种应用可能会导致死锁。如果另一个会话删除该行，可能产生上述这种状况。假如InnoDB表t1具备如下构造：

    CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

    当初假如3个会话按程序执行上面的操作：

    # Session 1:START TRANSACTION;INSERT INTO t1 VALUES(1);# Session 2:START TRANSACTION;INSERT INTO t1 VALUES(1);# Session 3:START TRANSACTION;INSERT INTO t1 VALUES(1);# Session 1:ROLLBACK;

    会话1的第一个操作获取行的独占锁。会话2和会话3的操作都会导致反复键谬误，它们都申请行的共享锁。当会话1回滚时，它会开释该行上的独占锁，并容许会话2和3的排队执行共享锁申请。此时，会话2和3死锁：因为互相持有共享锁，这两个会话都不能获取该行的排他锁。

    如果表曾经蕴含了键值为1的行且3个会话按程序执行如下操作时，会产生类似的状况：

    # Session 1:START TRANSACTION;DELETE FROM t1 WHERE i = 1;# Session 2:START TRANSACTION;INSERT INTO t1 VALUES(1);# Session 3:START TRANSACTION;INSERT INTO t1 VALUES(1);# Session 1:COMMIT;

    会话1的第一个操作获取行的独占锁。会话2和会话3的操作都会导致反复键谬误，它们都申请行的共享锁。当会话1提交时，它会开释该行上的独占锁，并容许会话2和3的排队执行共享锁申请。此时，会话2和3死锁：因为互相持有共享锁，这两个会话都不能获取该行的排他锁。

  • INSERT ... ON DUPLICATE KEY UPDATE与简略的INSERT不同，当产生键值反复的谬误时，将对要更新的行设置排他锁而不是共享锁。对于主键反复，采纳独占索引记录锁。对于惟一键值反复，采纳排他的next-key锁。
  • 如果在惟一键上没有抵触，REPLACE会像INSERT一样解决。除此之外，在被替换的行上将设置排他的next-key锁。（注：REPLACE是MySQL对SQL的扩大）

  • INSERT INTO T SELECT ... FROM S WHERE ...对于插入T表的每一行设置排他索引记录锁（没有间隙锁）。如果事务隔离级别为READ COMMITTED或者启用了innodb_locks_unsafe_for_binlog选项同时隔离级别不为SERIALIZABLE，InnoDB以一致性读的形式进行搜寻（无锁）。否则，InnoDB在S表中的行设置共享next-key锁。InnoDB必须在后一种状况下设置锁：在应用基于语句的二进制日志进行前滚复原期间，每个SQL语句必须以与最后执行的形式完全相同的形式执行。

    CREATE TABLE ... SELECT ...执行SELECT时设置共享next-key锁或者一致性读，与INSERT ... SELECT雷同。

    当在构造REPLACE INTO t SELECT ... FROM s WHERE ...或UPDATE t ... WHERE col IN (SELECT ... FROM s ...)中应用SELECT时，InnoDB对s表中的行设置共享next-key锁。

  • 当初始化表中事后定义为AUTO_INCREMENT的列时，InnoDB将在AUTO_INCREMENT列关联的索引尾部设置排他锁。在拜访主动递增计数器时，InnoDB应用特定的AUTO-INC表级锁模式，锁只继续到以后SQL语句完结，而不是整个事务完结。持有AUTO_INC表锁时，其余会话无奈向表中插入；见章节14.7.2 “InnoDB Transaction Model”。

    InnoDB在获取之前曾经初始化的AUTO_INCREMENT列的值时不设置任何锁。

  • 如果一个表上定义了一个外键束缚，任何须要查看约束条件的插入、更新或删除操作都会在要查看束缚的记录上设置共享的、记录级别的锁。InnoDB在束缚失败的状况下也会设置这些锁。

  • LOCK TABLE设置表锁，但这是在InnoDB层级之上的MySQL层级来设置这些锁。如果选项innodb_table_locks值为1(默认)并且autocommit = 0，InnoDB能够感知表锁，而且InnoDB之上的的MySQL层能感知行级锁的情况。

    否则，InnoDB的主动死锁检测无奈检测波及此类表锁的死锁。另外，因为在较高级别的MySQL层不晓得行级锁的状况，所以有可能在另一个会话以后曾经具备行级锁的表上取得表锁。然而这并不会危及事务的完整性，在章节14.7.5.2 “Deadlock Detection and Rollback”中进行了探讨。另见章节14.6.1.6 “Limits on InnoDB Tables”。

Phantom Rows

在同一个事务中，雷同的查问在不同的工夫产生不同的后果集，这就是所谓的幻读问题。例如，如果一个SELECT（语句）被执行两次，然而第二次返回了第一次没有返回的行，这行就被称为“幻影”行。

假如child表的id列上有一个索引，您心愿读取和锁定标识符值大于100的表中的所有行，以便在之后更新这些抉择的行中的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

查问从id大于100的第一个记录开始扫描索引。假如表蕴含id值为90和102的行。如果在扫描范畴内的索引记录上设置的锁没有对在间隙中进行插入的操作进行锁定(在本例中，90和102之间的间隙)，则另一个会话能够将一个新的id为101的行插入到表中。如果您在同一事务中执行雷同的SELECT，您将在查问返回的后果集中看到一个新的id为101的行(“幻影”)。如果咱们将一组行视为数据项，则新的幻影子项将违反事务的隔离准则，即事务运行时，它读取的数据在事务处理期间不会产生更改。

为了避免幻读，InnoDB应用了组合了索引记录锁和间隙锁的称为next-key锁的算法。InnoDB执行级锁定的形式是，当它搜寻或扫描表索引时，它会在遇到的索引记录上设置共享锁或排他锁。因而，行级锁实际上是索引记录锁。此外，索引记录上的next-key锁也会影响该索引记录之前的“间隙”。也就是说，next-key锁是索引记录锁加上索引记录之前的间隙上的间隙锁。如果一个会话领有索引中的记录R的共享或独占锁，则另一个会话不能按索引程序在R之前的间隙中插入新的索引记录。

当InnoDB扫描索引时，它还能够锁定索引中最初一个记录之后的间隙。在后面的示例中：为了避免任何id大于100的数据插入表中，InnoDB设置的锁蕴含了id值102之后的间隙上的锁。

您能够在应用程序中应用next-key锁定来实现唯一性查看：如果您在共享模式下读取数据，而没有看到将要插入的行有反复，则能够平安地插入行，并晓得在读取期间在读取行之后(的间隙)设置的next-key锁能够避免任何人同时插入与您的行雷同的内容。因而，next-key锁使您可能“锁定”表中不存在的货色。

间隙锁能够被显式禁用，这一点在章节14.7.1 “InnoDB Locking”中曾经阐明。这可能导致幻读问题，因为当间隙锁被禁用时其余会话能够在间隙中插入新的行。

Deadlocks in InnoDB

死锁是形容了这样一种状况，不同的事务因为各自持有另一个事务须要的锁而全副无奈继续执行。因为这两个事务都在期待资源变为可用状态，而他们同时不开释本身所持有的锁。

当多个事务以不同的程序锁定多个表中的行时（通过如UPDATE或SELECT ... FOR UPDATE之类的语句），可能会产生死锁。当这样的语句锁定索引记录和间隙时，每个事务可能会因为工夫问题只取得一部分锁，这样也可能会呈现死锁。对于死锁的样例，见章节14.7.5.1 “An InnoDB Deadlock Example”。

为了缩小死锁的可能性，应该多应用事务而不是锁表语句；尽量减少插入或更新数据事务的规模，使它们不会继续很长时间；当不同的事务更新多个表或大范畴的行时，在每个事务中应用雷同的操作程序执行语句（例如SELET ... FOR UPDATE）；在SELECT ... FOR UPDATE和UPDATE ... WHERE语句中可能应用到的列上创立索引；死锁的可能性不受隔离级别的影响，因为隔离级别扭转了读操作的行为，而死锁则是由写操作引起的。对于更多防止死锁、从死锁状态中复原的信息，见章节14.7.5.3 “How to Minimize and Handle Deadlocks”。

当启用死锁检测（默认）并产生死锁时，InnoDB将检测到该状况并回滚其中一个事务（受害者）。如果应用 innodb_deadlock_detect配置项敞开死锁检测，在死锁的状况下InnoDB通过innodb_lock_wait_timeout设置选项来回滚事务。因而，即便您的利用程序逻辑是正确的，您仍须要处理事务回滚重试的状况。若要查看InnoDB用户事务中最初的死锁状况，请应用SHOW ENGINE INNODB STATUS命令。如果事务构造或应用程序错误处理频繁引发死锁，则应在启用innodb_print_all_deadlocks选项的状况下运行，以便将所有死锁相干的信息打印到mysqld的谬误日志。对于更多死锁是如何被主动发现并解决的信息，见章节14.7.5.2 “Deadlock Detection and Rollback”。

An InnoDB Deadlock Example

上面的示例阐明了当锁申请引发死锁时谬误是如何产生的。这个例子波及两个用户，A和B。

首先，用户A创立了蕴含1行记录的表，而后开启事务。在事务执行过程中，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 MODE;+------+| i |+------+| 1 |+------+

而后，用户B开启了一个事务并尝试删除表中的该行：

mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> DELETE FROM t WHERE i = 1;

删除操作申请X锁。因为与用户A持有的S锁不兼容，获锁无奈被准许，因而该申请进入锁申请队列同时用户B被阻塞。

最初，用户A也尝试删除表中的该行：

mysql> DELETE FROM t WHERE i = 1;ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction

这里会产生死锁，因为用户A须要一个X锁来删除该行。然而，这个锁申请无奈被准许，因为客户端B曾经有了对X锁的申请，并且正在期待用户A开释其S锁。也不能将A持有的S锁降级为X锁，因为B当时申请X锁。因而，InnoDB为其中一个用户生成一个谬误并开释其锁。客户端返回谬误：

ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction

此时，能够准许另一个用户的锁申请，并将该行从表中删除。

Deadlock Detection and Rollback

启用死锁检测时（默认），InnoDB将自动检测事务死锁并回滚一个或多个事务以突破死锁。InnoDB尝试抉择绝对较小的事务进行回滚，而事务的大小取决于插入、更新或删除的行数。

当innodb_table_locks = 1（默认值）并且autocommit = 0，InnoDB能够感知到表锁，并且在它之上的MySQL层理解行级锁的信息。否则，InnoDB无奈检测到由MySQL锁表语句设置的表锁或由InnoDB以外的存储引擎设置的锁引发的死锁。通过设置innodb_lock_wait_timeout零碎变量的值来解决这些状况。

当InnoDB对一个事物执行齐全回滚时，该事务设置的所有锁都将被开释。然而，如果仅单个SQL语句因为谬误而引发回滚，则可能会保留该语句设置的一些锁。产生这种状况是因为依据InnoDB存储行锁的构造，在（谬误产生）之后无奈晓得哪个锁是由哪个语句设置的。

如果在事务中应用SELECT调用存储的函数，而函数过程中的某个语句失败，则该语句回滚。此外，如果在此之后执行ROLLBACK，则整个事务将回滚。

如果InnoDB监控输入的“LATEST DETECTED DEADLOCK”局部蕴含了以上面内容为结尾的信息，TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,，就表明在期待队列中的事务超过了下限200。一个期待队列中的事务数量超过200将以死锁解决，所有尝试查看进入队列的事务都将回滚。当一个申请锁的线程必须查看期待队列上的事务所领有的超过1000000个锁时，也可能产生雷同的谬误。

对于组织数据库操作防止死锁的技巧，详见章节14.7.5 “Deadlocks in InnoDB”。

Disabling Deadlock Detection

在高并发零碎中，当多个线程期待同一锁时，死锁检测会减慢处理速度。在这种状况下，禁用死锁检测并依附事务回滚中的innodb_lock_wait_timeout设置来解决产生的死锁或者会更加高效。死锁检测能够通过innodb_deadlock_detect配置项来禁用。

How to Minimize and Handle Deadlocks

本节基于章节14.7.5.2 “Deadlock Detection and Rollback”中无关死锁的概念信息。它解释了如何组织数据库操作以最小化死锁和应用程序中所需的后续错误处理。解释了如何组织数据库操作以缩小死锁以及后续应用程序中的错误处理。

死锁在事务性数据库中是一个典型的问题，但它们并不危险，除非它们太频繁，以至于您根本无法运行某些事务。通常状况下您须要编写应用程序来放弃当事务因为死锁而回滚时从新发动事务。

InnoDB应用主动行级锁。即便是只插入或删除一行的事务，也可能产生死锁。这是因为这些操作并不是真正的“原子”操作；它们会主动设置插入或删除行的（可能多个）索引记录的锁。

您能够应用以下技巧解决死锁并缩小其产生的可能性：

  • 任何时候都能够应用SHOW ENGINE INNODB STATUS命令来确定最近的死锁的起因。这能够帮忙您优化应用程序以防止死锁。
  • 如果频繁发现死锁正告，能够通过启用innodb_print_all_deadlocks配置选项获取更具体的调试信息。无关每个死锁的信息，不仅仅是最近的死锁，都记录在MySQL谬误日志中。调试实现后，请禁用此选项。
  • 总是事后筹备在事务因死锁而失败时从新发动事务。死锁并不危险，重试即可。
  • 放弃事务的简短，以减小其发生冲突的可能性。
  • 在进行一组相干更改之后立刻提交事务，使它们不容易发生冲突。特地是不要让交互式MySQL会话在有未提交事务的状况下关上很长时间。
  • 如果您须要应用锁定读（SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE），尝试应用较低的事务隔离级别，例如READ COMMITTED。
  • 当批改事务中的多个表或同一表中不同的行集时，每次都以雷同的程序执行这些操作。这样事务能够造成定义良好的队列，不会产生死锁。例如，将数据库操作组织到应用程序中的函数中，或者调用存储过程，而不是在不同地位编写多个类似的插入、更新和删除语句序列。
  • 尽可能较少应用锁。如果您可能答应SELECT从旧快照中返回数据，就不要为该语句增加FOR UPDATE或LOCK IN SHARE MODE后缀。这里应用READ COMMITTED隔离级别会比拟好，因为同一事务中的每个一致性读取都是从本人最新的快照中读取的。

  • 如果没有其余更好的方法，请应用表级锁序列化您的事务。对于具备事务性的表，如InnoDB表，应用LOCK TABLES的正确形式是以SET autocommit = 0（而不是START TRANSACTION）开始事务，接下来紧跟着LOCK TABLES语句，直到您显式提交事务后再调用UNLOCK TABLES。例如，如果您须要写入t1表并且从t2表读取数据，您能够这么做：

    SET autocommit=0;LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...COMMIT;UNLOCK TABLES;

    表级锁阻止对表的并发更新，以缩小零碎响应能力的代价来防止繁忙零碎中的死锁。

  • 序列化事务的另一种办法是创立一个仅蕴含一行的辅助“信号量”表。让每个事务在拜访其余表之前先更新该行。这样，所有事务都以串行形式进行。留神，InnoDB实时死锁检测算法在这种状况下也是无效的，因为序列化锁是行级锁。对于MySQL表级锁，必须应用办法超时来解决死锁。