参考官方文档：
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html

14.5.1 InnoDB 锁

共享和排他锁

InnoDB在存在两种类型的锁，共享（S）锁和独占（X）锁的情况下实现标准行级锁定。

共享（S）锁允许持有锁的事务读取一行。
独占（X）锁允许持有锁的事务更新或删除一行。

如果事务T1持有行r上的共享（S）锁，那么来自某个不同事务T2的对行r上的锁的请求按如下方式处理：

可以立即授予T2对S锁的请求。结果，T1和T2在r上都持有一个S锁。
T2对X锁的请求不能立即授予。

如果事务T1在行r上持有排他（X）锁，则不能立即授予来自某个不同事务T2的对r上任一类型的锁的请求。相反，事务T2必须等待事务T1释放其对行r的锁定。

意向锁

InnoDB支持多个粒度锁定，允许行锁和表锁共存。例如，诸如LOCK TABLES ... WRITE之类的语句在指定的表上执行独占锁（X锁）。为了在多个粒度级别实现锁定，InnoDB使用意向锁。意向锁是表级锁，用于指示事务对表中某一行的事务需要哪种类型的锁（共享或排他）。有两种类型的意向锁：
意向共享锁（IS）表示事务意图在表中的单个行上设置共享锁。
意向排他锁（IX）表明事务意图在表中的单个行上设置独占锁。

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

意向锁定协议如下：

在事务可以获取表中某一行的共享锁之前，它在表上必须首先获得一个IS锁或更强的锁。
在一个事务可以获得一个表中的一行独占锁之前，它必须首先获取表上的一个IX锁。

表级锁定类型的兼容性总结在下面的矩阵中。

如果请求事务与现有的锁定兼容，则授予锁定，但如果它与现有的锁定冲突，则该锁定不会被授予。事务一直等到冲突的现有锁被释放。如果锁定请求与现有的锁冲突并且因为会导致死锁而无法被授予，则会发生错误。

意向锁不会阻止除完整表请求之外的任何内容（例如，LOCK TABLES ... WRITE）。意向锁的主要目的是显示某人正在锁定一行，或者要锁定表中的一行。

意向锁定的事务数据在SHOW ENGINE INNODB STATUS和InnoDB监视器输出中显示类似于以下内容：

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

记录锁定

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

即使表没有索引定义，记录锁始终锁定索引记录。对于这种情况，InnoDB创建一个隐藏的聚集索引并使用这个索引进行记录锁定。

记录锁定的事务数据在SHOW ENGINE INNODB STATUS和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 ;;

间隙锁

间隙锁定是索引记录之间的间隙的锁定，或者是第一个索引记录之前或最后一个索引记录之后的间隙锁定。例如，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可以在间隔上保存共享间隙锁（间隙S锁），而事务B在同一间隙上保留独占间隙锁（间隙X锁）。允许冲突间隙锁定的原因是，如果从索引中清除记录，则必须合并由不同事务记录保存的间隙锁定。

InnoDB中的间隙锁是“纯粹的抑制性”，这意味着它们只会阻止其他事务插入到间隙中。它们不会阻止不同的事务在同一个间隙上进行间隙锁定。因此，间隙X锁具有与间隙S锁相同的效果。

可以显式禁用间隙锁定。如果将事务隔离级别更改为READ COMMITTED或启用innodb_locks_unsafe_for_binlog系统变量（现在已弃用），则会发生这种情况。在这些情况下，对搜索和索引扫描禁用间隙锁定，仅用于外键约束检查和重复键检查。

使用READ COMMITTED隔离级别或启用innodb_locks_unsafe_for_binlog还有其他影响。在MySQL评估了WHERE条件后，释放不匹配行的记录锁。对于UPDATE语句，InnoDB执行“半连续”读取，以便将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。

Next-Key锁

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

