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='用户信息表';

默认数据

id	user_id	number
1	1	3
5	5	5
8	8	8
9	9	9

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

测试

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

唯一键记录存在

事务1

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

查看锁信息

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	281473638589224:1068:281473567491024	1856	60	19	demo	user				281473567491024	TABLE	IX	GRANTED
INNODB	281473638589224:2:4:5:281473567488112	1856	60	19	demo	user			PRIMARY	281473567488112	RECORD	X,REC_NOT_GAP	GRANTED	5

事务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)

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	281473638589224:1068:281473567491024	1875	60	33	demo	user				281473567491024	TABLE	IX	GRANTED
INNODB	281473638589224:2:4:6:281473567488112	1875	60	33	demo	user			PRIMARY	281473567488112	RECORD	X,GAP	GRANTED	8

结论

事务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;

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	281473638589224:1068:281473567491024	1952	60	110	demo	user				281473567491024	TABLE	IX	GRANTED
INNODB	281473638589224:2:4:5:281473567488112	1952	60	110	demo	user			PRIMARY	281473567488112	RECORD	X,REC_NOT_GAP	GRANTED	5
INNODB	281473638589224:2:4:6:281473567488456	1952	60	110	demo	user			PRIMARY	281473567488456	RECORD	X	GRANTED	8

事务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;

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	281473638589224:1068:281473567491024	1891	60	66	demo	user				281473567491024	TABLE	IX	GRANTED
INNODB	281473638589224:2:6:7:281473567488112	1891	60	66	demo	user			IDX_MOBILE_NUM	281473567488112	RECORD	X	GRANTED	5, 5
INNODB	281473638589224:2:4:5:281473567488456	1891	60	66	demo	user			PRIMARY	281473567488456	RECORD	X,REC_NOT_GAP	GRANTED	5
INNODB	281473638589224:2:6:6:281473567488800	1891	60	66	demo	user			IDX_MOBILE_NUM	281473567488800	RECORD	X,GAP	GRANTED	8, 8

事务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;

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	281473638589224:1068:281473567491024	1910	60	84	demo	user				281473567491024	TABLE	IX	GRANTED
INNODB	281473638589224:2:6:6:281473567488112	1910	60	84	demo	user			IDX_MOBILE_NUM	281473567488112	RECORD	X,GAP	GRANTED	8, 8

事务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锁住了，有点奇怪

死锁例子

事务A	事务B
START TRANSACTION;
	START TRANSACTION;
select * from demo.user where id=6 for update
	select * from demo.user where id=7 for update
insert demo.user values(6,6,6)
	insert demo.user values(7,7,7)，然后报死锁

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