mysql锁(innodb)
测试数据库
create database if not exists `mysql_demo`;
use `mysql_demo`;
create table if not exists `user`
(
`id` int primary key,
`age` int ,
`name` varchar(20),
index index_age(age)
) charset utf8mb4 engine innodb;
insert into user (id, age, name)
values (1, 18, 'name_1')
, (2, 18, 'name_2')
, (3, 18, 'name_3')
, (4, 18, 'name_4')
, (5, 20, 'name_5')
, (7, 19, 'name_7')
, (8, 19, 'name_8');
锁的级别
- 排他锁(X)
对于所有的写操作默认均会加上排他锁,获取到锁后会禁止其他事务获取此资源的任意锁
也可使用 for update 显式上锁
# 事务A对id=1的上排他锁
select * from user where id = 1 for update;
------- 事务B --------
# 尝试加共享锁 阻塞
select * from user where id = 1 lock in share mode;
# 尝试加排他锁 阻塞
select * from user where id = 1 for update;
update user set name = 'name_1_1' where id = 1;
# 尝试加排他锁 阻塞 此时上的是表锁 包含有 id=1的数据
update user set name = 'name_2_2' where name = 'name_2';
- 共享锁(S)
读锁,会阻止其他事务获取此资源排他锁,允许获取共享锁
可以使用lock in share mode进行显式上锁
select语句不会默认上锁 为快照读
# 事务A对id=1的上共享锁
select * from user where id = 1 lock in share mode;
------- 事务B --------
# 未获取共享锁 快照读
select * from user where id = 1;
# 尝试获取共享锁 成功
select * from user where id = 1 lock in share mode;
# 尝试获取排他锁 阻塞
select * from user where id = 1 for update;
- 意向锁
当事务对某行数据使用了行锁,那么在这之前数据库会自动给此事务申请一个表的意向锁(意向共享锁IS、意向排他锁IX)
当其他事务进行申请行级别锁时,则会根据类型被阻塞
意向锁是一种表级别的锁
-
几种锁的互斥性
意向排他锁IX 意向共享锁IS 排他锁X(行级别) 共享锁S(行级别) 意向排他锁IX 兼容 兼容 互斥 互斥 意向共享锁IS 兼容 兼容 互斥 兼容 排他锁X(行级别) 互斥 互斥 互斥 互斥 共享锁S(行级别) 互斥 兼容 互斥 兼容
锁的粒度
只有命中索引才可能执行行锁,否则是表锁
- 行锁
锁加在索引上,并非数据行
# 索引命中 但数据不存在 此时为间隙锁 锁住(7,+∞)
select * from user where id = 8 for update;
# 命中索引且数据存在 此时锁住 1
select * from user where id = 1 for update;
# 命中索引且数据存在 此时锁住 (-∞,2)
select * from user where id < 2 for update;
# 命中索引且数据存在 此时锁住 age (-∞,18]、(18,19);id(-∞,4]、(4,7)
select * from user where age = 18 for update;
- 表锁
# 索引失效 即索引未命中 表锁
select * from user where id + 1 = 2 for update;
# 索引未命中 表锁
select * from user where name = 'name_1' for update;
上锁过程(行锁)
- 命中聚簇索引
对命中的聚簇索引行加锁
- 命中非聚簇
对命中的非聚簇索引行加锁
对对应的聚簇索引行加锁
锁的区间
- 普通行锁(record lock)
锁住的是一行数据
- 间隙锁 (gap lock)
锁住一个范围区间(开区间)
- 临键锁 (next-key lock)
间隙锁 + 普通行锁 (左开右闭)
- sql分析
# 事务A
select * from user where age = 18 for update;
------- 事务B --------
# sql_1 阻塞
select * from user where id = 4 for update;
# sql_2 阻塞
insert into user (id,age,name) values (6,19,'name_8')
# sql_3 阻塞
update user set name='name_5_5' where id =5;
# sql_4 阻塞
select * from user where age = 18 for update;
命中的索引是非聚簇非唯一索引
对index_age索引加临键锁 (-∞,18]、间隙锁(18,19)sql_4阻塞
对聚簇索引加临键锁 (-∞,4]、间隙锁(4,7)
sql_1、sql_2、sql_3阻塞