InnoDB以这样的方式执行行级锁定，即当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排它锁。因此，行级锁实际上是索引记录锁。索引记录上的 next-key 锁定还会影响该索引记录之前的“间隙”。也就是说， next-key锁定是索引记录锁定，并在索引记录之前的间隔上加上间隙锁定。如果一个会话对索引中的记录R具有共享或独占锁定，则另一个会话不能在索引顺序中的R之前的间隙中插入新的索引记录。

假设索引包含值10,11,13和20.此索引的可能下一个键锁定涵盖以下区间，其中圆括号表示排除区间端点，方括号表示包含端点：

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

对于最后一个间隔，next-key锁定将索引中最大值以上的间隔锁定，并且“上游”伪记录的值高于实际在索引中的任何值。上确界不是真实的索引记录，因此，实际上，这个next-key锁只锁定最大索引值之后的间隔。

默认情况下，InnoDB以REPEATABLE READ事务隔离级别运行。在这种情况下，InnoDB使用next-key锁进行搜索和索引扫描，这可以防止幻像行

next-key 锁的事务数据在SHOW ENGINE INNODB STATUS和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操作设置的一种间隙锁定。这个锁定以插入到相同索引间隙中的多个事务不需要等待对方的方式插入的意图，如果它们没有插入间隙中的相同位置。假设有索引记录的值为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);

插入意图锁定的事务数据在SHOW ENGINE INNODB STATUS和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 锁
AUTO-INC锁是一个特殊的表级锁，由事务插入带有AUTO_INCREMENT列的表中。在最简单的情况下，如果一个事务将值插入到表中，则任何其他事务都必须等待对该表执行自己的插入操作，以便第一个事务插入的行接收连续的主键值。

innodb_autoinc_lock_mode配置选项控制用于自动增量锁定的算法。它允许您选择如何在可预测的自动递增值序列和插入操作的最大并发之间进行权衡。（默认是1，获得到值之后就释放auto-inc锁）

空间索引的谓词锁定

为了处理涉及SPATIAL索引的操作的锁定， next-key锁定不能很好地支持REPEATABLE READ或SERIALIZABLE事务隔离级别。多维数据中没有绝对的排序概念，因此不清楚哪个是“ next”键。

为了支持具有SPATIAL索引的表的隔离级别，InnoDB使用谓词锁。 SPATIAL索引包含最小边界矩形（MBR）值，因此InnoDB通过对用于查询的MBR值设置谓词锁定来强制执行对索引的一致读取。其他事务不能插入或修改与查询条件匹配的行。

14.5.2 InnoDB 事务模型

在InnoDB事务模型中，目标是将多版本数据库的最佳属性与传统的两阶段锁定相结合。默认情况下，InnoDB在行级别执行锁定，并将查询作为非锁定一致读取运行，以Oracle的风格。 InnoDB中的锁定信息以节省空间的方式存储，因此不需要锁定升级。通常，允许多个用户锁定InnoDB表中的每一行或任何行的随机子集，而不会导致InnoDB内存耗尽。（锁的信息存放在内存中）

14.5.2.1 事务隔离级别

事务隔离是数据库处理的基础之一。隔离是I的首字母缩写ACID; 隔离级别是在多个事务进行更改并同时执行查询时，对性能和可靠性之间的平衡。

InnoDB 提供了所有的隔离级别： : READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, 和 SERIALIZABLE。InnoDB的隔离级别是 REPEATABLE READ。

用户可以使用SET TRANSACTION语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，请在命令行或选项文件中使用--transaction-isolation选项。

在批量报告等情况下，可以放宽一致性规则，即READ COMMITTED或READ UNCOMMITTED，其中精确一致性和可重复结果不如减少锁定开销那么重要。 SERIALIZABLE强制实施比REPEATABLE READ更严格的规则，主要用于特殊情况下，例如XA事务以及排除并发和死锁问题。

以下描述了MySQL如何支持不同的事务级别。该列表从最常用的级别到最少使用的级别。

REPEATABLE READ

这是InnoDB的默认隔离级别。在同一事务中的一致读取读取第一次读取建立的快照。这意味着如果您在同一事务中发出多个简单（非锁定）SELECT语句，则这些SELECT语句在彼此之间也是一致的。

