共计 49837 个字符，预计需要花费 125 分钟才能阅读完成。
注释开始
“XXX 语句加了什么锁 ” 自身就是个伪命题，一条语句到底加了什么锁受到很多条件制约：
- 以后事务的隔离级别，例如：RC 隔离级别只会呈现记录锁，RR 隔离级别才有可能呈现 GAP 锁
- SQL 是一致性非锁定读 (Consistent NonLocking Read，即一般 select 语句) 还是 DML(INSERT/UPDATE/DELETE)或锁定读(Locking Read)
- SQL 执行时是否应用到了索引，以及所应用的索引类型(聚簇索引 / 主键索引、非聚簇索引 / 二级索引 / 辅助索引、惟一索引)
- 表中的数据状况等，即两个 SQL 的执行打算是什么？索引扫描？全表扫描？以及数据分布间隙影响临键锁(Next-Key)
- 加锁情况也和数据库的版本非亲非故，目前眼下 5.7.x 和 8.0.x 系列加锁逻辑基本一致，本文是基于 mysql-8.0.25 版本撰写的！
1、隔离级别(isolation level)
数据库事务须要满足 ACID 四个准则，”I” 即隔离性，它要求两个事务互不影响，不能看到对方尚未提交的数据。数据库有 4 中隔离级别 (isolation level)，依照隔离性从弱到强(相应地，性能和并发性从强到弱) 别离是：
- Read Uncommitted，上面简称 RU
- Read Committed，上面简称 RC
- Repeatable Read，上面简称 RR
- Serializable
“I” 隔离性正是通过锁机制来实现的。提到锁就会波及到死锁，须要明确的是死锁的可能性并不受隔离级别影响，因为隔离级别扭转的是读操作的行为，而死锁是由写操作产生的。
-
规范 SQL 隔离级别
SQL 的规范制定者提出了不同的隔离级别：读未提交（READ-UNCOMMITED）、读已提交（READ_COMMITED）、可反复读（REPEATABLE-READ）、序列化读（SERIALIZABLE）。其中最高级隔离级别就是序列化读，而在其余隔离级别中，因为事务是并发执行的，所以或多或少容许呈现一些问题。
- 脏读：后一个事物读取并应用到前一个事务还未提交的数据，称之为脏读。
- 不可反复读：前一个事务中屡次读取同一个数据，并且期间该同一数据被后一个事物批改过，而引发的前一事务读取到同一数据不同后果的问题，称之为不可反复读。
- 幻读：幻读是指同一查问在同一事务中屡次进行，因为其余事务所做的插入操作，导致每次返回不同的后果集，此时产生幻像读，就好象产生了幻觉一样。
- 第 1 类更新失落：A 事务撤销时，把曾经提交的 B 事务的更新数据笼罩了。这类更新失落在目前支流数据库中曾经不存在了。
- 第 2 类更新失落 ：A 事务笼罩 B 事务曾经提交的数据，造成 B 事务所做操作失落。留神此处的第 2 类更新失落指的是诸如：
update account set money = money + 100 where id = 'xxx'这种状况；而对于update account set money = 100 where id = 'xxx'则无能为力，因为这波及到 ABA 问题，四种隔离级别都不能解决该问题，能够借助乐观锁来解决。
隔离级别 是否存在脏读 是否存在不可反复读 是否存在幻读 是否存在第 1 类更新失落 是否存在第 2 类更新失落 读未提交（READ-UNCOMMITED） 是 是 是 否 是 读已提交（READ-COMMITED） 否 是 是 否 是 可反复读（REPEATABLE-READ） 否 否 是 否 否 序列化读（SERIALIZABLE） 否 否 否 否 否
-
规范 SQL 事务隔离级别实现原理
咱们下面遇到的问题其实就是并发事务下的管制问题，解决并发事务的最常见形式就是乐观并发管制了（也就是数据库中的锁）。规范 SQL 事务隔离级别的实现是依赖锁的，咱们来看下具体是怎么实现的：
事务隔离级别 实现形式 读未提交（RU） 事务对以后被读取的数据不加锁；事务在更新某数据的霎时（就是产生更新的霎时），必须先对其加 行级共享锁，直到事务完结才开释。 读已提交（RC） 事务对以后被读取的数据加 行级共享锁（当读到时才加锁），一旦读完该行，立刻开释该行级共享锁；事务在更新某数据的霎时（就是产生更新的霎时），必须先对其加 行级排他锁，直到事务完结才开释。 可反复读（RR） 事务在读取某数据的霎时（就是开始读取的霎时），必须先对其加 行级共享锁 ，直到事务完结才开释；事务在更新某数据的霎时（就是产生更新的霎时），必须先对其加 行级排他锁，直到事务完结才开释。 序列化读（S） 事务在读取数据时，必须先对其加 表级共享锁 ，直到事务完结才开释；事务在更新数据时，必须先对其加 表级排他锁，直到事务完结才开释。 能够看到，在只应用锁来实现隔离级别的管制的时候，须要频繁的加锁解锁，而且很容易产生读写的抵触。
-
MySQL 的隔离级别
-
查看 MySQL 的隔离级别
MySQL 的默认隔离级别是：REPEATABLE-READ
-- MySQL8.0 版本查问语句 -- 查问以后会话的事务隔离级别 SELECT @@transaction_isolation; -- 查问全局的事务隔离级别 SHOW VARIABLES LIKE '%transaction_isolation%'; SELECT @@global.transaction_isolation; -- MySQL5.7 版本查问语句 -- 查问以后会话的事务隔离级别 SELECT @@tx_isolation; -- 查问全局的事务隔离级别 SHOW VARIABLES LIKE '%tx_isolation%'; SELECT @@global.tx_isolation; -
设置 MySQL 的隔离级别
-- 设置零碎以后隔离级别 set global transaction isolation level repeatable read -- 设置以后会话隔离级别 set session transaction isolation level repeatable read -- 设置下一个会话的隔离级别，这个没法验证，但的确起效 set transaction isolation level repeatable read或者在 my.cnf 文件中设置：
[mysqld] transaction-isolation = READ-COMMITTED
MySQL 的隔离级别比规范隔离级别提前了一个级别，具体如下：
隔离级别 是否存在脏读 是否存在不可反复读 是否存在幻读 是否存在第 1 类更新失落 是否存在第 2 类更新失落 读未提交（READ-UNCOMMITED） 是 是 是 否 是 读已提交（READ-COMMITED） 否 <u>否</u> 是 否 <u>否</u> 可反复读（REPEATABLE-READ） 否 否 <u>否</u> 否 否 序列化读（SERIALIZABLE） 否 否 否 否 否 精确地说，MySQL 的 InnoDB 引擎在提已交读（READ-COMMITED）级别通过 MVCC 解决了不可反复读的问题，在可反复读（REPEATABLE-READ）级别通过间隙锁解决了幻读问题。也就是说 MySQL 的事务隔离级别比对应的规范事务隔离级别更为谨严，也即：
- MySQL 的读已提交（READ-COMMITED）解决了不可反复读问题(抵得上规范事务隔离级别的 REPEATABLE-READ)；
- MySQL 的可反复读（REPEATABLE-READ）解决了幻读问题；
-
2、一致性非锁定读和锁定读
InnoDB 有两种不同的 SELECT，即一般 SELECT 和 锁定读 SELECT。锁定读 SELECT 又有两种，即 SELECT … FOR SHARE 和 SELECT … FOR UPDATE；锁定读 SELECT 之外的则是一般 SELECT。
不同的 SELECT 是否都须要加锁呢？
- 一般 SELECT 时应用一致性非锁定读，不加锁；
- 锁定读 SELECT 应用锁定读，加锁；
- 此外，DML(INSERT/UPDATE/DELETE)时，须要先查问表中的记录，此时也应用锁定读，加锁；
FOR SHARE 语法是 MySQL 8.0 时退出的，FOR SHARE 和 LOCK IN SHARE MODE 是等价的，即，FOR SHARE 用于代替 LOCK IN SHARE MODE，不过，为了向后兼容，LOCK IN SHARE MODE 仍然可用。
2.1、一致性非锁定读(Consistent NonLocking Read)
InnoDB 采纳多版本并发管制 (MVCC, multiversion concurrency control) 来减少读操作的并发性。MVCC 是指，InnoDB 应用基于工夫点的快照来获取查问后果，读取时在拜访的表上不设置任何锁，因而，在事务 T1 读取的同一时刻，事务 T2 能够自在的批改事务 T1 所读取的数据。这种读操作被称为一致性非锁定读。这里的读操作就是一般 SELECT。
隔离级别为 RU 和 Serializable 时不须要 MVCC，因而，只有 RC 和 RR 时，才存在 MVCC，才存在一致性非锁定读。
一致性非锁定读在两种隔离级别 RC 和 RR 时，是否有什么不同呢？是的，两种隔离级别下，拍得快照的工夫点不同
- RC 时，同一个事务内的每一个一致性读总是设置和读取它本人的最新快照。也就是说，每次读取时，都再从新拍得一个最新的快照（所以，RC 时总是能够读取到最新提交的数据）。
- RR 时，同一个事务内的所有的一致性读 总是读取同一个快照，此快照是执行该事务的第一个一致性读时所拍得的。
2.2、锁定读(Locking Read)
如果你先查问数据，而后，在同一个事务内 插入 / 更新 相干数据，一般的 SELECT 语句是不能给你足够的爱护的。其余事务能够更新 / 删除 你刚刚查出的数据行。InnoDB 提供两种锁定读，即：SELECT … FOR SHARE 和 SELECT … FOR UPDATE。它俩都能提供额定的安全性。
这两种锁定读在搜寻时 所遇到的 （ 留神：不是最终后果集中的 ）每一条索引记录(index record) 上设置排它锁或共享锁。此外，如果以后隔离级别是 RR，它还会在每个索引记录后面的间隙上设置排它的或共享的 gap lock（排它的和共享的 gap lock 没有任何区别，二者等价）。
看完背景介绍，咱们再来看一下 InnoDB 提供的各种锁。
3、InnoDB 提供的 8 种不同类型的锁
InnoDB 一共有 8 种锁类型，其中，意向锁 (Intention Locks) 和自增锁 (AUTO-INC Locks) 是表级锁，残余全部都是行级锁。此外，共享锁或排它锁 (Shared and Exclusive Locks) 只管也作为 8 种锁类型之一，它却并不是具体的锁，它是锁的模式，用来“润饰”其余各种类型的锁。
MySQL5.7 及之前，能够通过 information_schema.innodb_locks 查看事务的锁状况，然而，只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁状况。
MySQL8.0 删除了 information_schema.innodb_locks，增加了 performance_schema.data_locks，能够通过 performance_schema.data_locks 查看事务的锁状况，和 MySQL5.7 及之前不同，performance_schema.data_locks 岂但能够看到阻塞该事务的锁，还能够看到该事务所持有的锁，也就是说即便事务并未被阻塞，仍然能够看到事务所持有的锁（不过，正如文中最初一段所说，performance_schema.data_locks 并不总是能看到全副的锁）。表名的变动其实还反映了 8.0 的 performance_schema.data_locks 更为通用了，即便你应用 InnoDB 之外的存储引擎，你仍然能够从 performance_schema.data_locks 看到事务的锁状况。
performance_schema.data_locks 的列 LOCK_MODE 表明了锁的类型，上面在介绍各种锁时，咱们同时指出锁的 LOCK_MODE。
3.1、共享锁或排它锁(Shared and Exclusive Locks)
它并不是一种锁的类型，而是其余各种锁的模式，每种锁都有 shard 或 exclusive 两种模式。
当咱们说到共享锁（S 锁）或排它锁（X 锁）时，个别是指 行上的 共享锁或者 行上的 排它锁。须要留神的是，表锁也存在共享锁和排它锁，即 表上的 S 锁和 表上的 X 锁，表上的锁除了这两种之外，还包含上面将会提到的动向共享锁(Shard Intention Locks) 即 IS 锁、动向排它锁 (Exclusive Intention Locks) 即 IX 锁。表上的锁，除了这四种之外，还有其余类型的锁，这些锁都是在拜访表的元信息时会用到的（create table/alter table/drop table 等），本文不探讨这些锁，具体可见：罕用 SQL 语句的 MDL 加锁源码剖析。
数据行 r 上共享锁 (S 锁) 和排它锁 (X 锁) 的兼容性如下：
假如 T1 持有数据行 r 上的 S 锁，则当 T2 申请 r 上的锁时：
- T2 申请 r 上的 S 锁，则，T2 立刻取得 S 锁。T1 和 T2 同时都持有 r 上的 S 锁。
- T2 申请 r 上的 X 锁，则，T2 无奈取得 X 锁。T2 必须要期待直到 T1 开释 r 上的 S 锁。
假如 T1 持有数据行 r 上的 X 锁，则当 T2 申请 r 上的锁时：
T2 申请 r 上的任何类型的锁时，T2 都无奈取得锁，此时，T2 必须要期待直到 T1 开释 r 上的 X 锁。
3.2、意向锁(Intention Locks)
意向锁是表锁。含意是曾经持有了表锁，稍候将获取该表上某个 / 些行的行锁。有 shard 或 exclusive 两种模式。
LOCK_MODE 别离是：IS 或 IX。
意向锁用来锁定层级数据结构，获取子层级的锁之前，必须先获取到父层级的锁。能够这么看 InnoB 的层级构造：InnoDB 所有数据是 schema 的汇合，schema 是表的汇合，表是行的汇合。意向锁就是获取子层级（数据行）的锁之前，须要首先获取到父层级（表）的锁。
意向锁的目标是告知其余事务，某事务曾经锁定了或行将锁定某个 / 些数据行。事务在获取行锁之前，首先要获取到意向锁，即：
- 事务在获取 行上的 S 锁之前，事务必须首先获取 表上的IS 锁或表上的更强的锁(IX 锁)。
- 事务在获取 行上的 X 锁之前，事务必须首先获取 表上的IX 锁。
事务申请锁时，如果所申请的锁与已存在的锁兼容，则该事务能够胜利取得所申请的锁；如果所申请的锁与已存在的锁抵触，则该事务无奈取得所申请的锁。
表级锁 (table-level lock) 的兼容性矩阵如下：
| <font color=”red”> 表 </font> 级锁 (<font color=”red”>table-level</font> lock) 的兼容性如下 | ||||
|---|---|---|---|---|
| X | IX | S | IS | |
| X | <font color=”red”>Conflict</font> | <font color=”red”>Conflict</font> | <font color=”red”>Conflict</font> | <font color=”red”>Conflict</font> |
| IX | <font color=”red”>Conflict</font> | <font color=”green”>Compatible</font> | <font color=”red”>Conflict</font> | <font color=”green”>Compatible</font> |
| S | <font color=”red”>Conflict</font> | <font color=”red”>Conflict</font> | <font color=”green”>Compatible</font> | <font color=”green”>Compatible</font> |
| IS | <font color=”red”>Conflict</font> | <font color=”green”>Compatible</font> | <font color=”green”>Compatible</font> | <font color=”green”>Compatible</font> |
对于下面的兼容性矩阵，肯定留神两点：
- 在下面的兼容性矩阵中，S 是 表的 (不是行的) 共享锁，X 是 表的 (不是行的) 排它锁。
- 意向锁 IS 和 IX 和任何行锁 都兼容，即：和行的 X 锁或行的 S 锁都兼容（了解这句话与上表无关）。
所以，意向锁只会阻塞全表申请（例如：LOCK TABLES … WRITE|READ），不会阻塞其余任何货色。因为 LOCK TABLES … WRITE|READ 须要设置 X | S 表锁，这会被意向锁 IX 或 IS 所阻塞。
查看表锁语句：show open tables where in_use > 0;
InnoDB 容许表锁和行锁共存，应用意向锁来反对多粒度锁(multiple granularity locking)。意向锁如何反对多粒度锁呢，咱们举例如下
T1: SELECT * FROM t1 WHERE i=1 FOR UPDATE;
T2: LOCK TABLE t1 WRITE;
T1 执行时，须要获取 i = 1 的行的 X 锁，但 T1 获取行锁前，T1 必须先要获取 t1 表的 IX 锁，不存在抵触，于是 T1 胜利取得了 t1 表的 IX 锁，而后，又胜利取得了 i = 1 的行的 X 锁；T2 执行时，须要获取 t1 表的 X 锁，但 T2 发现，t1 表上曾经被设置了 IX 锁，因而，T2 被阻塞（因为表的 X 锁和表的 IX 锁不兼容）。
假如不存在意向锁，则：
T1 执行时，须要获取 i = 1 的行的 X 锁（不须要获取 t1 表的意向锁了）；T2 执行时，须要获取 t1 表的 X 锁，T2 是否获取到 T1 表的 X 锁呢？T2 无奈立刻晓得，T2 不得不遍历表 t1 的每一个数据行以查看，是否某个行上已存在的锁和本人行将设置的 t1 表的 X 锁抵触，这种的判断办法效率切实不高，因为须要遍历整个表。
所以，应用意向锁，实现了“表锁是否抵触”的疾速判断。意向锁就是协调行锁和表锁之间的关系的，或者也能够说，意向锁是协调表下面的读写锁和行下面的读写锁（也就是不同粒度的锁）之间的关系的。
3.3、临键锁(Next-Key Locks)
在隔离级别为 RR 模式下加锁的默认锁类型，会依据条件进行锁进化，进化成索引记录锁(Record Locks)、间隙锁(Gap Locks)、或者两者都存在。
假如表中数据存在 id=1,5,10 三条数据，以排它锁 (X 锁) 为例：
- LOCK_MODE = X,REC_NOT_GAP LOCK_DATA = 5：代表在 id= 5 处的记录锁
- LOCK_MODE = X,GAP LOCK_DATA = 5：代表在闭区间 (1,5) 之间的间隙锁
- LOCK_MODE = X LOCK_DATA = 5：代表在左闭右开区间 (1,5] 之间的临键锁，相当于下面两个的合并
3.4、索引记录锁(Record Locks)
也就是所谓的行锁，锁定的是索引记录。行锁就是索引记录锁，所谓的“锁定某个行”或“在某个行上设置锁”，其实就是在某个索引的特定索引记录（或称索引条目、索引项、索引入口）上设置锁。有 shard 或 exclusive 两种模式。
LOCK_MODE 别离是：S,REC_NOT_GAP 或 X,REC_NOT_GAP。
行锁就是索引记录锁，索引记录锁总是锁定索引记录，即便表上并未定义索引。表未定义索引时，InnoDB 主动创立暗藏的汇集索引（索引名字是 GEN_CLUST_INDEX），应用该索引执行 record lock。
举个例子(MySQL8.0 下)，假如有如下表构造及数据：
CREATE TABLE lock_test (id BIGINT(19) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
age TINYINT(3) NOT NULL DEFAULT '0',
created DATETIME NOT NULL,
PRIMARY KEY (id),
INDEX idx_lock_test_age (age)
) ENGINE=InnoDB;
INSERT INTO lock_test VALUES (1, 'zhangsan', 15, '2021-05-26 18:28:02');
INSERT INTO lock_test VALUES (5, 'lisi', 15, '2021-05-27 18:28:57');
INSERT INTO lock_test VALUES (10, 'wangwu', 21, '2021-05-26 18:29:21');
INSERT INTO lock_test VALUES (15, 'zhaoliu', 35, '2021-06-02 19:19:41');
INSERT INTO lock_test VALUES (23, 'hanjin', 23, '2021-06-02 20:22:20');
INSERT INTO lock_test VALUES (24, 'hanjin', 25, '2021-06-02 20:44:18');
+----+----------+-----+---------------------+
| id | name | age | created |
+----+----------+-----+---------------------+
| 1 | zhangsan | 15 | 2021-05-26 18:28:02 |
| 5 | lisi | 15 | 2021-05-27 18:28:57 |
| 10 | wangwu | 21 | 2021-05-26 18:29:21 |
| 15 | zhaoliu | 35 | 2021-06-02 19:19:41 |
| 23 | hanjin | 23 | 2021-06-02 20:22:20 |
| 24 | hanjin | 25 | 2021-06-02 20:44:18 |
+----+----------+-----+---------------------+-
select * from lock_test where id=xxx for update，走主键索引，在 RC 隔离级别下的加锁状况：-- 示例 1 -- session1 (RC 隔离级别) begin; select * from lock_test where id=5 for update; -- 命中惟一一条记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+---------------+-------------+-----------+ | 9530 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9530 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 | +----------+-------------+------------+-----------+---------------+-------------+-----------+ -- 示例 2 -- session1 (RC 隔离级别) begin; select * from lock_test where id=2 for update; -- 没有命中记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+-----------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+-----------+-------------+-----------+ | 9531 | lock_test | NULL | TABLE | IX | GRANTED | NULL | +----------+-------------+------------+-----------+-----------+-------------+-----------+下面 where 条件中 id 为聚簇索引 / 主键索引，所以：
- id= 5 命中到惟一一条记录时，先加表级动向排他锁(IX)，再在聚簇索引上加行级排他锁。
- id= 2 没有命中到记录时，只加表级动向排他锁(IX)
-
select * from lock_test where id=xxx for share，走主键索引，在 RC 隔离级别下的加锁状况：-- 示例 1 -- session1 (RC 隔离级别) begin; select * from lock_test where id=5 for share; -- 命中惟一一条记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +-----------------+-------------+------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------+-------------+------------+-----------+---------------+-------------+-----------+ | 283810968510344 | lock_test | NULL | TABLE | IS | GRANTED | NULL | | 283810968510344 | lock_test | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 5 | +-----------------+-------------+------------+-----------+---------------+-------------+-----------+- id= 5 命中到惟一一条记录时，先加表级动向共享锁(IS)，再在聚簇索引上加行级共享锁。
-
select * from lock_test where id=xxx for update，走主键索引，在 RR 隔离级别下的加锁状况：-- 示例 1 -- session1 (RR 隔离级别) begin; select * from lock_test where id=5 for update; -- 命中惟一一条记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+---------------+-------------+-----------+ | 9532 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9532 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 | +----------+-------------+------------+-----------+---------------+-------------+-----------+ -- 示例 2 -- session1 (RR 隔离级别) begin; select * from lock_test where id=2 for update; -- 没有命中记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+-----------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+-----------+-------------+-----------+ | 9533 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9533 | lock_test | PRIMARY | RECORD | X,GAP | GRANTED | 5 | +----------+-------------+------------+-----------+-----------+-------------+-----------+下面 where 条件中 id 为聚簇索引 / 主键索引，所以：
- id= 5 命中到惟一一条记录时，先加表级动向排他锁(IX)，再在聚簇索引上加行级排他锁。跟 RC 级别下截然不同！
- id= 2 没有命中到记录时，先加表级动向排他锁 (IX)，再加 GAP 锁。索引是 B + 树组织的，因而索引是从小到大按序排列的，在索引记录上查找给定记录时，InnoDB 会在扫描到匹配记录后，在紧接着第一个不满足查问条件的记录上加 GAP 锁，避免新的满足条件的记录插入，下面这个 GAP 区间是(1,5) 的闭区间，5 记录上理论并没有加锁(你能够另开一个会话，执行 select * from lock_test where id=5 for update 发现并没有被阻塞)
-
select * from lock_test where age=xxx for update，走一般索引，在 RC 隔离级别下的加锁状况：-- 示例 1 -- session1 (RC 隔离级别) begin; select * from lock_test where age=15 for update; -- 命中记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ | 9544 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9544 | lock_test | idx_lock_test_age | RECORD | X,REC_NOT_GAP | GRANTED | 15, 1 | | 9544 | lock_test | idx_lock_test_age | RECORD | X,REC_NOT_GAP | GRANTED | 15, 5 | | 9544 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 | | 9544 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 | +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ -- 示例 2 -- session1 (RC 隔离级别) begin; select * from lock_test where age=33 for update; -- 没有命中记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+-----------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+-----------+-------------+-----------+ | 9552 | lock_test | NULL | TABLE | IX | GRANTED | NULL | +----------+-------------+------------+-----------+-----------+-------------+-----------+下面 where 条件中 age 为非聚簇索引 / 二级索引，所以：
- age=15 命中到 2 条记录时，先加表级动向排他锁(IX)，再在非聚簇索引上加行级排他锁，同时也在对应的聚簇索引 / 主键索引上加行级排他锁！
- age=15 命中到 2 条记录时，在非聚簇索引 idx_lock_test_age 上加行级排他锁是依照索引值的升序挨个程序加锁。其中索引的程序对于数字则按大小升序排序，对于字符串则按字母程序升序排序(name=axx 排在 name=bxx 后面)
- age=33 没有命中到记录时，只加表级动向排他锁(IX)
-
select * from lock_test where age=xxx for share，走一般索引，在 RC 隔离级别下的加锁状况：-- 示例 1 -- session1 (RC 隔离级别) begin; select * from lock_test where age=15 for share; -- 命中记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +-----------------+-------------+-------------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------+-------------+-------------------+-----------+---------------+-------------+-----------+ | 283810968510344 | lock_test | NULL | TABLE | IS | GRANTED | NULL | | 283810968510344 | lock_test | idx_lock_test_age | RECORD | S,REC_NOT_GAP | GRANTED | 15, 1 | | 283810968510344 | lock_test | idx_lock_test_age | RECORD | S,REC_NOT_GAP | GRANTED | 15, 5 | | 283810968510344 | lock_test | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 1 | | 283810968510344 | lock_test | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 5 | +-----------------+-------------+-------------------+-----------+---------------+-------------+-----------+ -- 示例 2 -- session1 (RC 隔离级别) begin; select * from lock_test where age=11 for share; -- 没有命中记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +-----------------+-------------+------------+-----------+-----------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------+-------------+------------+-----------+-----------+-------------+-----------+ | 283810968510344 | lock_test | NULL | TABLE | IS | GRANTED | NULL | +-----------------+-------------+------------+-----------+-----------+-------------+-----------+- age=15 命中到 2 条记录时，先加表级动向共享锁(IS)，再在非聚簇索引上加行级共享锁，同时也在对应的聚簇索引 / 主键索引上加行级共享锁!
- age=11 没有命中到记录时，只加表级动向共享锁(IS)
3.5、间隙锁(Gap Locks)
索引记录之间的间隙上的锁，锁定尚未存在的记录，即索引记录之间的间隙。有 shard 或 exclusive 两种模式，但两种模式没有任何区别，二者等价。
LOCK_MODE 别离是：S,GAP 或 X,GAP。
gap lock 能够共存(co-exist)。事务 T1 持有某个间隙上的 gap lock 并不能阻止事务 T2 同时持有同一个间隙上的 gap lock。shared gap lock 和 exclusive gap lock 并没有任何的不同，它俩并不抵触，它俩执行同样的性能。
gap lock 锁住的间隙能够是第一个索引记录后面的间隙，或相邻两条索引记录之间的间隙，或最初一个索引记录前面的间隙。
索引是 B + 树组织的，因而索引是从小到大按序排列的，在索引记录上查找给定记录时，InnoDB 会在第一个不满足查问条件的记录上加 gap lock，避免新的满足条件的记录插入。
举个例子(MySQL8.0 下)，表构造及数据同上：
-
select * from lock_test where id=xxx for update/share，走主键索引，在 RR 隔离级别下的加锁状况：-- 示例 1 -- session1 (RR 隔离级别) begin; select * from lock_test where id=5 for update; -- 命中惟一一条记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; -- 此种状况与 RC 隔离级别没有区别(只加了主键上的记录锁) +----------+-------------+------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+---------------+-------------+-----------+ | 9553 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9553 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 | +----------+-------------+------------+-----------+---------------+-------------+-----------+ -- 示例 2 -- session1 (RR 隔离级别) begin; select * from lock_test where id=5 for share; -- 命中惟一一条记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; -- 此种状况与 RC 隔离级别没有区别(只加了主键上的记录锁) +-----------------+-------------+------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------+-------------+------------+-----------+---------------+-------------+-----------+ | 283810968510344 | lock_test | NULL | TABLE | IS | GRANTED | NULL | | 283810968510344 | lock_test | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 5 | +-----------------+-------------+------------+-----------+---------------+-------------+-----------+ -
select * from lock_test where age=xxx for update，走一般索引，在 RR 隔离级别下的加锁状况：-- session1 (RR 隔离级别) begin; select * from lock_test where age=21 for update; -- 命中一条记录 -- 示例 1 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ | 9554 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9554 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 21, 10 | | 9554 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 | | 9554 | lock_test | idx_lock_test_age | RECORD | X,GAP | GRANTED | 23, 23 | +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ -- 示例 2 -- session3 (RR 隔离级别) -- 因为 18 处在 GAP 锁区间 (15,21) 中，所以上面 insert 语句将会阻塞(见上面锁信息第 2 行) insert into lock_test(name,age,created) values('fengqi',18,now()); -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+-------------------+-----------+------------------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+-------------------+-----------+------------------------+-------------+-----------+ | 9555 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9555 | lock_test | idx_lock_test_age | RECORD | X,GAP,INSERT_INTENTION | WAITING | 21, 10 | | 9554 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9554 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 21, 10 | | 9554 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 | | 9554 | lock_test | idx_lock_test_age | RECORD | X,GAP | GRANTED | 23, 23 | +----------+-------------+-------------------+-----------+------------------------+-------------+-----------+ -- 示例 3 -- session3 (RR 隔离级别) -- 因为 22 处在 GAP 锁区间 (21,23) 中，所以上面 insert 语句将会阻塞(见上面锁信息第 2 行) insert into lock_test(name,age,created) values('fengqi',22,now()); -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+-------------------+-----------+------------------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+-------------------+-----------+------------------------+-------------+-----------+ | 9556 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9556 | lock_test | idx_lock_test_age | RECORD | X,GAP,INSERT_INTENTION | WAITING | 23, 23 | | 9554 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9554 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 21, 10 | | 9554 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 | | 9554 | lock_test | idx_lock_test_age | RECORD | X,GAP | GRANTED | 23, 23 | +----------+-------------+-------------------+-----------+------------------------+-------------+-----------+ -- 示例 4 -- session3 (RR 隔离级别) -- age=15(头边界)并不在下面 GAP 锁或者记录锁中，所以上面语句不会产生阻塞 update lock_test set name=concat(name,'1') where age=15; -- 示例 5 -- session3 (RR 隔离级别) /** age=18 处在 GAP 锁区间 (15,21) 中，然而上面语句却不会产生阻塞，因为此时走了一般索引 idx_lock_test_age 而不是全表扫描，全表扫描会导致全表被锁(此时该 update 语句将阻塞)，走索引的话，先在索引上加锁，此时 age=18 并不存在，所以 MySQL 做了优化：对 update 影响行数为 0 的状况立马放行不阻塞，绝对于下面的 insert 则不同，insert 会立马影响表中数据，而 update 影响行数为 0 时不影响表中数据，两者产生的后果不一样。所以 insert(age=18)阻塞而 update(age=18)却不阻塞 **/ update lock_test set name=concat(name,'1') where age=18; -- 示例 6 -- session3 (RR 隔离级别) -- 上面 delete 语句不会阻塞，与下面示例 5 一个情理 delete from lock_test where age=18; -- 示例 7 -- session3 (RR 隔离级别) -- age=21 与下面的记录锁抵触，必定是阻塞的，这个没什么好说的 update lock_test set name=concat(name,'1') where age=21; -- 示例 8 -- session3 (RR 隔离级别) -- 与示例 4 相似，age=23(尾边界)并不在下面 GAP 锁或者记录锁中，所以上面语句不会产生阻塞 update lock_test set name=concat(name,'1') where age=23;- age=21 命中到 1 条记录时，先加表级动向排他锁 (IX)，再在非聚簇索引(idx_lock_test_age) 上加行级排他锁，接着在对应的聚簇索引 / 主键索引上加行级排他锁，接着在非聚簇索引 (idx_lock_test_age) 的闭区间 (15,21) 和(21,23)上加排他 GAP 锁
- 另开一个会话执行
insert into lock_test(name,age,created) values('fengqi',18,now())时，因为 age=18 处于 GAP 锁区间 (15,21) 中，所以该 insert 语句将会阻塞 - 另开一个会话执行
insert into lock_test(name,age,created) values('fengqi',22,now())时，因为 age=22 处于 GAP 锁区间 (21,23) 中，所以该 insert 语句将会阻塞 - 另开一个会话执行
update lock_test set name=concat(name,'1') where age=15时，因为 age=15 并不在下面 GAP 锁或者记录锁中，所以该 update 语句不会产生阻塞 - 另开一个会话执行
update lock_test set name=concat(name,'1') where age=18时，此时走了一般索引 idx_lock_test_age 而不是全表扫描，全表扫描会导致全表被锁 (此时该 update 语句将阻塞)，走索引的话，先在索引上加锁，此时 age=18 并不存在，所以 MySQL 做了优化：对 update 影响行数为 0 的状况立马放行不阻塞，绝对于下面的 insert 则不同，insert 会立马影响表中数据，而 update 影响行数为 0 时不影响表中数据，两者产生的后果不一样。所以 insert(age=18) 阻塞而 update(age=18)却不阻塞 - 另开一个会话执行
delete from lock_test where age=18时，该 delete 语句不会产生阻塞，与上一个示例一个情理，不再赘述 - 另开一个会话执行
update lock_test set name=concat(name,'1') where age=21时，因为 age=21 与下面的记录锁抵触了，所以该 update 语句必定阻塞，没什么好说的 - 另开一个会话执行
update lock_test set name=concat(name,'1') where age=23时，与示例 4 相似，age=23(尾边界)并不在下面 GAP 锁或者记录锁中，所以不会产生阻塞
-
select * from lock_test where name=xxx for update，全表扫描不走索引，在 RR 隔离级别下的加锁状况：where 条件不走索引加锁，这个是一个比拟恐怖场景，即走全表扫描，全表所有记录被上锁了，每条记录之间的间隙也被锁死了，整张表被锁死了。
-- session1 (RR 隔离级别) begin; select * from lock_test where name='wangwu' for update; -- 命中一条记录 -- 示例 1 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; -- 上面这个就代表全表被锁了(全表所有记录被上锁了，并且每条记录之间的间隙也被锁死了)，从 LOCK_DATA 能够看出走了全表扫描，间接在主键索引上加锁的 -- supremum pseudo-record 意思为主键索引上的无穷大值，但却不在索引中，相当于表中最初一行 (id 最大的) 之后的间隙锁 +----------+-------------+------------+-----------+-----------+-------------+------------------------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+-----------+-------------+------------------------+ | 9559 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 1 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 5 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 10 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 15 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 24 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 23 | +----------+-------------+------------+-----------+-----------+-------------+------------------------+ -- 示例 2 -- session3 (RR 隔离级别) -- 全表被锁，任何 insert 语句都将被阻塞 -- 在两头插入记录，该 insert 语句被阻塞，通过上面的查看锁状况语句可知，第二条 WAITING 记录代表该 insert(id=12) -- 与 GAP 区间 (10,15) 发送锁抵触而期待 insert into lock_test(id,name,age,created) values(12,'fengqi',18,now()); -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+------------------------+-------------+------------------------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+------------------------+-------------+------------------------+ | 9562 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9562 | lock_test | PRIMARY | RECORD | X,GAP,INSERT_INTENTION | WAITING | 15 | | 9559 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 1 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 5 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 10 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 15 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 24 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 23 | +----------+-------------+------------+-----------+------------------------+-------------+------------------------+ -- 示例 3 -- session3 (RR 隔离级别) -- 全表被锁，任何 insert 语句都将被阻塞 -- 在小端插入记录，该 insert 语句被阻塞，通过上面的查看锁状况语句可知，第二条 WAITING 记录代表该 insert(id=-2) -- 与 GAP 区间 (-∞,1) 发送锁抵触而期待 insert into lock_test(id,name,age,created) values(-2,'fengqi',18,now()); -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+------------------------+-------------+------------------------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+------------------------+-------------+------------------------+ | 9563 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9563 | lock_test | PRIMARY | RECORD | X,GAP,INSERT_INTENTION | WAITING | 1 | | 9559 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 1 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 5 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 10 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 15 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 24 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 23 | +----------+-------------+------------+-----------+------------------------+-------------+------------------------+ -- 示例 4 -- session3 (RR 隔离级别) -- 全表被锁，任何 insert 语句都将被阻塞 /** 在大端插入记录，该 insert 语句被阻塞，通过上面的查看锁状况语句可知，第二条 WAITING 记录代表该 insert(id 为自增主键的下一个值) 与 GAP 区间 (24,+∞) 发送锁抵触而期待，其中 GAP 区间 (24,+∞) 通过 supremum pseudo-record 来示意 **/ insert into lock_test(name,age,created) values('fengqi',18,now()); -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+--------------------+-------------+------------------------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+--------------------+-------------+------------------------+ | 9564 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9564 | lock_test | PRIMARY | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record | | 9559 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 1 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 5 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 10 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 15 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 24 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 23 | +----------+-------------+------------+-----------+--------------------+-------------+------------------------+ -- 示例 5 -- session3 (RR 隔离级别) /** 全表被锁，任何走非聚簇索引的 update/delete 且命中记录的语句都将被阻塞，没有命中记录的语句不阻塞，为什么？因为两者加锁对象不同：全表锁在主键索引上加的锁，update/delete 走非聚簇索引时先在非聚簇索引上加锁再在聚簇索引上加锁，非聚簇索引都没命中记录，那么聚簇索引上必定也不会命中记录，所以间接放行(不阻塞) **/ -- 上面 update 语句走一般索引，且命中记录了，被阻塞 update lock_test set name=concat(name,'1') where age=15; -- 该语句命中两条记录(id=1,5) -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; /** 查看锁情况表中第二行：该 update 语句走一般索引 idx_lock_test_age，所以存在 GAP 锁，区间(-∞,id=1&age=15)，留神多个雷同 GAP 锁之间不抵触，session1 也加了该区间的 GAP 锁 **/ -- 查看锁情况表中第三行：LOCK_DATA= 1 代表在主键索引上加记录锁时抵触了，该 update 语句命中两条记录，在给第一条记录 (id=1) 加锁时阻塞了 +----------+-------------+-------------------+-----------+---------------+-------------+------------------------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+-------------------+-----------+---------------+-------------+------------------------+ | 9565 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9565 | lock_test | idx_lock_test_age | RECORD | X,REC_NOT_GAP | GRANTED | 15, 1 | | 9565 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 1 | | 9559 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 1 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 5 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 10 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 15 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 24 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 23 | +----------+-------------+-------------------+-----------+---------------+-------------+------------------------+ -- 示例 6 -- session3 (RR 隔离级别) /** 全表被锁，任何走非聚簇索引的 update/delete 且命中记录的语句都将被阻塞，没有命中记录的语句不阻塞，为什么？因为两者加锁对象不同：全表锁在主键索引上加的锁，update/delete 走非聚簇索引时先在非聚簇索引上加锁再在聚簇索引上加锁，非聚簇索引都没命中记录，那么聚簇索引上必定也不会命中记录，所以间接放行(不阻塞) **/ -- 上面 update 语句走一般索引，且没有命中记录了，不阻塞 update lock_test set name=concat(name,'1') where age=24; -- 该语句没有命中任何记录 -- 示例 7 -- session3 (RR 隔离级别) -- 上面 update 语句走主键索引，且命中记录了，被阻塞 update lock_test set name=concat(name,'1') where id=10; -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; -- 查看锁情况表中第二行：该 update 语句走主键索引命中惟一条记录，LOCK_DATA=10 代表在主键索引上加记录锁时抵触了 +----------+-------------+------------+-----------+---------------+-------------+------------------------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+---------------+-------------+------------------------+ | 9566 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9566 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 10 | | 9559 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 1 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 5 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 10 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 15 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 24 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 23 | +----------+-------------+------------+-----------+---------------+-------------+------------------------+ -- 示例 8 -- session3 (RR 隔离级别) -- 上面 update 语句走主键索引，且没有命中记录，MySQL 做了优化：没有命中记录的 update/delete 语句对表中数据是没有任何影响的 --，没必要让其阻塞期待，间接放行，不阻塞 update lock_test set name=concat(name,'1') where id=8; -- 示例 9 -- session3 (RC 隔离级别) /** 全表被锁，任何不走索引的 update/delete 且命中记录的语句都将被阻塞，为什么？因为不走索引时走全表扫描，加锁时即扫描主键索引挨个程序加锁。上面 update 语句没有走索引也是全表扫描，从 id= 1 开始加锁，但因为以后是 RC 隔离级别，存在 semi-consistent read 优化，所以上面第二行 LOCK_DATA= 5 而不像上面示例 10 那样是 1 **/ update lock_test set name=concat(name,'1') where name='lisi'; -- 命中一条记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+---------------+-------------+------------------------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+---------------+-------------+------------------------+ | 9572 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9572 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 5 | | 9559 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 1 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 5 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 10 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 15 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 24 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 23 | +----------+-------------+------------+-----------+---------------+-------------+------------------------+ -- 示例 10 -- session3 (RR 隔离级别) /** 全表被锁，任何不走索引的 update/delete 且命中记录的语句都将被阻塞，为什么？因为不走索引时走全表扫描，加锁时即扫描主键索引挨个程序加锁。上面 update 语句没有走索引也是全表扫描，从 id= 1 开始加锁，因为以后是 RR 隔离级别，不存在 semi-consistent read 优化，所以上面第二行 LOCK_DATA= 1 而不像下面示例 9 那样 **/ update lock_test set name=concat(name,'1') where name='lisi'; -- 命中一条记录 -- session2 (查看锁状况) SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA FROM performance_schema.data_locks a; +----------+-------------+------------+-----------+---------------+-------------+------------------------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+------------+-----------+---------------+-------------+------------------------+ | 9573 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9573 | lock_test | PRIMARY | RECORD | X | WAITING | 1 | | 9559 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 1 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 5 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 10 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 15 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 24 | | 9559 | lock_test | PRIMARY | RECORD | X | GRANTED | 23 | +----------+-------------+------------+-----------+---------------+-------------+------------------------+ -- 示例 11 -- session3 (RC 隔离级别) /** 全表被锁，任何不走索引的 update/delete 且命中记录的语句都将被阻塞，为什么？因为不走索引时走全表扫描，加锁时即扫描主键索引挨个程序加锁。上面 update 语句没有走索引也是全表扫描，从 id= 1 开始加锁，但因为以后是 RC 隔离级别，存在 semi-consistent read 优化，因为没有命中记录，所以上面语句是不阻塞的 **/ update lock_test set name=concat(name,'1') where name='aaa'; -- 没有命中记录，不阻塞 -- 示例 12 -- session3 (RR 隔离级别) /** 全表被锁，任何不走索引的 update/delete 且命中记录的语句都将被阻塞，为什么？因为不走索引时走全表扫描，加锁时即扫描主键索引挨个程序加锁。上面 update 语句没有走索引也是全表扫描，从 id= 1 开始加锁，但因为以后是 RR 隔离级别，不存在 semi-consistent read 优化，那么得老老实实从 id= 1 开始扫描整个聚簇索引，扫描到 id= 1 时发现锁抵触，故产生阻塞 **/ update lock_test set name=concat(name,'1') where name='aaa'; -- 没有命中记录，阻塞总结：
- RR 隔离级别下，走聚簇索引 | 惟一索引的等值查问，只会加记录锁，不加 GAP 锁
- RR 隔离级别下，select for update/update/delete 等语句不走索引将会全表扫描，导致全表被锁
- 全表被锁即：锁住表中任何存在的记录(主键索引)、锁住相邻主键索引值之间的间隙、表中最小主键后面的间隙、表中最大主键前面的间隙
- 全表被锁，任何 insert 语句都将被阻塞
- 全表被锁，任何 update/delete 且命中记录的语句都将被阻塞
- 全表被锁，在 RC 隔离级别下，因为存在 semi-consistent read 优化，任何没有命中记录的 update/delete 的语句都将不会被阻塞
- 全表被锁，在 RR 隔离级别下，因为不存在 semi-consistent read 优化，任何没有命中记录的 update/delete 的语句也照样被阻塞，不存在优化
-
select * from lock_test where age = 15 AND date(created) = '2021-05-27' for update，走一般索引，在 RR 隔离级别下的加锁状况：-- session1 (RR 隔离级别) begin; select * from lock_test where age = 15 AND date(created) = '2021-05-27' for update; -- 命中 1 条记录 -- 示例 1 -- (查看锁状况) -- 很显著下面查问走一般索引 idx_lock_test_age，其加锁状况与 select * from lock_test where age = 15 for update 完全一致，见上面 +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ | 9573 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9573 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 15, 1 | | 9573 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 15, 5 | | 9573 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 | | 9573 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 | | 9573 | lock_test | idx_lock_test_age | RECORD | X,GAP | GRANTED | 21, 10 | +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ -- session1 (RR 隔离级别) begin; select * from lock_test where age = 15 and name = 'lisi' for update; -- 命中 1 条记录 -- (查看锁状况) 与下面完全一致 +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ | TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +----------+-------------+-------------------+-----------+---------------+-------------+-----------+ | 9574 | lock_test | NULL | TABLE | IX | GRANTED | NULL | | 9574 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 15, 1 | | 9574 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 15, 5 | | 9574 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 | | 9574 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 | | 9574 | lock_test | idx_lock_test_age | RECORD | X,GAP | GRANTED | 21, 10 | +----------+-------------+-------------------+-----------+---------------+-------------+-----------+- MySQL 的锁机制是基于索引，加不加锁看走了哪个索引，与其余 where 条件无关(诸如下面的 date(created) = ‘2021-05-27’)
3.6、插入意向锁(Insert Intention Locks)
一种非凡的 gap lock。INSERT 操作插入胜利后，会在新插入的行上设置 index record lock，但，在插入行之前，INSERT 操作会首先在索引记录之间的间隙上设置 insert intention lock，该锁的范畴是(插入值, 向下的一个索引值)。有 shard 或 exclusive 两种模式，但，两种模式没有任何区别，二者等价。
LOCK_MODE 别离是：S,GAP,INSERT_INTENTION 或 X,GAP,INSERT_INTENTION。
insert intention lock 收回按此形式进行插入的用意：多个事务向同一个 index gap 并发进行插入时，多个事务无需互相期待。
假如已存在值为 4 和 7 的索引记录，事务 T1 和 T2 各自尝试插入索引值 5 和 6，在失去被插入行上的 index record lock 前，俩事务都首先设置 insert intention lock，于是，T1 insert intention lock (5, 7)，T2 insert intention lock (6, 7)，只管这两个 insert intention lock 重叠了，T1 和 T2 并不相互阻塞。
如果 gap lock 或 next-key lock 与 insert intention lock 的范畴重叠了，则 gap lock 或 next-key lock 会阻塞 insert intention lock。隔离级别为 RR 时正是利用此个性来解决幻读问题；只管 insert intention lock 也是一种非凡的 gap lock，但它和一般的 gap lock 不同，insert intention lock 互相不会阻塞，这极大的提供了插入时的并发性。总结如下：
- gap lock 会阻塞 insert intention lock。事实上，gap lock 的存在只是为了阻塞 insert intention lock
- gap lock 互相不会阻塞
- insert intention lock 互相不会阻塞
- insert intention lock 也不会阻塞 gap lock
INSERT 插入行之前，首先在索引记录之间的间隙上设置 insert intention lock，操作插入胜利后，会在新插入的行上设置 index record lock，也就是在不产生锁抵触的状况下在霎时 LOCK_MODE 由 X,GAP,INSERT_INTENTION 变为 X,REC_NOT_GAP。
-- 示例 1
-- gap lock 会阻塞 insert intention lock
-- session1 (RR 隔离级别)
begin;
select * from lock_test where age=21 for update; -- 命中一条记录
-- session2 (RR/RC 隔离级别)
INSERT INTO lock_test VALUES (3, 'zhangsan', 16, '2021-05-26 18:28:02'); -- 插入记录，产生阻塞
-- session3 (查看锁状况)
SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA
FROM performance_schema.data_locks a;
+----------+-------------+-------------------+-----------+------------------------+-------------+-----------+
| TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+----------+-------------+-------------------+-----------+------------------------+-------------+-----------+
| 20493 | lock_test | NULL | TABLE | IX | GRANTED | NULL |
| 20493 | lock_test | idx_lock_test_age | RECORD | X,GAP,INSERT_INTENTION | WAITING | 21, 10 |
| 20492 | lock_test | NULL | TABLE | IX | GRANTED | NULL |
| 20492 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 21, 10 |
| 20492 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| 20492 | lock_test | idx_lock_test_age | RECORD | X,GAP | GRANTED | 23, 23 |
+----------+-------------+-------------------+-----------+------------------------+-------------+-----------+
-- 示例 2
-- session1 (RR 隔离级别)
begin;
INSERT INTO lock_test VALUES (3, 'zhangsan', 16, '2021-05-26 18:28:02'); -- 插入一条记录
+----+----------+-----+---------------------+
| id | name | age | created |
+----+----------+-----+---------------------+
| 1 | zhangsan | 15 | 2021-05-26 18:28:02 |
| 3 | zhangsan | 16 | 2021-05-26 18:28:02 |
| 5 | lisi | 15 | 2021-05-27 18:28:57 |
| 10 | wangwu | 21 | 2021-05-26 18:29:21 |
| 15 | zhaoliu | 35 | 2021-06-02 19:19:41 |
| 23 | hanjin | 23 | 2021-06-02 20:22:20 |
| 24 | hanjin | 25 | 2021-06-02 20:44:18 |
+----+----------+-----+---------------------+
-- insert intention lock 也不会阻塞 gap lock
-- session2 (RR 隔离级别)
begin;
select * from lock_test where age=21 for update; -- 命中一条记录，且不阻塞
-- session3 (查看锁状况)
SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA
FROM performance_schema.data_locks a;
+----------+-------------+-------------------+-----------+---------------+-------------+-----------+
| TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+----------+-------------+-------------------+-----------+---------------+-------------+-----------+
| 20495 | lock_test | NULL | TABLE | IX | GRANTED | NULL |
| 20495 | lock_test | idx_lock_test_age | RECORD | X | GRANTED | 21, 10 |
| 20495 | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| 20495 | lock_test | idx_lock_test_age | RECORD | X,GAP | GRANTED | 23, 23 |
| 20494 | lock_test | NULL | TABLE | IX | GRANTED | NULL |
+----------+-------------+-------------------+-----------+---------------+-------------+-----------+
-- session4 (RR/RC 隔离级别)
-- 这里为啥不阻塞? 因为 session1 曾经插入一条 (id=3,age=16) 的进入表中，紧接着 session2 加了 GAP 锁，这个 GAP 锁的区间是
-- (age=16,age=23)这个区间而不是之前咱们所通晓的(age=15,age=23)，这时上面 insert 语句不阻塞
INSERT INTO lock_test VALUES (2, 'zhangsan', 15, '2021-05-26 18:28:02'); -- 插入一条记录，且不阻塞
-- 插入一条 age=16 时，与下面 GAP 区间抵触，这时上面 insert 语句阻塞
INSERT INTO lock_test VALUES (28, 'zhangsan', 16, '2021-05-26 18:28:02'); -- 插入一条记录，阻塞
-- 示例 3
-- insert intention lock 互相不会阻塞
-- session1 (RR 隔离级别)
begin;
INSERT INTO lock_test VALUES (2, 'zhangsan', 16, '2021-05-26 18:28:02'); -- 插入一条记录, 不阻塞
-- session2 (RR 隔离级别)
begin;
INSERT INTO lock_test VALUES (3, 'zhangsan', 16, '2021-05-26 18:28:02'); -- 插入一条记录, 不阻塞
-- session3 (查看锁状况)
SELECT a.ENGINE_TRANSACTION_ID TRANS_ID, a.OBJECT_NAME, a.INDEX_NAME, a.LOCK_TYPE, a.LOCK_MODE, a.LOCK_STATUS, a.LOCK_DATA
FROM performance_schema.data_locks a;
+----------+-------------+------------+-----------+-----------+-------------+-----------+
| TRANS_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+----------+-------------+------------+-----------+-----------+-------------+-----------+
| 20509 | lock_test | NULL | TABLE | IX | GRANTED | NULL |
| 20504 | lock_test | NULL | TABLE | IX | GRANTED | NULL |
+----------+-------------+------------+-----------+-----------+-------------+-----------+3.7、自增锁(AUTO-INC)
AUTO-INC 锁是当向应用含有 AUTO_INCREMENT 列的表中插入数据时须要获取的一种非凡的表级锁
在最简略的状况下，如果一个事务正在向表中插入记录，则任何其余事务必须期待对该表执行本人的插入操作，以便使第一个事务插入的行的值是间断的。
innodb_autoinc_lock_mode 这个参数管制着在向有 auto_increment 列的表插入数据时，相干锁的行为；
通过对它的设置能够达到性能与平安 (主从的数据一致性) 的均衡。
咱们先对 insert 做一下分类
首先 insert 大抵上能够分成三类：
- simple insert 如 insert into t(name) values(‘test’)
- bulk insert 如 load data | insert into … select …. from ….
- mixed insert 如 insert into t(id,name) values(1,’a’),(null,’b’),(5,’c’)
innodb_autoinc_lock_mode 的阐明
innodb_autoinc_lock_mode 有三个取值：
-
tradition 传统(innodb_autoinc_lock_mode=0) 模式:
- 它提供了一个向后兼容的能力
- 在这一模式下，所有的 insert 语句(“insert like”) 都要在语句开始的时候失去一个表级的 auto_inc 锁，在语句完结的时候才开释这把锁，留神呀，这里说的是语句级而不是事务级的，一个事务可能蕴含一个或多个语句
- 它能保障值调配的可预见性，与连续性，可重复性，这个也就保障了 insert 语句在复制到 slave 的时候还能生成和 master 那边一样的值(它保障了基于语句复制 binlog_format=STATEMENT 的平安)
- 因为在这种模式下 auto_inc 锁始终要放弃到语句的完结，所以这个就影响到了并发的插入，并发性能最差
-
consecutive 间断(innodb_autoinc_lock_mode=1) 模式:
- 这一模式下 simple insert 做了优化，因为 simple insert 一次性插入值的个数能够立马失去 确定，所以 mysql 能够一次生成几个间断的值，用于这个 insert 语句；总的来说这个对复制也是平安的(它保障了基于语句复制的平安)
- 这一模式也是 mysql-5.7.7 之前的默认模式，这个模式的益处是 auto_inc 锁不肯定会始终放弃到语句的完结，只有语句失去了相应的值后就能够提前开释锁
- 这种模式是可预判行数时应用新形式，不可确定 bulk insert 数量时则降级应用表锁跟 tradition 模式一样
-
interleaved 交织(innodb_autoinc_lock_mode=2) 模式
- 因为这个模式下曾经没有了 auto_inc 锁，所以这个模式下的性能是最好的；然而它也有一个问题，就是对于同一个语句来说它所失去的 auto_incremant 值可能不是间断的
总结：
- 如果你的二进制文件格式是 mixed | row 那么这三个值中的任何一个对于你来说都是主从复制平安的
- innodb row 复制时，可将 innodb_autoinc_lock_mode 设置为 2，这时可在所有 insert 状况下表取得最大并发度
- innodb statement 复制时，可将 innodb_autoinc_lock_mode 设置为 1，保障复制平安的同时，取得简略 insert 语句的最大并发度
- myisam 引擎状况下，无论什么样自增 id 锁都是表级锁，设置 innodb_autoinc_lock_mode 参数有效
- 实际上提问者说到的在 innodb 引擎下自增 id 值作为主键的状况下，相比 uuid 或者自定义的主键，是能够进步插入速度的，因为 innodb 是主键汇集索引，理论的主键值必须依照主键程序存取，那么自增 id 自身就是升序的，那么在插入数据时，底层就不用再做额定的排序操作，也缩小了索引页决裂的次数，从而大大增加 insert 速度（除非其余计划也能保障主键齐全自增）
- innodb_autoinc_lock_mode 的默认值随着 binlog_format 的默认值扭转而扭转：binlog_format 在 5.7.7 之前默认值为 STATEMENT，此时 innodb_autoinc_lock_mode 默认为 1；5.7.7 之后为 ROW，此时 innodb_autoinc_lock_mode 默认为 2
3.8、semi-consistent read
semi-consistent read（半一致性读）是什么
简略来说，semi-consistent read 是 read committed 与 consistent read 两者的联合。一个 update 语句 (semi-consistent read 只针对 update)，如果读到一行曾经加锁的记录，此时 InnoDB 返回记录最近提交的版本，由 MySQL 下层(Server 层) 判断此版本是否满足 update 的 where 条件。若满足(须要更新)，则 MySQL 会从新发动一次读操作，此时会读取行的最新版本(并加锁)。(对于 update scan 返回的不满足条件的记录，会提前放锁)
应用限度
- semi-consistent read 只会产生在 RC 隔离级别下。
- 或者是在 RR 隔离级别下且参数 innodb_locks_unsafe_for_binlog 被设置为 true，这时候就相当于 RR 级别降为 RC。
- semi-consistent read 只实用于对聚簇索引记录加锁的状况，并不适用于对二级索引记录加锁的状况。
- innodb_locks_unsafe_for_binlog 参数会导致主从复制 binlog 不平安，从 MySQL8.0 开始被移除了，也就是说在 8.0 下的 RR 隔离级别下是不存在 semi-consistent read。
长处
- 缩小了更新同一行记录时的抵触，缩小锁期待。无并发抵触，读记录最新版本并加锁；有并发抵触，读事务最新的 commit 版本，不加锁，无需锁期待。
- 能够提前放锁，进一步缩小并发抵触概率。对于不满足 update 更新条件的记录，能够提前放锁，缩小并发抵触的概率。
- 在了解了 semi-consistent read 原理及实现计划的根底上，能够酌情思考应用 semi-consistent read，进步零碎的并发性能。
毛病
- 非抵触串行化策略，因而对于主从复制 binlog 来说，是不平安的。为什么对于 binlog 来说是不平安的这里又要提到下面所说的应用限度：RC 级别下或者 RR 级别下 innodb_locks_unsafe_for_binlog 为 true。如果 innodb_locks_unsafe_for_binlog=true，就相当于禁止了 gap 锁。粗略的能够了解为从 RR 降为 RC 隔离级别，毕竟 RR 与 RC 最大的不同在于 gap 锁(避免幻读)。
4、死锁例子
4.1、多条语句穿插执行导致的死锁
这种类型的死锁非常好了解，跟各种语言中的死锁基本一致，即线程 1 和线程 2 都须要获取 A、B 两把锁，然而他们获取锁的程序相同：线程 1 先获取 A、再获取 B，而线程 2 先获取 B 再获取 A，两者获取锁的程序相同产生互相期待的状况，产生了死锁，在 Java 语言中就有自检测这种死锁的机制，JVM 堆栈会报发现 deadlock 异样，同样 MySQL 也会有这样的自检测机制，一旦呈现死锁，引擎就会报 deadlock 异样。上面是一个这样的典型例子：这是一个因为争抢两个 Gap 锁导致的死锁
数据还是下面的表和数据：
CREATE TABLE lock_test (id BIGINT(19) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
age TINYINT(3) NOT NULL DEFAULT '0',
created DATETIME NOT NULL,
PRIMARY KEY (id),
INDEX idx_lock_test_age (age)
) ENGINE=InnoDB;
INSERT INTO lock_test VALUES (1, 'zhangsan', 15, '2021-05-26 18:28:02');
INSERT INTO lock_test VALUES (5, 'lisi', 15, '2021-05-27 18:28:57');
INSERT INTO lock_test VALUES (10, 'wangwu', 21, '2021-05-26 18:29:21');
INSERT INTO lock_test VALUES (15, 'zhaoliu', 35, '2021-06-02 19:19:41');
INSERT INTO lock_test VALUES (23, 'hanjin', 23, '2021-06-02 20:22:20');
INSERT INTO lock_test VALUES (24, 'hanjin', 25, '2021-06-02 20:44:18');
+----+----------+-----+---------------------+
| id | name | age | created |
+----+----------+-----+---------------------+
| 1 | zhangsan | 15 | 2021-05-26 18:28:02 |
| 5 | lisi | 15 | 2021-05-27 18:28:57 |
| 10 | wangwu | 21 | 2021-05-26 18:29:21 |
| 15 | zhaoliu | 35 | 2021-06-02 19:19:41 |
| 23 | hanjin | 23 | 2021-06-02 20:22:20 |
| 24 | hanjin | 25 | 2021-06-02 20:44:18 |
+----+----------+-----+---------------------+-- session1 (RR 隔离级别)
begin;
-- 为结构死锁，咱们先锁住 GAP(1,5)
update lock_test set name = concat(name, '1') where id = 4; -- 不阻塞，且命中 0 条记录
-- session2 (RR 隔离级别)
begin;
-- 为结构死锁，咱们再锁住 GAP(5,10)
update lock_test set name = concat(name, '1') where id = 6; -- 不阻塞，且命中 0 条记录
-- session1 (RR 隔离级别)
-- 为结构死锁，咱们向 session2 的 GAP(5,10)中插入数据
-- 上面产生 Deadlock ERROR 之后，该语句继续执行并胜利插入库中
INSERT INTO lock_test VALUES (7, 'asan', 16, '2021-05-26 18:28:02'); -- 阻塞住了
-- session2 (RR 隔离级别)
-- 为结构死锁，咱们向 session1 的 GAP(1,5)中插入数据
INSERT INTO lock_test VALUES (3, 'asan', 18, '2021-05-26 18:28:02'); -- 此时发送了死锁
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction4.2、单条语句因为应用不同的非聚簇索引，导致在加锁聚簇索引时程序相同造成的死锁
这种状况产生死锁的起因很荫蔽，要是不晓得原理，很难说出个所以然，纵使搜遍百度可能也毫无脉络。
例如，现有表和数据如下：
CREATE TABLE t_news (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
title VARCHAR(128) NOT NULL COMMENT '新闻标题',
ups INT NOT NULL DEFAULT 0 COMMENT '顶数量',
downs INT NOT NULL DEFAULT 0 COMMENT '踩数量',
comments INT NOT NULL DEFAULT 0 COMMENT '评论数量',
created DATETIME NOT NULL COMMENT '创立工夫',
PRIMARY KEY (id),
KEY idx_news_ups(ups),
KEY idx_news_downs(downs)
) COMMENT '新闻表';
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 1', 1, 1000, 0, now());
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 2', 2, 999, 0, now());
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 3', 3, 998, 0, now());
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 4', 4, 997, 0, now());
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 5', 5, 996, 0, now());
...
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 996', 996, 5, 0, now());
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 997', 997, 4, 0, now());
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 998', 998, 3, 0, now());
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 999', 999, 2, 0, now());
INSERT INTO t_news(title, ups, downs, comments, created) VALUES ('热门新闻 1000', 1000, 1, 0, now());-- session1 (RC 隔离级别)
begin;
-- 通过 idx_news_ups 索引来进行更新，此时只存在记录锁，先在 idx_news_ups 索引上加锁(都能胜利, 无阻塞产生)，-- 等到在主键索引上加锁时，因为与 idx_news_downs 索引加锁程序相同，而导致死锁(严格说是有可能)
-- 请确保上面语句走了 idx_news_ups 索引
UPDATE t_news a SET a.comments = a.comments + 1 WHERE a.ups >= 400 AND a.ups <= 600;
-- session2 (RC 隔离级别)
begin;
-- 通过 idx_news_downs 索引来进行更新，此时只存在记录锁，先在 idx_news_downs 索引上加锁(都能胜利, 无阻塞产生)，-- 等到在主键索引上加锁时，因为与 idx_news_ups 索引加锁程序相同，而导致死锁(严格说是有可能)
-- 请确保上面语句走了 idx_news_downs 索引
UPDATE t_news a SET a.comments = a.comments + 1 WHERE a.downs >= 400 AND a.downs <= 600;
-- 上述两个会话只能说是有可能，与数据量无关，因为数据量小的话 SQL 执行太快可能模仿不进去，而且必须在代码中能力模仿进去，因为命令行下近乎同时操作两个窗口手速达不到
-- 当合乎两个语句 where 条件的交加很大时基本上都会发送死锁模仿该死锁的 JAVA 代码：
@Service
public class NewsService {
private final JdbcTemplate jdbcTemplate;
public NewsService(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}
@Transactional(rollbackFor=Exception.class)
public void initNews() {
String sql = "DELETE FROM t_news WHERE 1=1";
jdbcTemplate.update(sql);
sql = "INSERT INTO t_news(title, ups, downs, comments, created) VALUES (?, ?, ?, ?, ?)";
int total = 1000;
for(int i = 1; i <= total; i++) {
String title = "热门新闻" + i;
int ups = i;
int downs = total - i + 1;
int comments = 0;
String created = LocalDateTime.now().toString();
jdbcTemplate.update(sql, title, ups, downs, comments, created);
}
}
@Transactional(rollbackFor=Exception.class, isolation=Isolation.READ_COMMITTED)
public void incrementCommentsByUps() {
// 须要通过 EXPLAIN 确认一下上面 SQL 的确走了 idx_news_ups 索引
String sql = "UPDATE t_news a SET a.comments = a.comments + 1 WHERE a.ups >= 400 AND a.ups <= 600";
jdbcTemplate.update(sql);
}
@Transactional(rollbackFor=Exception.class, isolation=Isolation.READ_COMMITTED)
public void incrementCommentsByDowns() {
// 须要通过 EXPLAIN 确认一下上面 SQL 的确走了 idx_news_downs 索引
String sql = "UPDATE t_news a SET a.comments = a.comments + 1 WHERE a.downs >= 400 AND a.downs <= 600";
jdbcTemplate.update(sql);
}
}
@SpringBootTest(classes=DeadlockExampleApplication.class)
public class DeadlockExampleTest {@Resource(name="newsService")
private NewsService newsService;
@Test
public void initNews() {newsService.initNews();
}
@Test
public void tryDeadlock() throws Exception {
boolean continued = true;
while(continued) {continued = incrementComments();
}
System.out.println("deadlock found!");
}
protected boolean incrementComments() throws Exception {CompletableFuture<Boolean> async1 = CompletableFuture.supplyAsync(() -> {
try {newsService.incrementCommentsByUps();
return true;
} catch (Exception e) {e.printStackTrace();
return false;
}
});
CompletableFuture<Boolean> async2 = CompletableFuture.supplyAsync(() -> {
try {newsService.incrementCommentsByDowns();
return true;
} catch (Exception e) {e.printStackTrace();
return false;
}
});
CompletableFuture<Void> allAsync = CompletableFuture.allOf(async1, async2);
allAsync.join(); // 期待 async1、async2 都实现了
return async1.get() && async2.get();
}
}参考链接
MySQL 加锁剖析 1
MySQL 加锁剖析 2
正文完
