探讨 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)来保证修改的时候产生的幻读。