对于锁定读取（SELECT FOR WITH UPDATE或LOCK IN SHARE MODE），UPDATE和DELETE语句，锁定取决于语句是使用唯一索引的具有唯一搜索条件还是范围类型搜索条件。

对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不是锁定之前的缺口。
对于其他搜索条件，InnoDB的锁定扫描的索引范围，使用间隙锁或next-key锁来阻止其他会话插入到范围所覆盖的间隙中。

READ COMMITTED

(oracle 的默认隔离级别)
即使在同一个事务中，每次一致的读取都会设置并读取其自己的新快照。

对于锁定读取（SELECT FOR WITH UPDATE或LOCK IN SHARE MODE），UPDATE语句和DELETE语句，InnoDB只锁定索引记录，而锁定它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。

由于禁用了间隙锁定，所以可能会出现幻影读问题，因为其他会话可以将新行插入到间隙中。

如果使用READ COMMITTED，则必须使用基于行的二进制日志记录。

使用READ COMMITTED有附加效果：

对于UPDATE或DELETE语句，InnoDB仅为更新或删除的行持有锁。在MySQL评估了WHERE条件后，释放不匹配行的记录锁。这大大降低了死锁的可能性，但它们仍然可能发生。
对于UPDATE语句，如果一行已被锁定，InnoDB执行“半连续”读取，将最新的提交版本返回给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;

在这种情况下，该表没有索引，因此搜索和索引扫描使用隐藏的聚簇索引而不是索引列进行记录锁定。

假设一个会话使用这些语句执行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时，它首先获取它读取的每一行的排它锁，然后确定是否修改它。如果InnoDB不修改该行，它将释放该锁。否则，InnoDB将保留该锁直到事务结束。这会影响事务处理，如下所示

当使用默认的REPEATABLE READ隔离级别时，第一个UPDATE会在它读取的每一行上获取一个x锁，并且不会释放它们中的任何一行：

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锁，并为其未修改的行释放它们：

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)

对于第二次更新，InnoDB执行“半连续”读取，将读取的每一行的最新提交版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配：

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

但是，如果WHERE条件包含索引列，并且InnoDB使用索引，则在获取和保留记录锁时仅考虑索引列。在下面的示例中，第一个UPDATE在b = 2的每一行上都会获取并保留一个x锁。第二个UPDATE在尝试获取相同记录上的x锁时阻塞，因为它也使用在列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语句以非锁定方式执行，但可能会使用一个可能的早期版本的行。因此，使用这种隔离级别，这样的读取不一致。这也被称为脏读。否则，这个隔离级别就像READ COMMITTED一样。

SERIALIZABLE

此级别与REPEATABLE READ类似，但如果自动提交被禁用，则InnoDB会将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE。如果启用自动提交，则SELECT是它自己的事务。因此，它被认为是只读的，并且如果作为一致（非锁定）读取执行，则可以被序列化，并且不需要阻塞其他事务。（如果其他事务已修改选定的行，要强制阻止普通SELECT，请禁用自动提交。）

14.5.2.2 autocommit，commit 和 rollback

在InnoDB中，所有的用户活动都发生在一个事务中。如果启用自动提交模式，则每个SQL语句将自行形成一个事务。默认情况下，MySQL为启用自动提交的每个新连接启动会话，因此如果该语句没有返回错误，则MySQL会在每个SQL语句后执行提交。如果语句返回错误，则提交或回滚行为取决于错误。

启用自动提交的会话可以通过以明确的START TRANSACTION或BEGIN语句启动事务，并使用COMMIT或ROLLBACK语句结束它来执行多语句事务。

如果在SET autocommit = 0的会话中禁用自动提交模式，则会话始终打开一个事务。 COMMIT或ROLLBACK语句结束当前事务并开始一个新事务。

如果具有禁用自动提交功能的会话在未显式提交最终事务的情况下结束，则MySQL会回滚该事务

