Mysql 百问系列:InnoDb加锁分析

1,590 阅读6分钟

问题:

  1. 当我们进行更新修改的时候,InnoDb 是如何加锁的?
  2. 锁是加在哪的?

准备工作:

数据库在RR(可重复读)隔离级别下。

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `addresss` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT student VALUES (1,'张一',1,'ssss'),(2,'张二',2,'ssss'),(3,'张三',3,'ssss'),(4,'张四',4,'ssss'),(5,'张五',5,'ssss'),(10,'张十',10,'ssss'),(20,'张二十',20,'ssss');

数据的聚簇索引类似于:

1 2 3 4 5 10 20
张一 张二 张三 张四 张五 张十 张二十
1 2 3 4 5 10 20
ssss ssss ssss ssss ssss ssss ssss

情况一:主键等值查询

## select 后面不加lock in share mode 或者 for update 是不加锁的。
#事务A
begin;
select * from student where id = 10 lock in share mode; 加S锁。

我们知道这个时候,如果其他事务,想要修改id = 10 的信息是被阻塞的。

#事务B  被阻塞
update student set age = 200 where id = 10

那么到这里看似顺利,我们稍微修改下,将id 改为9 ,我们知道9 这条数据是不存在的

#事务A
commit; ##不管如何先提交下事务。
begin; # 重新开始事务
select * from student where id = 9 lock in share mode; 加S锁。

然后我们同样在事务B 中去更新id = 10 的数据。

#事务B  未被阻塞
update student set age = 200 where id = 10

看到更新没有被阻塞,我们换成insert 语句。

#事务B  插入数据被阻塞
insert into student VALUES (8,'张八',12,'ssss');

加锁规则: 1. 加锁的单位是 next-key 锁。 2. 如果等值查询数据存在,则进化为 record(记录)锁,如果不存在则退化为 gap(间隙) 锁

上面的例子中,查询id 为10 的时候,只锁了id = 10 的这条数据。 但是查找id = 9 的时候,没找到数据,则退化为gap 锁,锁住了5 -10 之间的间隙,所以插入数据 (8,'张八',12,'ssss') 被阻塞。

情况二:无索引等值查询

#事务A
commit; ##不管如何先提交下事务。
begin; # 重新开始事务
select * from student where name ='张十' lock in share mode; 加S锁。
#事务B  插入数据被阻塞
insert into student VALUES (8,'张八',12,'ssss');
#事务C  修改数据被阻塞
update student set age = 10 where id = 10

更严重的一种情况是如果用不到索引,可能演变为表级锁。
也许你说平时根本不用在select 时特意去加锁, 那我们改成修改语句。

#事务A
commit; ##不管如何先提交下事务。
begin; # 重新开始事务
update student set age = 100 where name ='张9' ; 

由于张9 不存在,并且查询条件用不到索引,那么整个表都会被锁住。

#事务B  修改数据被阻塞
update student set age = 10 where id = 1

如果查询时用不到任何索引,则next-key 锁保持不变。 也就是 record 锁 和 gap 锁的合体。 确保修改语句能用到索引,防止全表被锁,可以降低死锁的发生。

情况三: 主键范围查找

#事务A
commit;
begin;
select * from student where id <15 lock in share mode;
#事务B
update student set age = 10 where id = 20
#事务C
insert into student VALUES (8,'张八',12,'ssss');

可以发现事务B,C都被锁住了。为什么查找范围明明是id<15,id为20的数据确也被锁住了?
这次加锁的范围其实是1,2,3,4,5,10以及5到10的间隙,10到20的间隙,还有20本身。由于15数据不存在,往后查找到最近一条记录加上next-key锁。

简单的归纳为:范围查找会锁住符合查找条件的所有记录,并锁住第一条不满足该条件的记录。

情况四: 无索引范围查找

#事务A
commit;
begin;
select * from student where age <8 lock in share mode;
#事务B
update student set age = 10 where id = 20
#事务C
insert into student VALUES (8,'张八',12,'ssss');

事务B,C同样被锁住了。但是这次的查找条件是age<8,但是连id =20 的数据都被锁住了。

在无索引的情况下,范围查找加锁范围扩大到了整表。

普通二级索引是如何加锁的?

在看这个问题前,我们先了解下锁是加在哪的
我们为表添加一个age 的普通二级索引,命名为index_age

ALTER TABLE `student` ADD INDEX `index_age`(`age`);

索引index_age 的结构如下:

age: 1 2 3 4 5 10 20
id: 1 2 3 4 5 10 20

当我们执行时

#事务A
commit;
begin;
update student set age = 2 where id =3;

对应的聚簇索引3会被锁住,并且相应的index_age也会被锁住。

当更新语句中更新字段涉及到索引字段时,对应的二级索引记录也会被加锁。

上面这个语句是先给聚簇索引加锁,然后再给二级索引index_age加锁。
如果换成:

update student set name = 'gg' where age =3;

那么加锁顺序为先给二级索引index_age加锁,然后再给聚簇索引加锁。

如果是二级索引范围查找

###事务A
commit;
begin;
select * from student where age <5 lock in share mode;
#注意,如果student数据较少,上面语句可能使用主键进行全表查找,而未使用到index_age,这种情况全表被锁。
#从这个现象也可以看出执行某个语句加锁是根据其使用到的索引来进行相应加锁的。

那么二级索引indxe_age 1,2,3,4 先被锁,然后查到对应的id 对对应的聚簇索引进行加锁。
分别尝试

update student set addresss ='xxxxx' where id = 2  ### 被阻塞
update student set addresss ='xxxxx' where id = 20  ### 未被阻塞

只给二级索引加锁,没有给聚簇索引加锁:

###事务A
commit;
begin;
select * from student where age =15 lock in share mode;

由于age =15的数据是不存在的,所以二级索引index_age 10到20之间有了间隙锁。但是聚簇索引是没有加锁的。

请尝试分析下面语句是否会被阻塞。答案下篇文章揭晓。

INSERT into student VALUES (15,'xxxx',4,'xxxx'); #语句1
INSERT into student VALUES (16,'xxxx',14,'xxxx'); #语句2
UPDATE student set age = 14 where  id = 2; #语句3

上一篇文章问题解答:

链接:juejin.cn/post/684490…

# 查询剩余库存
select last_number from stock where  id = 1000 lock in share mode; ## 步骤一
#库存足够 --减去库存
update stock set last_number = last_number - 1 where id = 1000; ## 步骤二
  1. 会产生死锁。如果事务A 完成步骤一,事务B 也完成步骤一,此时事务A 尝试进行步骤二时,发现需要等事务B释放S锁,同理,事务B需要进行步骤二时,也需要等事务A的S锁。死锁发生。
  2. 如果步骤一不加锁。会发生库存超卖现象。
  3. 修改步骤一select last_number from stock where  id = 1000 for update; 这样可以避免死锁,同时避免超卖。但并发量相应降低。

如有帮助,请多多关注。谢谢。