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://eip.teamshub.com/resources/loading.gif)
![](https://media.teamshub.com/10000/tm/2022/01/21/topic/fbb1abd6-49b2-483e-b823-7074d18a9c49.png)
图片加载失败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 锁的分类简单记录
![](https://img2022.cnblogs.com/blog/2599613/202209/2599613-20220929232129223-381938585.png)