一些语句隐式地结束一个事务，就好像你在执行语句之前做了一个COMMIT一样。

COMMIT意味着在当前交易中所做的更改将永久化，并对其他会话可见。另一方面，ROLLBACK语句会取消当前事务所做的所有修改。 COMMIT和ROLLBACK都释放当前事务期间设置的所有InnoDB锁定。

使用事务进行一组DML操作

默认情况下，与MySQL服务器的连接从启用自动提交模式开始，在执行时自动提交每条SQL语句。如果您对其他数据库系统有经验，那么这种操作模式可能并不熟悉，标准做法是发出一系列DML语句并提交它们或将它们一起回滚。

要使用多语句事务，请使用SQL语句SET autocommit = 0关闭自动提交，并根据需要使用COMMIT或ROLLBACK结束每个事务。要开启自动提交，请使用START TRANSACTION开始每个事务并使用COMMIT或ROLLBACK结束它。

在客户端语言端的事务

在诸如PHP，Perl DBI，JDBC，ODBC或MySQL的标准C调用接口之类的API中，您可以像使用其他SQL语句（如SELECT或INSERT）一样将事务控制语句（如COMMIT）作为字符串发送到MySQL服务器。一些API还提供单独的特殊事务提交和回滚函数或方法。

14.5.2.3 一致性非锁定读

一致的读取意味着InnoDB使用多版本化在某个时间点向查询呈现数据库的快照。该查询可以查看在该时间点之前提交的事务所做的更改，以及以后或未提交的事务所做的非更改。此规则的例外是该查询查看同一事务中较早的语句所做的更改。此异常会导致以下不规则：如果您更新表中的某些行，则SELECT会看到更新行的最新版本，但它也可能会看到任何行的旧版本。如果其他会话同时更新同一个表，异常意味着您可能会看到该表处于数据库中从未存在的状态。

（允许可重复读）

如果事务隔离级别为REPEATABLE READ（默认级别），则同一事务内的所有一致读取将读取该事务中第一次读取所创建的快照。您可以通过提交当前事务并在发出新查询之后为您的查询获得更新鲜的快照。

通过READ COMMITTED隔离级别，事务中的每个一致读取都会设置并读取其自己的新快照。

一致的读取是InnoDB在READ COMMITTED和REPEATABLE READ隔离级别中处理SELECT语句的默认模式。一致的读取不会在它访问的表上设置任何锁，因此其他会话可以自由修改这些表，同时在表上执行一致的读取。

假设您正在运行默认的REPEATABLE READ隔离级别。当您发出一致的读取（即普通的SELECT语句）时，InnoDB会为您的事务提供一个依据查询的查看数据库的时间点。如果另一个事务删除了一行并在分配了时间点后提交，则不会看到该行已被删除。插入和更新的处理方式相似。

注意：

数据库状态的快照适用于事务中的SELECT语句，而不一定适用于DML语句。如果插入或修改某些行然后提交该事务，则从另一个并发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或START TRANSACTION WITH CONSISTENT SNAPSHOT。

这称为多版本并发控制。

在下面的例子中，只有当B提交了插入并且A已经提交时，会话A才看到由B插入的行，这样时间点推进到B的提交。

如果要查看数据库的“最新”状态，请使用READ COMMITTED隔离级别或锁定读取：

SELECT * FROM t FOR SHARE;

通过READ COMMITTED隔离级别，事务中的每个一致读取都会设置并读取其自己的新快照。在LOCK IN SHARE MODE中，会发生锁定读取：SELECT会阻塞，直到包含最新行的事务结束（请参见第14.5.2.4节“锁定读取”）。

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

一致性读取不能在DROP TABLE上工作，因为MySQL不能使用已经被删除的表并且InnoDB摧毁该表。
一致的读取操作不能在ALTER TABLE上工作，因为该语句会生成原始表的临时副本，并在构建临时副本时删除原始表。在事务中重新发布一致的读取时，新表中的行不可见，因为在执行事务快照时，这些行不存在。在这种情况下，事务返回错误：ER_TABLE_DEF_CHANGED，“表定义已更改，请重试事务”。

