1. Record LockRecord
Lock 也就是我们所说的记录锁，记录锁是对索引记录的锁，注意，它是针对索引记录，即它只锁定记录这一行数据
1.将系统变量 innodb_status_output_locks 设置为 默认为OFF
mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output_locks | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)，
mysql> set global innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output_locks | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
2.执行如下 SQL，锁定一行数据，此时会自动为表加上 IX 锁：
mysql> begin; #开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city where id=1 for update;
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)
mysql> show engine innodb status\G; #查看 InnoDB 存储引擎的情况
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-05-07 19:07:18 139841320953600 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 1284 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1
OS WAIT ARRAY INFO: signal count 1
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS #主要关注这里的TRANSACTIONS
------------
Trx id counter 18190
Purge done for trx's n:o < 18183 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421316317542832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421316317542024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 18189, ACTIVE 4 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 8, OS thread handle 139841320953600, query id 25 localhost root starting
show engine innodb status
TABLE LOCK table `world`.`city` trx id 18189 lock mode IX **************
RECORD LOCKS space id 5 page no 6 n bits 248 index PRIMARY of table `world`.`city` trx id 18189 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 ********
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000430e; asc C ;;
2: len 7; hex 02000001140151; asc Q;;
3: len 30; hex 4b6162756c20202020202020202020202020202020202020202020202020; asc Kabul ; (total 35 bytes);
4: len 3; hex 414647; asc AFG;;
5: len 20; hex 4b61626f6c202020202020202020202020202020; asc Kabol ;;
6: len 4; hex 800003e8; asc ;;
--------
TABLE LOCK table `world`.`city` trx id 18189 lock mode IX
这句就是说事务 id 为 18189 的事务，为 city 表添加了意向排他锁(IX)。
RECORD LOCKS space id 5 page no 6 n bits 248 index PRIMARY of table `world`.`city` trx id 18189 lock_mode X locks rec but not gap
Record lock, 这句就是一个锁结构的记录，这里的索引是 PRIMARY，加的锁也是正儿八经的记录锁(not gap)。
LOCKS REC BUT NOT GAP，有为句就说明这是一个记录锁
Record Lock 和我们之前所讲的 S 锁以及 X 锁有什么区别呢?S 锁是共享锁，X 锁是排他锁，当我们加 S 锁或者 X 锁的时候，如果用到了索引，锁加在了某一条具体的记录上，那么这个锁也是一个记录锁(其实，记录锁，S 锁，X 锁，概念有一些重复的地方，但是描述的重点不一样)。
2. Gap Lock
Gap Lock 也叫做间隙锁，它的存在可以解决幻读问题，另外需要注意，Gap Lock 也只在 REPEATABLE READ 隔离级别下有效。先来看看什么是幻读，我们来看如下一个表格：
有两个会话，A 和 B，先在会话 A 中开启事务，然后查询 age 为 99 的用户总数，注意使用当前读，因为在默认的隔离级别下，默认的快照读并不能读到其他事务提交的数据，至于快照读和当前读的区别。当会话 A 中第一次查询过后，会话 B 中向数据库添加了一行记录，等到会话 A 中第二次查询的时候，就查到了和第一次查询不一样的结果，这就是幻读(注意幻读专指数据插入引起的不一致)。
在 MySQL 8.0默认的隔离级别 REPEATABLE READ 下，上图所描述的情况无法复现。无法复现的原因在于，在 MySQL 的 REPEATABLE READ 隔离级别中，它已经帮我们解决了幻读问题，解决的方案就是 Gap Lock。
之所以出现幻读的问题，是因为记录之间存在缝隙，用户可以往这些缝隙中插入数据，这就导致了幻读问题，如下图：
id 之间有缝隙，有缝隙就有漏洞。前面我们所说的记录锁只能锁住一条具体的记录，但是对于记录之间的空隙却无能无力，这就导致了幻读(其他事务可往缝隙中插入数据)。现在 Gap Lock 间隙锁，就是要把这些记录之间的间隙也给锁住，间隙锁住了，就不用担心幻读问题了，这也是 Gap Lock 存在的意义。
给一条记录加 Gap Lock，是锁住了这条记录前面的空隙，例如给 id 为 1 的记录加 Gap Lock，锁住的范围是 (-∞,1)，给 id 为 3 的记录加 Gap Lock，锁住的范围是 (1,3)，那么 id 为 10 后面的空隙怎么锁定呢?MySQL 提供了一个 Supremum 表示当前页面中的最大记录，所以最后针对 Supremum 锁住的范围就是 (10,+∞)，这样，所有的间隙都被覆盖到了，由于锁定的是间隙，所以都是开区间。
简单的例子：
1.创建一个表：
mysql> CREATE TABLE `T4` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
-> `age` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `age` (`age`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
插入以下数据：
mysql> insert into T4 values('1','xiaoli',99);
Query OK, 1 row affected (0.00 sec)
mysql> insert into T4 values('2','xiaoming',89);
Query OK, 1 row affected (0.00 sec)
mysql> insert into T4 values('3','xiaoliu',79);
Query OK, 1 row affected (0.00 sec)
mysql> select * from T4;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | xiaoli | 99 |
| 2 | xiaoming | 89 |
| 3 | xiaoliu | 79 |
+----+----------+------+
3 rows in set (0.00 sec)
2.执行如下 SQL，锁定一行数据，此时也会产生间隙锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from T4 force index(age) where age=89 for update;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 2 | xiaoming | 89 |
+----+----------+------+
1 row in set (0.00 sec)
3.查看innodb引擎状况
mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-05-07 19:56:28 139841320953600 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6 srv_active, 0 srv_shutdown, 4210 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3
OS WAIT ARRAY INFO: signal count 3
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 18208
Purge done for trx's n:o < 18205 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421316317542832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421316317542024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 18207, ACTIVE 58 sec
4 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 8, OS thread handle 139841320953600, query id 37 localhost root starting
show engine innodb status
TABLE LOCK table `world`.`T4` trx id 18207 lock mode IX
RECORD LOCKS space id 8 page no 5 n bits 72 index age of table `world`.`T4` trx id 18207 lock_mode X ****记录锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000059; asc Y;;
1: len 4; hex 00000002; asc ;;
RECORD LOCKS space id 8 page no 4 n bits 72 index PRIMARY of table `world`.`T4` trx id 18207 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 *****间隙锁
一个间隙锁的加锁记录，可以看到，在某一个记录之前加了间隙锁。这就是间隙锁。
非常重要注意：Gap Lock 只在 REPEATABLE READ 隔离级别下有效。
3. Next-Key Lock
既想锁定一行，又想锁定行之间的记录，那么就是 Next-Key Lock 了，换言之，Next-Key Lock 是 Record Lock 和 Gap Lock 的结合体。
Next-Key Lock 的加锁规则： 锁的范围是左开右闭。 如果是唯一非空索引的等值查询，Next-Key Lock 会退化成 Record Lock。 普通索引上的等值查询，向后遍历时，最后一个不满足等值条件的时候，Next-Key Lock 会退化成 Gap Lock。
举例:
创建一个学生表：
mysql> CREATE TABLE `student` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
-> `score` double NOT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `score` (`score`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected, 1 warning (0.02 sec)
插入数据：
mysql> insert into student values ('1','a','90');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values ('2','b','89');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values ('3','c','95');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values ('4','d','80');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values ('5','e','79');
Query OK, 1 row affected (0.01 sec)
查看数据：
mysql> select * from student;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | a | 90 |
| 2 | b | 89 |
| 3 | c | 95 |
| 4 | d | 80 |
| 5 | e | 79 |
+----+------+-------+
5 rows in set (0.00 sec)
执行以下SQL：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student force index(score) where score=90 for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | a | 90 |
+----+------+-------+
1 row in set (0.00 sec)
查看引擎状况：
mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-05-07 20:14:25 139841320953600 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12 srv_active, 0 srv_shutdown, 5274 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9
OS WAIT ARRAY INFO: signal count 9
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 18231
Purge done for trx's n:o < 18229 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421316317542832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421316317542024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 18230, ACTIVE 51 sec
3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 8, OS thread handle 139841320953600, query id 49 localhost root starting
show engine innodb status
TABLE LOCK table `world`.`student` trx id 18230 lock mode IX ****************
RECORD LOCKS space id 9 page no 5 n bits 72 index score of table `world`.`student` trx id 18230 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 **************************
由于 score 是唯一非空索引，所以 Next-Key Lock 会退化成 Record Lock，换句话说，这行 SQL 只给 score 为 90 的记录加锁，不存在 Gap Lock，即我们新开一个会话，插入一条 score 为 88 的记录也是 OK 的。
mysql> insert into student values ('6','f','88');
Query OK, 1 row affected (0.00 sec)
由于并不存在 score 为 91 的记录，所以这里会产生一个范围为 (90,95) 的间隙锁，我们执行如下 SQL 可以验证：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student force index(score) where score=91 for update;
Empty set (0.00 sec)
另外一个会话中插入一个90.1的score，出现间隙锁（90~95），90~95数据都添加不进去。
mysql> insert into student values ('7','g','90.1');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into student values ('8','h','90');
ERROR 1062 (23000): Duplicate entry '90' for key 'student.score'
mysql> insert into student values ('9','j','94.9');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into student values ('10','l','95.1'); ********插入95以上数据可以
Query OK, 1 row affected (0.00 sec)
总结：
可以看到，90.1、94.9 都会被阻塞(我按了 Ctrl C，所以大家看到查询终止)。
90、95 则不符合唯一非空索引的条件。
95.1 则可以插入成功
参考文献：
https://www.51cto.com/article/707803.html