前言

本篇译自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锁。

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

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

如果一个事务请求的锁与现有的锁兼容，那么请求将被允许，如果冲突则将被拒绝。事务将等待直到已经存在的不兼容的锁被释放。如果一个获锁请求与已存在的锁冲突，且可能会引发死锁，将会出现一个异常。

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

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

1
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监控的输出中：

1
2
3
4
5
6
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 gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: 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的行使用一个索引记录锁，而不会影响别的会话在前面的间隙中插入行：

1
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锁包含以下区间，其中圆括号表示对区间端点的排除，方括号表示包含端点：

1
2
3
4
5
(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监控的输出中：

1
2
3
4
5
6
7
8
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: 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记录之前的间隙锁：

1
2
3
4
5
6
7
8
9
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开始一个事务来向间隙中插入一条记录。当事务等待获取排他锁时使用了一个插入意向锁。

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

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

1
2
3
4
5
6
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 waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: 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将锁定记录或者等待锁释放。

    考虑下面的例子，以这张表为基础：

    1
    2
    3
    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操作：

    1
    2
    3
    # Session A
    START TRANSACTION;
    UPDATE t SET b = 5 WHERE b = 3;

    同时假设有另一个会话在此之后使用下面的语句执行UPDATE：

    1
    2
    # Session B
    UPDATE t SET b = 4 WHERE b = 2;

    当InnoDB执行这些UPDATE语句时，它首先为读取的每一行获取独占锁（X锁），然后决定是否修改它。如果InnoDB不修改行，它将释放锁。否则将保持锁直到事务结束。这将影响事务的处理，如下所示。

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

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

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

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

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

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

    但是，如果WHERE条件语句包含了索引列，同时InnoDB使用了索引，那么获取和释放记录锁只和索引列相关。在下面的例子中，第一个UPDATE语句获取并保持b=2的行的独占锁。第二个UPDATE将在它获取相同记录的独占锁时阻塞，因为它也使用了b列定义的索引。

    1
    2
    3
    4
    5
    6
    7
    8
    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 A
    START TRANSACTION;
    UPDATE t SET b = 3 WHERE b = 2 AND c = 3;
    # Session B
    UPDATE 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结束。下面的例子显示了两个事务。第一个提交，而第二个回滚。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
shell> mysql test
mysql> 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语句能够影响这些刚提交的行，即使会话无法查询它们。如果一个事务尝试更新或删除被不同的事务更改提交的记录，那么这些更改将对当前事务可见。例如，您可能会遇到类似下面的情况：

1
2
3
4
5
6
7
8
9
10
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提交后增加。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
		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隔离级别或者锁定读：

1
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表中的行。

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

如果要锁定t2表中的行，需要给子查询添加锁定后缀：

1
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：

1
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来对计数器执行锁定读，然后增加计数器。例如：

1
2
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中，生成唯一标识的特定任务只需要访问表一次就可以完成：

1
2
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具有如下结构：

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

    现在假设3个会话按顺序执行下面的操作：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # 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个会话按顺序执行如下操作时，会发生相似的情况：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # 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的表中的所有行，以便在之后更新这些选择的行中的某些列：

1
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锁：

1
2
3
4
5
6
7
8
9
10
11
12
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开启了一个事务并尝试删除表中的该行：

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

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

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

1
2
3
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为其中一个用户生成一个错误并释放其锁。客户端返回错误：

1
2
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表读取数据，您可以这么做：

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

    表级锁阻止对表的并发更新，以减少系统响应能力的代价来避免忙碌系统中的死锁。

  • 序列化事务的另一种方法是创建一个仅包含一行的辅助“信号量”表。让每个事务在访问其他表之前先更新该行。这样，所有事务都以串行方式进行。注意，InnoDB实时死锁检测算法在这种情况下也是有效的，因为序列化锁是行级锁。对于MySQL表级锁，必须使用方法超时来解决死锁。