读取的类型因INSERT INTO ... SELECT，UPDATE ...（SELECT）和CREATE TABLE ...等子句中的选择而异，但不指定FOR UPDATE或LOCK IN SHARE MODE：

默认情况下，InnoDB使用更强大的锁，SELECT部分的行为类似于READ COMMITTED，即使在同一事务中，每个一致的读取也会设置并读取其自己的新快照。

要在这种情况下使用一致读取，请启用innodb_locks_unsafe_for_binlog选项并将事务的隔离级别设置为READ UNCOMMITTED，READ COMMITTED或REPEATABLE READ（即SERIALIZABLE以外的任何其他模式）。在这种情况下，从所选表格读取的行上不设置锁定。

14.5.2.4 锁定读取

如果您查询数据，然后在同一事务中插入或更新相关数据，则常规SELECT语句不会提供足够的保护。其他事务可以更新或删除刚才查询的相同行。 InnoDB支持两种类型的锁定读取，提供额外的安全性：

SELECT ... LOCK IN SHARE MODE

在读取的任何行上设置共享模式锁定。其他会话可以读取行，但在您的事务提交之前无法修改它们。如果这些行中的任何一行已被另一个尚未提交的事务更改，那么您的查询将一直等到该事务结束，然后使用最新值。

SELECT ... FOR UPDATE

对于搜索遇到的索引记录，锁定行和任何关联的索引条目，就像为这些行发布UPDATE语句一样。其他事务被阻止更新这些行，从执行SELECT ... LOCK IN SHARE MODE或者通过读取特定事务隔离级别的数据。一致的读取忽略读取视图中存在的记录上设置的任何锁定。（记录的旧版本不能被锁定;它们通过在记录的内存副本上应用撤消日志来重建。）

这些子句在处理树形结构或图形结构化数据时非常有用，无论是在单个表中还是在多个表中拆分。您可以将边缘或树枝从一处移到另一处，同时保留返回和更改这些“指针”值。

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

这些子句在处理树形结构或图形结构化数据时非常有用，无论是在单个表中还是在多个表中拆分。您可以将边或树枝从一处移到另一处，同时保留返回并更改这些“指针”值的权利。

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

注意：

使用SELECT FOR UPDATE锁定行以进行更新仅适用于禁用自动提交时（通过使用START TRANSACTION开始事务或将自动提交设置为0）。如果启用自动提交，则不会锁定与规范匹配的行。

锁定读的例子

假设你想插入一个新的行到一个子表中，并确保子行在父表中有一个父行。您的应用程序代码可以确保贯穿这一系列操作的参照完整性。

首先，使用一致的读取来查询表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语句仅检索标识符信息（特定于当前连接）。它不访问任何表。

14.5.3 在InnoDB中通过不同的SQL语句设置锁

锁定读取，UPDATE或DELETE通常会在处理SQL语句时扫描的每个索引记录上设置记录锁定。在语句中是否存在WHERE条件将排除该行并不重要。 InnoDB不记得确切的WHERE条件，但只知道扫描了哪些索引范围。这些锁通常是next-key锁，它们也会在记录之前立即阻止插入到“间隙”中。但是，可以显式禁用间隙锁定，这会导致不使用next-key 锁。有关更多信息，请参见第14.5.1节“InnoDB锁定”。事务隔离级别还可以影响设置了哪些锁; 请参见第14.5.2.1节“事务隔离级别”。

如果在搜索中使用二级索引，并且集合的索引记录锁是独占的，则InnoDB也会检索相应的聚集索引记录并在其上设置锁。

如果您没有适合您的语句的索引，并且MySQL必须扫描整个表以处理语句，则表的每一行都会被锁定，从而阻止其他用户的所有插入操作到表中。创建好的索引非常重要，以便您的查询不会不必要地扫描多行。

