【MySQL】记录锁？间隙锁？临键锁？到底锁了些什么？这一篇帮你捋清楚(￣∇￣)_百度小程序_上海毫米网络优化公司

您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >

相关推荐recommended

【MySQL】记录锁？间隙锁？临键锁？到底锁了些什么？这一篇帮你捋清楚(￣∇￣)

作者：mmseoamin日期：2023-12-05

特别强调～

本测试使用的是MySQL 8.0.27～ 8.0.27～ 8.0.27（因为不同版本命令可能会有差异哈）

打开两个终端，分别连接上MySQL，使用select @@global.transaction_isolation;查看隔离级别（间隙锁要在可重复读的隔离级别下）

【MySQL】记录锁？间隙锁？临键锁？到底锁了些什么？这一篇帮你捋清楚(￣∇￣),第1张

如果报类似ERROR 1193 (HY000): Unknown system variable 'tx_isolation'的错，一般是版本问题

# 老版本：select @@global.tx_isolation;
select @@global.tx_isolation;
# 5.8版本之后使用：select @@global.transaction_isolation;
select @@global.transaction_isolation;

我们的测试表中的数据长这样

【MySQL】记录锁？间隙锁？临键锁？到底锁了些什么？这一篇帮你捋清楚(￣∇￣),第2张

事务1

终端A开启事务，查询id=5的数据（注意加上for update使用当前读）

select * from app_user_copy1 where id = 5 for update;

【MySQL】记录锁？间隙锁？临键锁？到底锁了些什么？这一篇帮你捋清楚(￣∇￣),第3张

查看当前事务的锁信息

select * from performance_schema.data_locks;

【MySQL】记录锁？间隙锁？临键锁？到底锁了些什么？这一篇帮你捋清楚(￣∇￣),第4张

在MySQL 5.5以上、5.7.14以下的版本中，用户可以通过INFORMATION_SCHEMA下的INNODB_TRX、INNODB_LOCKS以及INNODB_LOCK_WAITS这三张表简单地监控并分析可能存在的锁问题
在MySQL 8.0版本中，则需要使用performance_schema下的data_locks以及data_lock_waits获取相关的锁以及锁等待信息
而MySQL版本在5.7.14到8.0之间的用户，只能通过其它手段间接的获取上述信息

我们从LOCK_MODE列中可以看到此事当前事务有两把锁（后面附有各个列的含义介绍）
- 第一行LOCK_MODE为IX，即意向排他锁，属于表级锁
- 第二行LOCK_MODE为X,REC_NOT_GAP，表示当前仅为行记录锁，且非间隙锁，属于行级锁
  
  打开一个终端B，同样开启事务，更新id=5的行数据，会进入阻塞
```
update app_user_copy1 set name='test' where id=5;
```
  等到超时了就报错
  
  分别插入 id=3 和 id=7 的数据
