探讨 MySQL 的锁

上篇博客说了 MVCC 解决了 MySQL 在可重复的隔离情况下幻读的问题,这篇博客主要探讨下,在修改的时候,如何解决幻读的问题。

MySQL 在控制并发的时候,同样采用了锁的机制。从读写上面分,有读写和写锁,从结构上分,有行锁和表锁.行锁又分为行锁、间隙锁和 Next Key

读锁和写锁

读锁 :共享锁 ,S 锁

写锁:排它锁 ,X 锁

select :不加锁,加锁后,也可以使用 select 查询数据

怎么加锁

select ...lock in share mode 加读锁

select ...for updare ,update、 delete 都是加写锁

其中,读读共享, 读写互斥 写写互斥

insert 是不会加入写锁,因为 MySQL 在读的时候有 MVCC 来控制读取,无需直接添加写锁。但是会加一个隐式锁 ,防止当前事务提交前,数据被其他事务访问。

隐式锁:

一个事务插入一条记录后,还未提交,这条记录会保存本次事务 id ,而其他的事务如果想来的对这个记录加时会发现事务 id 不对应,这个隐式锁会转换成显示的 写锁(X 锁)。

行锁和表锁

行锁:

  1. Record Lock:单个行记录上的锁,只锁定记录本身
  2. Gap Lock:间隙锁,锁定一个范围,但不包括记录本身。 目的是为了防止同一个事物的两次当前读,出现幻读的情况
  3. Next-Key Lock:1+2,锁定一个范围,并锁定记录本身。目的:解决幻读

我们还是采用 读已提交和可重复读 来测试锁的情况。

读已提交

主键索引写锁
1
2
3
//事务 A
begin;
select * from tbl_user where id=1 for update;
1
2
3
4
5
//事务 B 
begin;
select * from tbl_user where id=1 for update;//不可以查询
select * from tbl_user where id=2 for update; //可以查询

image-20200815170838581

B 事务中非相同 ID 的数据能够正常的查询。说明在主键 ID 情况下,使用的是行锁,只简单的锁住了一条数据。

唯一索引和主键索引效果一致。

普通索引
1
2
3
//事务 A
begin;
select * from tbl_user where user_name='user1' for update;
1
2
3
4
//事务 B 
begin;
select * from tbl_user where user_name='user1' for update;//不可以查询
select * from tbl_user where user_name='user2' for update; //可以查询

image-20200815171830302

使用普通索引,也会把所有的查询出来的数据加锁

普通索引插入数据
1
2
3
begin;
mysql> select * from tbl_user where user_name ='user1' for update;

1
2
begin;
mysql> insert into tbl_user (user_name,user_code,user_age) values ('user1','u0007',45);//正常插入

普通索引插入已经锁定的数据是能够正常插入,事务提交后,A 事务中也能够正常的查询数据,这个时候 A 事务就产生了幻读

image-20200816091052128

全表扫描
1
2
begin;
select * from tbl_user where user_age =10 for update;
1
2
3
4
5
6
7
begin;
select * from tbl_user where user_name='user1' for update; //不可以查询
select * from tbl_user where id=1 for update; //不可以查询
select * from tbl_user where user_age=15 for update; //不可以查询
select * from tbl_user where user_age=10 for update; //不可以查询
select * from tbl_user where id=2 for update; //可以查询
select * from tbl_user where user_name='user2' for update; //可以查询

image-20200815173100976

通过上面的分析,全表扫描的情况下,也是只会对查询出来的数据加锁,但是如果再次使用加锁的字段再去查询,都会被阻塞。(此处还是一个疑问,知道的朋友可以指点下)

可重复读的隔离级别

主键索引和唯一索引

结果与 读已提交结果相同

普通索引查询

结果与 读已提交结果相同

普通索引插入
1
2
begin;
select * from tbl_user where user_name='user1' for update;
1
2
3
4
5
6
7
begin;
insert into tbl_user (user_name,user_code,user_age) values('user1','u0006',40) //插入 user1 无法插入。
insert into tbl_user (user_name,user_code,user_age) values ('user11','u000100',100);//插入 user1 无法插入。
insert into tbl_user (user_name,user_code,user_age) values ('user10','u000100',100);//插入 user1 无法插入。
insert into tbl_user (user_name,user_code,user_age) values('user6','u0006',40) //能够成功插入
insert into tbl_user (user_name,user_code,user_age) values ('user2','u00020',100);//能够成功插入

在插入同样的数据的时候,MySQL 会阻塞当前插入,这样就能够给防止幻读的产生。

对于 user10, user11 无法插入,是因为 MySQL 对数据添加了写锁的同时,又对数据添加了间隙锁,当然这个间隙锁是有范围的。当插入 user2,user6 的时候又可以插入了。

全表扫描
1
2
begin;
select * from tbl_user where user_age =10 for update;
1
2
3
4
5
begin;
select * from tbl_user where user_name='user1' for update;//阻塞
select * from tbl_user where user_name='user2' for update;//阻塞
select * from tbl_user where user_age =10 for update;//阻塞
select * from tbl_user where user_age =15 for update;//阻塞

image-20200816102041376

全表扫描的情况下,所有的插入都会被阻塞,这个时候会锁住全部数据和所有的间隙。这种情况在生产环境中,一定要避免,否则会阻塞所有的更改操作。

总上对比结果如下:

隔离级别 主键索引 唯一索引 普通索引 全表扫描
读已提交 行锁 行锁 行锁 行锁
可重复读 行锁 行锁 间隙锁+行锁 锁全部数据和间隙

通过上面的分析,MySQL 防止幻读的产生总有两个策略,MVCC 解决读取的幻读,间隙锁和行锁(Next-Key Lock)来保证修改的时候产生的幻读。

作者

付威

发布于

2020-08-17

更新于

2020-09-06

许可协议

评论