正文开始
"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)的兼容性矩阵如下：
| 表级锁(table-level lock)的兼容性如下 | ||||
|---|---|---|---|---|
| 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是表的(不是行的)排它锁。
- 意向锁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
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。