```
INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (3, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
```
INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (7, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
  都插入成功，说明当命中🎯注解索引时，临键锁退化为行级锁，是不会加间隙锁的
  
  事务1结束，将数据恢复至测试开始前
  
  事务2
  
  终端A开启事务，查询id=3的数据（注意加上for update使用当前读）
```
select * from app_user_copy1 where id = 3 for update;
```
  查看当前事务的锁信息
```
select * from performance_schema.data_locks;
```
  可以看到，此时LOCK_MODE为X,GAP，LOCK_DATA为5，即加了间隙锁，锁住 id=5 的行数据前的间隙；
  
  终端B开启事务，更新 id=1 跟 id=5 两个边界信息
```
update app_user_copy1 set name='test' where id = 1; update app_user_copy1 set name='test' where id = 5;
```
  都更新成功，即当命中间隙时，会锁住当前间隙，并且不包括前后两条数据（即开区间）
  
  事务2结束，将数据恢复至测试开始前
  
  事务3
  
  终端A开启事务，查询 id > 3 and id <=5 的数据（注意加上for update使用当前读）
```
select * from app_user_copy1 where id > 3 and id <= 5 for update;
```
  查看当前事务的锁信息
```
select * from performance_schema.data_locks;
```
  可以看到，此时LOCK_MODE为X，LOCK_DATA为5，即加了临键锁，锁住 id=5 的行数据以及其前的间隙；
  
  终端B开启事务，更新 id=1 、id=5 的信息
```
update app_user_copy1 set name='test' where id = 1; update app_user_copy1 set name='test' where id = 5;
```
  id=1 更新成功
  
  id=5 更新失败
  
  插入id=2数据，插入失败
```
INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (2, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
  插入id=7数据，插入成功
```
INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (7, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
  即临键锁会锁住当前记录以及记录前的间隙（左开右闭区间）
  
  事务3结束，将数据恢复至测试开始前
  
  事务4
  
  终端A开启事务，查询 id > 3 and id < 9 的数据（注意加上for update使用当前读）
```
select * from app_user_copy1 where id > 3 and id < 9 for update;
```
  查看当前事务的锁信息
```
 select * from performance_schema.data_locks;
```
  可以看到，此时有两个行级锁
  - 第一个LOCK_MODE为X，LOCK_DATA为5，即加了临键锁，锁住 id=5 的行数据以及其前的间隙；
  - 第二个LOCK_MODE为X,GAP，LOCK_DATA为9，即加了间隙锁，锁住 id=9 的行数据前的间隙；
    
    终端B开启事务，更新 id=1 、id=5 、id=9 的信息
```
update app_user_copy1 set name='test' where id = 1; update app_user_copy1 set name='test' where id = 5; update app_user_copy1 set name='test' where id = 9; 
```
    id=1 更新成功
    
    id=5 更新失败
    
    id=9 更新成功
    
    插入id=2数据，插入失败
```
INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (2, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
    插入id=7数据，插入失败
```
INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (7, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
    即当查询的是一段范围时，会锁住在符合查询条件的所有数据行，以及范围内的所有间隙（开区间，除非该数据行符合查询条件）事务5 前面4个测试我们都是使用的唯一的主键索引，下面我们用普通索引试下(￣∇￣)/ 终端A开启事务，查询 age=35 的数据（注意加上for update使用当前读）
```
select * from app_user_copy1 where age = 35 for update;
```
    查看当前事务的锁信息
```
 select * from performance_schema.data_locks;
```
    可以看到，此时有3把行锁
    - 第一行LOCK_MODE列中为X，即加了临键锁
      - LOCK_DATA为35, 5指的是 age=35 的 id=5 的数据
      - 锁住的范围是 age=35 的行数据以及其前间隙
    - 第二行LOCK_MODE列中为X,REC_NOT_GAP
      - LOCK_DATA为5，即给 id=5 的行记录加了记录锁
    - 第三行LOCK_MODE列中为X,GAP，即加了间隙锁
      - LOCK_DATA为37, 24的指的是 age=37 的 id=24 的数据
      - 锁住的范围是 age=37 的行数据前的间隙
      我们把数据库数据按照age升序排列，如下图
      
      终端B开启事务，做如下更新操作
```
update app_user_copy1 set name='test' where age = 33; update app_user_copy1 set name='test' where age = 35; update app_user_copy1 set name='test' where age = 37;
```
      age=33 更新成功
      
      age=35 更新失败
      
      age=37 更新成功
      
      插入 age=34 数据，插入失败
```
INSERT INTO `app_user_copy1` (`name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES ('用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 34, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
      插入 age=36 数据，插入失败
```
INSERT INTO `app_user_copy1` (`name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES ('用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 36, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
      插入 age=38 数据，插入成功
```
INSERT INTO `app_user_copy1` (`name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES ('用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 38, '2022-01-21 13:28:15', '2022-01-21 13:28:15');
```
      上面测试中WHERE后的条件都是加了索引的，如果该字段未加索引，则会锁表（未命中不锁）
      
      总结
      - LOCK_MODE列中为X，即加了临键锁，锁住的范围是LOCK_DATA中的行数据以及其前间隙（左开右闭）
      - LOCK_MODE列中为X,REC_NOT_GAP，锁住的是LOCK_DATA中的行数据
      - LOCK_MODE列中为X,GAP，锁住的是LOCK_DATA中的行数据前面的间隙（左开右开）

上一篇：11 Go的作用域

下一篇：MySQL 查询语句大全