对于SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE，获取已扫描行的锁，并且预计将释放不符合结果集的行（例如，如果它们不符合在WHERE子句中给出的标准）。但是，在某些情况下，行可能不会立即解锁，因为在执行查询期间，结果行与其原始源之间的关系会丢失。例如，在UNION中，可以在评估之前将来自表的扫描（和锁定）行插入临时表中，以确定它们是否符合结果集。在这种情况下，临时表中的行与原始表中的行之间的关系会丢失，并且后面的行在查询执行结束之前不会解锁。

InnoDB按如下设置特定类型的锁。

SELECT ... FROM是一致的读取，读取数据库的快照并设置不锁定，除非事务隔离级别设置为SERIALIZABLE。对于SERIALIZABLE级别，搜索会在遇到的索引记录上设置共享的next key 锁。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁定。
SELECT ... FROM ... LOCK IN SHARE MODE 中锁定设置搜索遇到的所有索引记录上共享的next-key锁定。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁定。
SELECT ... FROM ... FOR UPDATE为搜索遇到的每个记录设置独占的下一个键锁定。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁定。

对于搜索遇到的索引记录，SELECT ... FROM ... FOR UPDATE阻止其他会话执行SELECT ... FROM ... 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;

现在假设三个会话按顺序执行以下操作：

会话1：

START TRANSACTION;

INSERT INTO t1 VALUES(1);

会话2：

START TRANSACTION;

INSERT INTO t1 VALUES(1);

会话3：

START TRANSACTION;

INSERT INTO t1 VALUES(1);

会话1：

ROLLBACK;

会话1的第一个操作获取该行的排它锁。会话2和3的操作都会导致重复键错误，并且它们都会为该行请求共享锁。当会话1回滚时，它将释放对该行的排它锁，并为会话2和3授予排队的共享锁请求。此时，会话2和3发生死锁：因为另一方持有共享锁，所以都不能获得该行的排它锁。

如果表中已经包含具有键值1的行并且三个会话按顺序执行以下操作，则会出现类似的情况：

会话1：

START TRANSACTION;

DELETE FROM t1 WHERE i = 1;

会话2：

START TRANSACTION;

INSERT INTO t1 VALUES(1);

会话3：

START TRANSACTION;

INSERT INTO t1 VALUES(1);

会话1：

commit；

会话1的第一个操作获取该行的排它锁。会话2和3的操作都会导致重复键错误，并且它们都会为该行请求共享锁。当会话1提交时，它将释放对该行的排它锁，并为会话2和3授予排队的共享锁请求。此时，会话2和3发生死锁：因为另一方持有共享锁，所以都不能获得该行的排它锁。

INSERT ... ON DUPLICATE KEY UPDATE与简单的INSERT不同之处在于，当发生重复键错误时，将在要更新的行上的放置排它锁而不是共享锁。对重复的主键值采取独占索引记录锁定。独占的next-key锁定用于重复的唯一键值。

如果在唯一键上没有碰撞，则REPLACE就像INSERT一样完成。否则，独占的next-key锁被放置在要被替换的行上。
INSERT INTO T SELECT ... FROM S WHERE ...为插入T的每一行设置排它索引记录锁（不带间隙锁）。如果事务隔离级别为READ COMMITTED，或启用innodb_locks_unsafe_for_binlog并且事务隔离级别不是SERIALIZABLE，InnoDB在S上执行搜索作为一致读（无锁）。否则，InnoDB将在来自S的行上设置共享的下一个键锁定。InnoDB必须在后一种情况下设置锁定：在前滚恢复期间，每个SQL语句都必须以与原来完全相同的方式

CREATE TABLE ... SELECT ...使用共享的next-key锁定或作为一致读取执行SELECT，如同INSERT ... SELECT。

当在，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表锁定被保持时，其他会话不能插入到表中;

InnoDB获取先前初始化的AUTO_INCREMENT列的值，而不设置任何锁。

