mysql 行锁,间隙锁,临键锁，锁范围和死锁实际例子实战

背景

想了解下RR事务如何防止幻读的，以及一个实际的死锁例子

锁介绍

行锁（Record Lock）：
锁直接加在索引记录上面。通常来说就是锁一行
间隙锁（Gap Lock）：
锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。通常来说是锁区间，可以表示为（）
临键锁 ( Next-Key Lock )
行锁与间隙锁组合起来用就叫做Next-Key Lock。 可以表示为[]

表

CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  `mobile_num` bigint NOT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_USER_ID` (`user_id`),
  KEY `IDX_MOBILE_NUM` (`mobile_num`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3 COMMENT='用户信息表';

默认数据

每次测试后，回到这个状态

测试

下面测试都是在事务RR级别下的测试的，mysql 版本8.0

唯一键记录存在

事务1

START TRANSACTION;
select * from 
demo.`user`  where id=5 for update
//先不提交
COMMIT;

查看锁信息

事务2

失败
update demo.`user` set user_id =5 where id=5;
成功
insert demo.`user` values(4,4,4)
成功
insert demo.`user` values(6,6,6)

结论

只会锁id=5这一行

唯一键记录不存在

事务1

START TRANSACTION;
select * from 
demo.`user`  where id=6 for update
COMMIT;

事务2

成功
insert demo.`user` values(4,4,4)
失败
insert demo.`user` values(5,5,5)
失败
insert demo.`user` values(6,6,6)
失败
insert demo.`user` values(7,7,7)

结论

事务A锁住的区间是[6,7]，从结果来推导一下，因为id=6这条记录不存在，所以在(5，8)的 间隙都要锁住，因为这些间隙都可能会插入ID=6

范围查询

事务1

START TRANSACTION;
select * from 
demo.`user`  where id>=5 and id<=8 for update
COMMIT;

事务2

成功
insert demo.`user` values(4,4,2)
都失败
insert demo.`user` values(5,5,3)
insert demo.`user` values(6,6,4)
insert demo.`user` values(7,7,5)
insert demo.`user` values(8,8,6)
成功
insert demo.`user` values(10,10,7)

结论

比较好理解，会把[5，8]都锁住

普通索引存在

事务1

START TRANSACTION;

select * from 
demo.`user`  where mobile_num=5 for update
COMMIT;

事务2

失败
update demo.`user` set user_id =5 where id=5;

都失败
insert demo.`user` values(10,10,3)
insert demo.`user` values(10,10,4)
insert demo.`user` values(10,10,5)
insert demo.`user` values(10,10,6)
insert demo.`user` values(10,10,7)

成功
insert demo.`user` values(10,10,8)
成功
insert demo.`user` values(10,10,2)

总结

where id=5 会把id=5锁，
set user_id =5 从数据来看，user_id从3到8的区间都可能插入5，所以user_id锁住的区间是（3，8）

普通索引不存在

事务A

START TRANSACTION;

select * from 
demo.`user`  where mobile_num =6 for update


COMMIT;

事务B

成功
insert demo.`user` values(11,11,4)
失败
insert demo.`user` values(12,12,5)
失败
insert demo.`user` values(10,10,6)
失败
insert demo.`user` values(10,10,7)
成功
insert demo.`user` values(10,10,8)

结论

锁住了【5，8) ，因为6不存在，所以6可能在的区间是（5，8），这里为啥实际上把5锁住了，有点奇怪

死锁例子

从之前的结论可以分析，5，8这个区间是空的，所以执行for update 操作会把这个区间锁住，两个事务都会对这加锁