MySQL锁分类
@
本文对InnoDB的锁及sql语句会用到的锁做一个详细讲解
锁分类
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
共享锁和互斥锁
InnoDB 实现了标准的行级锁，分为共享锁和互斥锁。共享锁也叫 S （shared）锁，互斥锁也叫X（exclusive）锁。共享锁允许持有该锁的事务读取一行，互斥锁允许持有锁的事务更新或删除行。如果事务 T1 在行 R1 上持有共享锁 S，假设另外一个不同的事务 T2 对行 R1 发起锁请求，处理过程如下
- T2 对 S 锁的请求可以立即通过，于是 T1 和 T2 同时共享锁 S
- T2 对 互斥锁 X 的请求不会马上通过，T2 必须等待
如果事务 T1 在行 R 上持有互斥锁，则无法立即通过来自某个不同事务 T2 对 R 上任一类型的锁的请求。 相反，事务 T2 必须等待事务 T1 释放其在行 R 上的锁
意向锁 Intension Lock
InnoDB 支持多种粒度的锁，并且允许行锁和表锁共存。例如LOCK TABLES ... WRITE对表加上互斥锁。
InnoDB为了实现多粒度级别的锁共存，出现了意向锁。它是一种表级锁，用于指示事务打算即将在表中的行上获取的锁类型。有两种意向锁：
- 意向共享锁： 简称IS，表示事务打算在表中的各个行上设置共享锁。比如
SELECT ... LOCK IN SHARE MODE会加IS锁 - 意向互斥锁：简称IX，表示事务打算对表中的各个行设置互斥锁。比如
UPDATE, INSERT、DELETE、SELECT ... FOR UPDATE等会加IX锁
意向锁加锁规则如下：
- 事务可以获取表中行上的共享锁之前，它必须首先获取表上的 IS 锁或更强的锁（比如X锁）
- 事务可以获取表中行上的互斥锁之前，它必须首先获取表上的 IX 锁
也就是说在获取行锁之前必须先得获取到表上的意向锁，从而实现了表锁和行锁共存。比如当SELECT ... LOCK IN SHARE MODE执行时会先加一个 IS 锁，当UPDATE、INSERT、DELETE或者SELECT ... FOR UPDATE执行时会先给表上加一个 IX 锁，后面会进行说明。
表锁兼容性
下图表示了表级锁之间的兼容性
| X | IX | S | IS | |
|---|---|---|---|---|
| X | Conflict | Conflict | Conflict | Conflict |
| IX | Conflict | Compatible | Conflict | Compatible |
| S | Conflict | Conflict | Compatible | Compatible |
| IS | Conflict | Compatible | Compatible | Compatible |
如果事务请求的锁与现有的锁兼容，则该事务加锁成功，但如果与现有锁冲突，则加锁失败，事务会一直等待，直到存在的这个锁被释放。如果请求的锁与现有的锁冲突，并且请求的这个锁可能会导致死锁，则会发生错误。
不同的事务可以在同一张表上获取不同种类的意向锁，但是第一个获取表上意向互斥锁的事务会阻止其他事务获取该表上的任何 S 或 X 锁。相反，第一个获取表上意向共享锁的事务会阻止其他事务获取表上的任何 X 锁。两阶段过程允许按顺序解决锁请求，而不会阻塞锁和兼容的相应操作。
除了全表请求（例如LOCK TABLES ... WRITE）之外，意向锁不会阻塞任何东西。 意向锁的主要目的是表明有事务正在锁定表中的行，或者将要锁定表中的行，那么此时其他事务想获取的锁与存在意向锁冲突的话，其他事务就必须等待该意向锁被释放。
索引记录锁 Record Lock
索引记录锁是一种加在索引记录上的锁，这个索引记录既可以是主键索引也可以是二级索引。例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;会防止任何其他事务插入、更新或删除 t.c1 值为 10 的行
记录锁锁的是索引的记录，即使定义的表没有索引，对于这种情况，InnoDB 创建一个隐藏的聚集索引并使用这个索引来实现索引记录锁
间隙锁 Gap Lock
间隙锁是在加在索引记录之间的间隙上的锁，或加在第一条索引记录之前或最后一条索引记录之后的间隙上的锁。例如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;可以防止其他事务将值 15 插入 t.c1 列，不管列中是否已经存在任何 15 这个值，因为范围内所有现有值之间的间隙都被锁了
间隙可能跨越单个索引值、多个索引值，甚至是空的。间隙锁是性能和并发性之间权衡的一部分，并且仅用于某些事务隔离级别，例如RR级别，不是所有隔离级别都会用到间隙锁。
使用唯一索引锁定行以搜索唯一行的SQL语句不需要间隙锁，如果是多列唯一索引，并且搜索条件仅使用了多列唯一索引的某些列的情况，还是可能会加间隙锁。
举个例子，如果 id 列具有唯一索引，则下面这条sql仅对 id 值为 100 的行使用索引记录锁，而其他会话是否在id = 100前面或后面的间隙中插入行是无所谓的，因此不会加间隙锁。
SELECT * FROM child WHERE id = 100;
但是，如果 id 列没有索引或具有非唯一索引，则该sql会锁定前面的间隙，防止产生2条id=100的数据
这里还值得注意的是，不同的事务可以在间隙上持有冲突的锁。 例如事务 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 条件后，不匹配行的记录锁将被释放。 对于 UPDATE 语句，InnoDB 执行半一致性（semi-consistent）读取，这样它会将最新提交的版本返回给 MySQL，以便 MySQL 可以确定该行是否匹配 UPDATE 的 WHERE 条件。
Next-Key Locks
Next-Key Lock是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合
InnoDB 执行行锁的方式是，当它搜索或扫描表索引时，它会在它遇到的索引记录上设置共享或互斥锁。因此，行锁实际上也是索引记录锁。
索引记录上的 next-key 锁也会影响该索引记录之前的“间隙”，也就是说，next-key 锁是索引记录锁加上索引记录前面的间隙上的间隙锁。如果一个会话在索引中的记录 R 上具有共享或互斥锁，则另一个会话不能在索引顺序中 R 之前的间隙中插入新的索引记录。
假设索引包含值 10、11、13 和 20，此索引可能的 next-key 锁包含以下区间，其中圆括号表示排除区间端点，方括号表示包含端点：(- ∞, 10]，(10, 11]，(11, 13]，(13, 20]，(20, + ∞)，也就是说是左开右闭的区间。
为什么是左开右闭，而且是相邻值的区间
对于最后一个间隔，next-key lock 锁定索引中最大值上方的间隙。同时MySQL还假设了一条不存在的记录，叫做"supremum” pseudo-record，它的 key 值高于该索引中的任何实际值。"supremum" pseudo-record不是真正的索引记录，因此，实际上这个next-key锁还是只锁定最大索引值之后的间隙。
默认情况下，InnoDB 以 REPEATABLE READ 事务隔离级别运行。 在这种情况下，InnoDB 使用 next-key 锁进行搜索和索引扫描，这可以防止幻读现象
插入意向锁
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-insert-intention-locks
插入意向锁是一种在插入行之前由 INSERT 操作设置的间隙锁。此锁表示插入的意图，如果多个事务想要插入行到同一索引间隙，但是未插入到间隙内的同一位置，则它们无需相互等待。 例如，假设当前索引已经有值为 4 和 7 的记录，有两个事务分别尝试插入值 5 和 6 ，在获得插入行的互斥锁之前，每个事务可以使用插入意向锁对 4 和 7 之间的间隙上进行加锁， 但这两个事务不会相互阻塞，因为每个事物要操作的行是不冲突的。
下面举例说明插入意向锁的使用，先建表并插入id为90和102的数据
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
首先是没有冲突的情况
-- 事务1
START TRANSACTION;
INSERT INTO child (id) values (95);
COMMIT;
-- 事务2
START TRANSACTION;
INSERT INTO child (id) VALUES (97);
COMMIT;
下面是加锁情况，这种情况下是看不到插入意向锁的，因为此时没有其他事务也要插入id相同的行
冲突情况
下面这个例子来说明存在冲突的情况，事务在获得插入记录的互斥锁之前会先获取插入意向锁。事务 A 启动，并且在 ID 大于 100 的索引记录上加互斥锁。互斥锁包括记录 102 之前的间隙锁：
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id |
+-----+
| 102 |
+-----+
此时，加锁情况如下图所示：对 id 为 102 的行加了一个互斥锁
然后客户端 B 开始事务，将记录插入到间隙中，B 会阻塞。
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);
事务在等待获得互斥锁时采用插入意向锁，加锁情况如下图所示：
如果表有索引，那么插入操作可能需要在索引上获取锁，以确保索引的一致性。这可能会增加锁的范围和复杂性。
自增锁
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-auto-inc-locks
如果自增要加锁的话，会加一个表锁，叫做AUTO-INC锁。针对表中存在AUTO_INCREMENT列时，在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务都必须等待，以便第一个事务插入的行生成连续的主键值。
通过SELECT * FROM performance_schema.data_locks;只能看到下面这一条 IX 锁记录，因为自增锁是sql语句处理完成后就释放，具体可参考 InnoDB自增处理 了解MySQL如何处理自增的
下面是测试所用sql：
CREATE TABLE `autoinc_table` (
`id` bigint NOT NULL AUTO_INCREMENT,
`col1` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`col2` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`col3` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
SQL语句使用的锁
不管 sql 语句中是否存在 WHERE 条件可以将该行排除掉，加锁读（locking read）、UPDATE 或 DELETE 通常都会在 SQL 语句处理过程中在被扫描的每个索引记录上设置索引记录锁。
InnoDB 不会记忆确切的 WHERE 条件，而只知道扫描了哪些索引范围，因此查询过程中访问到的对象才会加锁，这些锁通常是next-key lock，它也会阻止其他事务将新行插入到被锁定记录之前的间隙。
如果显式禁用了间隙锁，那么就不会使用next-key lock，同时事务隔离级别也会影响具体会设置哪种锁。
如果在查询使用到了辅助索引，同时对辅助索引加的锁是互斥锁，那么InnoDB此时也会对主键加锁
如果一条sql没用到索引，那么就必须扫描整个表以处理该语句，则表的每一行都会被锁定，这反过来又会阻止其他用户对表的所有插入，因此创建良好的索引很重要，这样查询就不会扫描超出必要的行数。
InnoDB 会为不同的sql语句设置特定类型的锁，查看 performance_schema.data_locks 表来查看所加的锁
SELECT
ENGINE_LOCK_ID,
ENGINE_TRANSACTION_ID,
THREAD_ID,
OBJECT_SCHEMA, OBJECT_NAME,
INDEX_NAME,
LOCK_TYPE, LOCK_MODE, LOCK_STATUS
FROM performance_schema.data_locks;
下面对每种sql可能会加的锁进行说明，除特别说明外，测试结果的隔离级别均为REPEATABLE READ
SELECT ... FROM
对于普通的select，InnoDB都是默认快照读（consistent read），因此不会加锁，除非隔离级别是SERIALIZABLE的情况下。对于序列化读，搜索过程中将会为遇到的所有索引记录加上共享的next-key锁。对于使用唯一索引锁定行以搜索唯一行的语句，则只需要一个索引记录锁。
比如SELEC id FROM user WHERE id = 1，这种情况下，即使是序列化读这个级别，也不用对所有索引进行加锁，只用对id = 1的索引进行加锁
SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE
使用了唯一索引的SELECT ... FOR UPDATE和SELECT ... FOR SHARE语句会为扫描过程中扫描到的行加锁，并释放不在期望结果集中的行上的锁，对于那些不在 where 条件中的行，本来就不是本次查询的目标，加锁浪费资源，因此在计算条件后不满则则会释放锁。
在某些情况下，被加锁的行可能不会立即解锁，因为结果行与其原始源之间的关系在查询执行期间丢失。例如，在 UNION 查询中，可能会将表中已扫描并加了锁的行插入到临时表中，然后再评估它们是否符合结果集的条件。 在这种情况下，临时表中的行与原始表中的行之间的关系丢失，并且后面的行要直到查询执行结束才解锁
SELECT ... LOCK IN SHARE MODE 会在搜索遇到的所有索引记录上设置共享的 next-key 锁。 但是对于使用唯一索引锁定行以搜索唯一行的语句，只需要一个索引记录锁
以前面的 child 表为例：表中数据只有id=90和id=102的两行，事务执行下面的sql
START TRANSACTION;
SELECT * FROM child WHERE id = 10 LOCK IN SHARE MODE;
加锁情况如下图所示
SELECT ... FOR UPDATE 在搜索遇到的每条记录上设置一个互斥的 next-key 锁。事务执行SELECT * FROM child WHERE id > 80 FOR UPDATE;结果如下：
同样对于使用唯一索引锁定行以搜索唯一行的语句，只需要一个索引记录锁。事务执行SELECT * FROM child WHERE id = 90 FOR UPDATE;结果如下：
如果唯一索引查的是不存在的记录，例如事务执行SELECT * FROM child WHERE id = 90 FOR UPDATE;结果如下：会加间隙锁
对于搜索过程中遇到的索引，在某些事务隔离级别下，SELECT ... FOR UPDATE 会阻止其他会话执行 SELECT ... LOCK IN SHARE MODE 或读取操作。
Locking Read
对于带UPDATE语句和 DELETE 语句， 带FOR UPDATE 或者带 FOR SHARE 的SELECT语句，这些都属于加锁读操作，这些操作具体要加的锁依赖于sql是否使用了唯一索引，并且是否有唯一性搜索条件，或者搜索条件是 range 类型
- 对于具有唯一搜索条件的唯一索引，InnoDB只对查找到的索引记录进行加锁，而不是该索引前的间隙。比如
SELECT * FROM user WHERE id = 1 FOR UPDATE，其中id是主键，此时只对id为1的索引进行加锁，前提是 id 为1的记录真实存在，如果不存在还是会加间隙锁，这个前面已经提到过。 - 对于其他搜索条件和非唯一索引，InnoDB会锁定扫描的索引范围，使用间隙锁或 next-key lock来阻止其他会话插入到范围所覆盖的间隙中。比如
SELECT * FROM user WHERE user_age > 22 AND user_age < 26 FOR UPDATE，其中 user_id 是非唯一索引，则对 22 ~ 26 这个间隙进行加锁
非唯一搜索条件
比如UPDATE child SET id = id WHERE id > 90 AND id < 200;这条sql，EXPLAIN结果type是range
加锁情况如下
非唯一索引
看下面这个例子
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
`c1` int(32) NOT NULL, `c2` int,
PRIMARY KEY (`c1`), KEY `idx_c2` (`c2`)
);
INSERT INTO t1 VALUES (1, 1), (2, 1);
执行UPDATE t1 SET c2 = c2 + 1 WHERE c2 = 1;加锁情况如下：
这里对idx_c2和主键都加了锁，同时还在idx_c2索引上加了两个间隙锁，这2个间隙锁和2个索引记录锁共同组成了一个next key锁
UPDATE ... WHERE
UPDATE ... WHERE ...会在搜索遇到的每条记录上设置一个互斥的 next-key 锁。 但是对于使用唯一索引锁定行以搜索唯一行的语句，只需要一个索引记录锁。
START TRANSACTION;
UPDATE category SET name = name WHERE category_id = 1;
可以看到加了一个 IX 表锁，同时在主键索引上加了一个 X 行锁
如果不加 where 条件呢？
START TRANSACTION;
UPDATE category SET name = name;
除了一条是表锁，另外 17 条是记录锁，此时表中数据只有 16 条，为什么有 18 条锁记录呢？
SELECT COUNT(*) FROM category; -- 16
原因在于上面的查询只查了部分字段，其实多的那一条和其他是有区别的，如下图所示：
这个就是前面 next key 锁提到的一条不存在的记录："supremum" pseudo-record，会对这条不存在的记录进行加锁。同时也说明，UPDATE 不加WHERE 条件时会加 next-key 锁，如果加 where 1 = 1 这类条件，其实 1 = 1 会被处理掉，最终还是相当于没条件。
二级索引加锁
UPDATE二级索引会导致主键索引上也加锁
以前面的t1表为例，UPDATE t1 SET c2 = c2 * 10 WHERE c2 = 1;
这个实际上是因为这里的UPDATE通过二级索引查找，最终会查到聚集索引上被锁的行
共享锁
当 UPDATE 修改聚集索引记录时，会在受影响的二级索引记录上采用隐式锁定。
在插入新的二级索引记录之前，会执行重复检查扫描，UPDATE 操作也会对受影响的二级索引记录加共享锁。
DELETE FROM ... WHERE ...
DELETE FROM ... WHERE ...会在搜索遇到的每条记录上设置一个互斥的 next-key 锁。 但是对于使用唯一索引锁定行以搜索唯一行的语句，只需要一个索引记录锁
INSERT
INSERT 会在插入的行上设置互斥锁。 这个锁是索引记录锁，不是 next-key 锁，并且不会阻止其他会话在该会话想要插入的行之前或之后的间隙中插入行。在插入行之前，会设置一种称为插入意向锁的间隙锁。 此锁表示插入的意向，即如果多个事务都要插入到同一索引间隙中，但是未插入到间隙内的同一位置，只是插入到同一个范围，则它们无需相互等待。 假设有值为 4 和 7 的索引记录。尝试插入值 5 和 6 的两个单独事务会优先使用插入意向锁锁定 4 和 7 之间的间隙，而不是获取待插入行的互斥锁。此时这两个插入意向锁是不会发生阻塞的，因为 5 和 6 这两行是不冲突的。也就是说，INSERT 可能会设置两种锁，先设置插入意向锁，如果两个不同的事务要插入的位置相同，则设置互斥锁
如果发生Duplicate Key Error，则会在重复的这个索引记录上设置共享锁，此时已经有该索引记录值存在。如果另一个会话已经拥有互斥锁，那么如果有多个会话尝试插入同一行，则这种情况下共享锁的使用可能会导致死锁。
Duplicate Key Error Cause DeadLock
发生Duplicate Key Error时加的这个共享锁在并发的情况下是会产生死锁的，下面来看一下死锁是如何产生的：
以下面的 t1 表为例：
CREATE TABLE t1 (i INT, PRIMARYKEY(i)) ENGINE= InnoDB;
考虑以下过程：
| Session 1 | Session 2 | Session 3 |
|---|---|---|
| START TRANSACTION; | START TRANSACTION; | START TRANSACTION; |
| INSERT INTO t1 VALUES(1); | ||
| INSERT INTO t1 VALUES(1); -- Wait |
||
| INSERT INTO t1 VALUES(1); -- Wait |
||
| ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | ||
| ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | ||
| ROLLBACK |
Session 1 的第一个操作获取 id 为 1 的这行的互斥锁。Session 2 和 3 的操作都会导致Duplicate Key错误，并且它们都会请求该行的共享锁。 当会话 1 回滚时，它会释放其在行上的互斥锁，然后处于等待队列中的会话 2 和 3 的共享锁请求被响应。 此时会话 2 和会话 3 会出现死锁：根据共享锁和互斥锁的加锁规则，由于对方持有共享锁，两者都无法获得该行的互斥锁
为什么在INSERT操作失败后还要对行加锁 ？
原因是它需要锁定现有的行，以便后续插入重复记录的尝试始终失败。举个例子，假设表中存在 key 值为1的数据，现有2个事务
| Transaction A | Transaction B | |
|---|---|---|
| START TRANSACTION; | ||
| fails as "duplicate" | INSERT INTO t VALUE(1); | |
| START TRANSACTION; | ||
| B must wait | DELETE FROM t WHERE id = 1; | |
| COMMIT; | ||
| transaction is still in progress | ||
| must fail to stay consistent with the previous attempt | INSERT INTO t VALUE(1); |
A 这个事务在同一个事务内尝试了2次插入操作。如果事务A的第一次插入失败了不加锁的话，允许事务B同时删除，那么这两个事务将不一致。假设事务B成功，即表示事务A在出现Duplicate Key后未加锁，那么事务A第二次INSERT尝试的结果有两种情况：
- 成功：但这与第一次尝试不一致，因为第一次尝试失败了
- 失败：但是并发的事务B已经删除了行，所以不应该有冲突
这两种结果都会破坏事务的 ACID 特性
另外一个相似的情况是，如果表中已经包含键值为 1 的行，假设现在有三个会话依次执行以下操作，则会出现类似的情况：
| Session 1 | Session 2 | Session 3 | |
|---|---|---|---|
| START TRANSACTION; | START TRANSACTION; | START TRANSACTION; | |
| DELETE FROM t1 WHERE i =1; | |||
| Duplicate Key Error | INSERTINTO t1 VALUES(1); | ||
| Duplicate Key Error | INSERTINTO t1 VALUES(1); | ||
| COMMIT; |
Session 1 的第一个操作获取行的互斥锁。 会话 2 和 3 的操作都导致重复key错误，并且它们都会请求该行的共享锁。当会话 1 提交时，它会释放其对行的互斥锁，并会话 2 和 3 的共享锁请求。此时会话 2 和会话 3 会出现死锁：由于对方持有共享锁，两者都无法获得该行的互斥锁。
为什么加的是共享锁而不是互斥锁？
加互斥锁的性能比共享锁的性能差
INSERT ... ON DUPLICATE KEY UPDATE
INSERT ... ON DUPLICATE KEY UPDATE 与简单的 INSERT 不同之处在于，当发生重复键错误时，将在要更新的行上放置互斥锁而不是共享锁。 对重复的主键值采用互斥索引记录锁。 对重复的唯一键值采用互斥的 next-key 锁。
INSERT ... ON DUPLICATE KEY UPDATE和普通的 INSERT 区别在于，如果发生duplicate-key error，INSERT ... ON DUPLICATE KEY UPDATE是在要更新的行上加的互斥锁，而普通的INSERT是在要插入的位置上加的共享锁
REPLACE
如果唯一键上没有冲突，则 REPLACE 就像 INSERT 一样完成。 否则，将在要替换的行上放置一个独占的 next-key 锁。
INSERT INTO T SELECT ... FROM S WHERE ...
INSERT INTO T SELECT ... FROM S WHERE ... 会在在插入到 T 的每一行上设置一个互斥索引记录锁，注意这里没有间隙锁。如果事务隔离级别为 READ COMMITTED，或者启用了 innodb_locks_unsafe_for_binlog 并且事务隔离级别不是 SERIALIZABLE，InnoDB 使用不加锁的Consistent Read操作在表 S 上进行搜索。 否则，InnoDB 将 在 S 的行上设置共享的 next-key 锁。
InnoDB 必须在后一种情况下设置锁：在使用基于语句的二进制日志进行前滚恢复期间，每个 SQL 语句必须以与原来被执行时完全相同的方式执行。
CREATE TABLE ... SELECT ...
CREATE TABLE ... SELECT ... 使用共享的 next-key 锁或作为一致读取执行 SELECT，如 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 列关联的索引末尾设置互斥锁，同时初始化表上先前指定的 AUTO_INCREMENT 列。
innodb_autoinc_lock_mode
当 innodb_autoinc_lock_mode=0 时，InnoDB 使用一种特殊的 AUTO-INC 表锁模式，在访问自动增量计数器时，获取锁并保持到当前 SQL 语句的末尾（而不是整个事务的末尾）。 当 AUTO-INC 表锁被持有时，其他客户端不能插入到表中。 innodb_autoinc_lock_mode=1 的“批量插入”也会发生相同的行为。 表级 AUTO-INC 锁不与 innodb_autoinc_lock_mode=2 一起使用。
mysql> SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)
InnoDB 在不设置任何锁的情况下获取先前初始化的 AUTO_INCREMENT 列的值。
关于自增的处理细节查看
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
FOREIGN KEY
如果在表上定义了 FOREIGN KEY 约束，则任何需要检查约束条件的插入、更新或删除都会在它查看的记录上设置共享记录锁。
InnoDB 在不满足约束的情况下也会设置这些锁，就和前面的Duplicate Key Error的情况一样
LOCK TABLES
LOCK TABLES 加的是表锁，但设置这些锁的是 InnoDB 层之上的更高 MySQL 层，而不是InnoDB来设置的，如果 innodb_table_locks = 1（默认值为1）并且 autocommit = 0，则 InnoDB 知道表锁，并且 InnoDB 之上的 MySQL 层知道行级锁。
如果 innodb_table_locks 不为 1 或者 autocommit = 0，在这种情况下，较高的 MySQL 层不知道行级锁，所以有可能在被另一个会话拥有行级锁的表上获得表锁。InnoDB 的自动死锁检测无法检测到涉及此类表锁的死锁。但是，这不会危及事务完整性
如果 innodb_table_locks=1，LOCK TABLES 在每个表上获取两个锁。除了 MySQL 层的表锁之外，它还获取了 InnoDB 表锁。通过设置 innodb_table_locks=0 可以避免获取 InnoDB 表锁。如果没有任何一个 InnoDB 表锁被获取，即使表的某些记录被其他事务锁定，LOCK TABLES 这条sql的执行也会完成。
在 MySQL 5.7 中，innodb_table_locks=0 对使用 LOCK TABLES ... WRITE 进行显式锁定的表无效，但是它对通过 LOCK TABLES ... WRITE 隐式或通过 LOCK TABLES ... READ 锁定以进行读取或写入的表有效。
LOCK TABLES ... WRITE涉及到隐式上锁的例子有：触发器
当事务提交或中止时，事务持有的所有 InnoDB 锁都会被释放。因此，在 autocommit=1 模式下对 InnoDB 表调用 LOCK TABLES 没有多大意义，因为获取的 InnoDB 表锁将立即释放。
注意：不能在事务中间锁定其他表，因为 LOCK TABLES 会隐式地执行 COMMIT 和 UNLOCK TABLES。
查看锁信息
参考：https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-locks-table.html
MySQl 8之前
SELECT * FROM information_schema.innodb_locks; # 查询InnoDB锁信息
SELECT * FROM information_schema.innodb_lock_waits; # 查询InnoDB锁等待列表
SELECT * FROM sys.innodb_lock_waits; # 查询InnoDB锁等待列表
MySQl 8之后
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
注意：sys.innodb_lock_waits 仍可以用，它其实是一个视图
这三个表在 MySQL 数据库中涉及锁的监控和分析，主要用于性能调优和故障排查。下面是它们各自的作用：
- performance_schema.data_locks：提供有关当前活动的锁的信息。它包括锁的类型、锁的状态、持有锁的线程、被锁定的资源等。可以用来诊断和分析锁竞争问题，帮助识别哪些线程正在持有或等待锁。
- performance_schema.data_lock_waits：记录了线程在等待数据锁时的详细信息，包括等待的开始时间、持续时间、等待的锁类型和被锁住的线程等。用于分析锁等待的情况，帮助识别性能瓶颈和优化数据库操作。
- sys.innodb_lock_waits：该视图提供了 InnoDB 存储引擎中的锁等待信息。它显示了哪些事务在等待锁，以及造成等待的事务。可以帮助开发者和数据库管理员理解事务之间的锁竞争，进而优化事务的设计和执行。
以下是 performance_schema.data_locks、performance_schema.data_lock_waits 和 sys.innodb_lock_waits 三个表的字段信息及其含义的详细介绍。
performance_schema.data_locks
该表包含关于当前活动锁的详细信息，主要字段如下：
| 字段名 | 数据类型 | 含义 |
|---|---|---|
OBJECT_TYPE |
ENUM | 锁定对象的类型（如 TABLE, INDEX, etc.） |
OBJECT_SCHEMA |
STRING | 锁定对象的数据库模式（schema） |
OBJECT_NAME |
STRING | 锁定对象的名称 |
LOCK_TYPE |
ENUM | 锁的类型（如 RECORD, TABLE, etc.） |
LOCK_STATUS |
ENUM | 锁的状态（如 GRANTED, WAITING） |
LOCK_DURATION |
TIME | 锁持续的时间 |
THREAD_ID |
BIGINT | 持有或请求锁的线程 ID |
TRANSACTION_ID |
BIGINT | 相关事务的 ID |
LOCK_MODE |
ENUM | 锁的模式（如 S, X，表示共享锁和互斥锁） |
LOCK_OWNER_THREAD_ID |
BIGINT | 当前持有锁的线程 ID |
performance_schema.data_lock_waits
该表记录了等待锁的线程信息，主要字段如下：
| 字段名 | 数据类型 | 含义 |
|---|---|---|
REQUESTING_THREAD_ID |
BIGINT | 请求锁的线程 ID |
REQUESTING_LOCK_ID |
STRING | 请求的锁的 ID |
BLOCKING_THREAD_ID |
BIGINT | 阻塞请求的线程 ID |
BLOCKING_LOCK_ID |
STRING | 阻塞的锁的 ID |
WAIT_DURATION |
TIME | 等待的持续时间 |
WAIT_START |
TIMESTAMP | 等待开始的时间 |
sys.innodb_lock_waits
该视图提供了 InnoDB 锁等待的信息，主要字段如下：
| 字段名 | 数据类型 | 含义 |
|---|---|---|
requesting_trx_id |
STRING | 请求锁的事务 ID |
blocking_trx_id |
STRING | 阻塞请求的事务 ID |
waiting_time |
BIGINT | 等待的持续时间（毫秒） |
wait_started |
TIMESTAMP | 等待开始的时间 |
lock_mode |
ENUM | 锁的模式（如 S, X） |
lock_type |
STRING | 锁的类型（如 RECORD, TABLE） |
table_name |
STRING | 锁定的表的名称 |
index_name |
STRING | 锁定的索引的名称（如果适用） |
浙公网安备 33010602011771号