注释开始
"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代码：
@Servicepublic 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