如果在表上定义了FOREIGN KEY约束，则需要检查约束条件的任何插入，更新或删除都会在它检查约束的记录上设置共享记录级锁。在约束失败的情况下，InnoDB也会设置这些锁。

LOCK TABLES设置表锁，但它是InnoDB层之上设置这些锁的较高MySQL层。如果innodb_table_locks = 1（缺省值）和autocommit = 0，InnoDB知道表锁，InnoDB上面的MySQL层知道行级锁。

否则，InnoDB的自动死锁检测无法检测涉及这种表锁的死锁。另外，因为在这种情况下，较高的MySQL层不知道行级别锁定，所以可以在另一个会话当前具有行级别锁定的表上获得表锁定。

14.5.4 幻影行

当相同的查询在不同时间产生不同组的行时，所谓的幻影问题发生在事务中。例如，如果SELECT执行了两次，但是第二次返回的是第一次未返回的行，则该行是“幻像”行。

假设在子表的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 locking的算法，将索引行锁定与间隙锁定相结合。 InnoDB以这样的方式执行行级锁定，即当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排它锁。因此，行级锁实际上是索引记录锁。另外，索引记录上的next-key锁也会影响该索引记录之前的“间隙”。也就是说，next-key锁定是索引记录锁定，并在索引记录之前的间隔上加上间隙锁定。如果一个会话对索引中的记录R具有共享或独占锁定，则另一个会话不能在索引顺序中的R之前的间隙中插入新的索引记录。

当InnoDB扫描索引时，它也可以锁定索引中最后一条记录后的间隔。在前面的例子中就是这样：为了防止任何插入到ID大于100的表中，由InnoDB设置在id值102之后的间隙中包含锁。

您可以使用 next-key 锁定在您的应用程序中实现唯一性检查：如果您以共享模式读取数据，并且没有看到要插入的行的重复项，那么您可以安全地插入行并知道在读取过程中设置在行的后继键上的下一个键锁可以防止任何人同时插入行的副本。因此，next-key锁定使您能够“锁定”表中不存在的东西。

可以禁用间隙锁定，如第14.5.1节“InnoDB锁定”中所述。这可能会导致幻影问题，因为其他会话可以在禁用间隙锁定时将新行插入到间隙中。

14.5.5 InnoDB 中的死锁

死锁是由于每个事务持有另一个事务需要的锁而导致不同事务无法继续的情况。因为两个事务都在等待资源变为可用，所以既不释放它拥有的锁。

当事务锁定多个表中的行时（通过诸如UPDATE或SELECT ... FOR UPDATE之类的语句），可能会发生死锁，但顺序相反。当这些语句锁定索引记录和间隔的范围时，也会发生死锁，每个事务不是由于时间问题而获取一些锁。

为了减少死锁的可能性，使用事务而不是LOCK TABLES语句; 保持插入或更新数据的事务足够小以至于不能长时间保持打开状态; 当不同的事务更新多个表或大范围的行时，请在每个事务中使用相同的操作顺序（例如SELECT ... FOR UPDATE）; 在SELECT ... FOR UPDATE和UPDATE ... WHERE语句中使用的列上创建索引。死锁的可能性不受隔离级别的影响，因为隔离级别改变了读操作的行为，而死锁因为写操作而发生。

当启用死锁检测（默认）并发生死锁时，InnoDB会检测到该情况并回滚其中一个事务（受害者）。如果使用innodb_deadlock_detect配置选项禁用死锁检测，InnoDB将依靠innodb_lock_wait_timeout设置来回滚发生死锁的事务。因此，即使您的应用程序逻辑正确，您仍然必须处理必须重试事务的情况。要查看InnoDB用户事务中的最后一个死锁，请使用SHOW ENGINE INNODB STATUS命令。如果频繁的死锁突出显示事务结构或应用程序错误处理的问题，请在启用innodb_print_all_deadlocks设置的情况下运行，以将有关所有死锁的信息打印到mysqld错误日志中。

14.5.5.1 一个InnoDB 死锁的例子

