关于mysql:InnoDB锁和事务模型

42次阅读

共计 24606 个字符，预计需要花费 62 分钟才能阅读完成。

原始博文链接

前言

本篇译自 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 监控的输入中：

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 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 的行应用一个索引记录锁，而不会影响别的会话在后面的间隙中插入行：

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 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 记录之前的间隙锁：

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 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 将锁定记录或者期待锁开释。

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

    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 A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

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

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

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

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

    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 提交或者回滚：

    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-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 列定义的索引。

    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 完结。上面的例子显示了两个事务。第一个提交，而第二个回滚。

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 语句可能影响这些刚提交的行，即便会话无奈查问它们。如果一个事务尝试更新或删除被不同的事务更改提交的记录，那么这些更改将对以后事务可见。例如，您可能会遇到相似上面的状况：

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 表级锁，必须应用办法超时来解决死锁。

正文完
mysql
发表至： mysql
2023-02-28
 0
关于mysql:MySql-踩坑小知识集散地
关于mysql:ISSS615-Data-Management
关于mysql:Mysql报错message-from-server-is-blocked-because-of-many
关于mysql:Mysql数据实时增量同步工具之gomysqltransfer