MySQL——SQL加锁机制简要分析
前提：针对于InnoDB引擎 行锁讨论
锁机制
锁机制
MySQL的锁机制可以分为：锁模型(lock mode)和锁类型(lock type)
锁模型(lock mode)
共享锁&排他锁
InnoDB 实现了两种类型的标准行锁：共享(S)锁和排他(X)锁。（下文简称S锁和X锁）
- S锁允许持有该锁的事务读取一行记录，可以同时有多个事务对记录加S锁；
- X锁允许持有该锁的事务更新或删除一行记录，同一时间只能有一个事务加X锁；
意象锁
注意： nnoDB支持 多粒度锁 ：即行锁与表锁共存。例如语句 LOCK TABLES ... WRITE 获取表的X锁。InnoDB使用 意向锁 实现在多个粒度
上加锁。意向锁是表锁，用于指明一个事务稍后要获取哪种类型的行锁（S or X）。意向锁有两种类型：
- 共享意向锁（IS）：指明事务将要获取行的共享锁
- 独占意向锁（IX）：指明事务将要获取行的独占锁
例如， SELECT ... FOR SHARE 获取了 IS 锁 ， SELECT ... FOR UPDATE 获取了 IX 锁。
自增锁
LOCK AUTONIC:自增锁 简称AI锁，当插入数据且表中有自增列时，会在生成自增值前为该表加AI锁
锁类型
锁类型可以分为表锁和行锁。表锁会对整张表加锁，有MYSQL服务器实现。行锁会锁住某一行，某几行或者行之间的间隙，由存储引擎实现，比如innodb
简约介绍几种简单的行锁
记录锁(Record Lock) 单个行记录下的锁，会锁住索引信息。如果没有设置索引会使用隐式的主键来锁定；
间隙锁(Gap Lock) 加在两个索引之间或第一个索引之前，或者最后一个索引之后。并不锁定索引本身;
在可重复读隔离级别下才会产生间隙锁, 它可以防止其他事物在加锁范围内插入或修改记录，保证两次读取加锁范围内的记录数据不变，避免幻读发生;
产生间隙锁的场景：
使用普通索引
使用多列唯一索引
使用唯一索引
注意：在上述场景中进行范围查找或者索引列记录不存在，会产生间隙锁
Next-key 锁(Next-key locks) 可以认为是记录锁和间隙锁的组合，即锁定范围，又锁定索引记录本身
插入意向锁(Insert Intention locks) 是一种特殊的意向锁，与上文中提到的读，写意象锁是两个不通的概念，只会在Insert 时才会有这个锁。
由下文的行锁兼容矩阵， 在持有间隙锁或 Next-key 锁时，请求插入意向锁会有冲突。因为间隙锁的作用就是防止幻读，而正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行，进而避免了幻读的产生。
规则
在解读死锁日志、找寻死锁发生原因前，先了解几种行锁对应的死锁日志描述：
记录锁（LOCK RECNOT GAP）: lock mode X locks rec but not gap
间隙锁（LOCK GAP）: lock mode X locks gap before rec
Next-key 锁（LOCKORNIDARY）: lock mode X
插入意向锁（LOCK INSERT INTENTION）: lock_mode X locks gap before rec insert intention
行锁兼容矩阵（横向是已持有锁，纵向是正在请求的锁）
图片加载失败https://media.teamshub.com/10000/tm/2022/01/21/topic/fbb1abd6-49b2-483e-b823-7074d18a9c49.png
测试前提
1、开启lock monitor
mysql> show variables like '%innodb_status_output%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
+----------------------------+-------+
2 rows in set (0.00 sec)
开启lock monitor
mysql> set global innodb_status_output=on;
mysql> set global innodb_status_output_locks=on;
2、准备数据
## 数据库版本：8.0.24
DROP TABLE IF EXISTS `tb2`;
DROP TABLE IF EXISTS `tb2`;
CREATE TABLE `tb2` (
`id` int(11) NOT NULL,
`c` int(11) NULL DEFAULT NULL,
`u` int(11) NULL DEFAULT NULL,
`n` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_u_unique`(`u`) USING BTREE,
INDEX `idx_n_normal`(`n`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `tb2` VALUES (10, 11, 12, 13);
INSERT INTO `tb2` VALUES (20, 21, 22, 23);
INSERT INTO `tb2` VALUES (30, 31, 32, 33);
----------------------------------测试过程省略(太多了)---------------------------------
测试过程中可以通过：
mysql> show engine innodb status\G; 查看加锁情况
或者
mysql> select * from performance_schema.data_locks\G; 查看加锁情况
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF |
+----------------------------+-------+
注意：8.0.24版本没有 information_schema.innodb_lock_waits和information_schema.innodb_locks表了
结论：
RR模式下：
| 等值select for update/delete/update使用索引类型 | 锁定内容 |
| 主键(聚簇索引) | 对聚簇索引记录+Record Lock |
| 唯一索引 |
对辅助索引记录+Record Lock
对聚簇索引记录+Record Lock
|
| 普通索引 |
对相关索引记录+Next-Key Lock(该条记录的前后范围均会被锁住，比如c=23,(13,23],(23,33]均会加锁)
对聚簇索引记录+Record Lock
|
| 不使用索引 | 对聚簇索引全表+ Next-Key Lock |
| 范围select for/update/delete使用索引类型 | 锁定内容 |
| 主键(聚簇索引) |
对聚簇索引记录+Record Lock+Next-Key Lock >= =<
或者
对聚簇索引记录+Next-Key Lock > <
|
| 唯一索引 |
对辅助索引记录+Next-Key Lock (该条记录的前后范围均会被锁住，比如u>=4222 and u<=4226 ,(4220 ,4222 ],,(4222,4223],(4223,4226],(4226,4227)均会加锁)
对聚簇索引记录+Record Lock
|
| 普通索引 |
对相关索引记录+Next-Key Lock( 该条记录的前后范围均会被锁住c>43 and c<=433, (43,433],(433，正无穷)均会被锁，而(33,43)这个范围不会被锁，如果c>=43 and c<=433, (33,43]会被锁 )
对聚簇索引记录+Record Lock
|
| 不使用索引 | 对聚簇索引全表+ Next-Key Lock |
RC模式下：
| 等值select for update/delete/update使用索引类型 | 锁定内容 |
| 主键(聚簇索引) | 对聚簇索引记录+Record Lock |
| 唯一索引 |
对辅助索引记录+Record Lock
对聚簇索引记录+Record Lock
|
| 普通索引 |
对相关索引记录+Recode Lock
对聚簇索引记录+Record Lock
|
| 不使用索引 |
对聚簇索引全表+Recode Lock
|
| 范围select for/update/delete使用索引类型 | 锁定内容 |
| 主键(聚簇索引) | 对聚簇索引记录+Record Lock |
| 唯一索引 |
对辅助索引记录+Record Lock
对聚簇索引记录+Record Lock
|
| 普通索引 |
对相关索引记录+Record Lock
对聚簇索引记录+Record Lock
|
| 不使用索引 | 对聚簇索引全表+Record Lock |
个人理解：在RC模式下，由于没有gap锁，也就是间接没有next-key 锁，相当于只有record 锁
附件为：个人对mysql 锁的分类简单记录