以下示例说明了锁请求会导致死锁时如何发生错误。这个例子涉及两个客户端，A和B.

首先，客户端A创建一个包含一行的表，然后开始一个事务。在事务中，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锁。由于B先前请求X锁，因此A持有的S锁也不能升级为X锁。结果，InnoDB为其中一个客户端生成一个错误并释放它的锁。客户返回这个错误：

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

try restarting transaction

此时，可以授予其他客户端的锁定请求，并从表中删除该行。

14.5.5.2 死锁检测和回滚

当启用死锁检测时（默认），InnoDB会自动检测事务死锁并回滚一个或多个事务来打破死锁。 InnoDB会尝试选择小型事务进行回滚，其中事务的大小由插入，更新或删除的行数决定。

如果innodb_table_locks = 1（缺省值）和autocommit = 0，InnoDB感知表锁，并且它上面的MySQL层感知行级锁。否则，InnoDB无法检测由MySQL LOCK TABLES语句设置的表锁或由InnoDB以外的存储引擎设置的锁所在的死锁。通过设置innodb_lock_wait_timeout系统变量的值来解决这些情况。

当InnoDB执行事务的完整回滚时，将释放事务设置的所有锁。但是，如果只有一条SQL语句由于错误而回滚，则该语句设置的某些锁可能会被保留。发生这种情况是因为InnoDB以一种格式存储行锁，以致于它无法知道哪个锁由哪个语句设置。

如果SELECT在事务中调用存储的函数，并且函数中的语句失败，那么该语句将回滚。此外，如果在此之后执行ROLLBACK，则整个事务回滚。

如果InnoDB Monitor输出的最新检测到的DEADLOCK部分包含一条消息，指出“ TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION”，这表示等待中的事务数量列表已达到200的限制。超过200个

InnoDB使用自动行级锁定。即使在插入或删除单行的事务中，也可能会出现死锁。那是因为这些行动并非真正的“原子”。他们自动在插入或删除的行的索引记录（可能有几个）上设置锁定。

您可以通过以下技术来应对死锁并减少它们发生的可能性：

在任何时候，发出SHOW ENGINE INNODB STATUS命令来确定最近发生死锁的原因。这可以帮助您调整应用程序以避免死锁。
如果经常出现死锁警告，请通过启用innodb_print_all_deadlocks配置选项来收集更多的调试信息。关于每个死锁的信息，不仅仅是最新的，记录在MySQL错误日志中。完成调试后禁用此选项。
如果事务由于死锁而失败，请始终准备重新发出事务。死锁并不危险。再试一次。
保持事务持续时间较短，以减少冲突。
在进行一系列相关更改后立即提交事务，以减少冲突。特别是，不要让交互式mysql会话在未提交事务的情况下长时间处于打开状态。
如果使用锁定读取（SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如READ COMMITTED。
在事务中修改多个表或同一表中的不同行时，每次都要以一致的顺序执行这些操作。然后事务形成明确定义的队列并且不会发生死锁。例如，将数据库操作组织到应用程序中的函数中，或调用存储例程，而不是在不同位置编码多个相似的INSERT，UPDATE和DELETE语句序列。
将合适的索引添加到表中。然后，您的查询需要扫描更少的索引记录，从而设置更少的锁。使用EXPLAIN SELECT来确定MySQL服务器认为哪些索引最适合您的查询。
使用更少的锁定。如果您可以承受允许SELECT从旧快照返回数据，请不要将FOR UPDATE或LOCK IN SHARE MODE子句添加到它。在这里使用READ COMMITTED隔离级别是很好的，因为在同一事务中每次一致的读取都会从它自己的新快照中读取。
如果没有其他帮助，则使用表级锁序列化您的事务。将LOCK TABLES与事务表（例如InnoDB表）一起使用的正确方法是，使用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表级锁定时，必须使用超时方法来解决死锁问题。

ITPub博客

14.5 InnoDB 锁和事务模型

最新文章