MySQL 中的表级锁很差劲吗?

[TOC]

当然不是!

其实本来今天是要和大家聊一聊 MyISAM 引擎的,结果在写的过程中,又发现锁这个话题可以单独写一篇,于是就有了今天的文章。

说到 MyISAM 和 InnoDB 的区别,很多人都知道,区别在于一个是表锁一个是行锁,那么小伙伴们有没有想过,表锁和行锁有什么区别?各自又有哪些玩法?今天松哥就来和大家聊聊这个话题。

1.锁

我们先来大致说一下 MySQL 中的锁。

当多个事务或者多个进程访问同一个资源的时候,为了保证数据的一致性,就需要用到 MySQL 锁机制,从锁定资源的角度来看,MySQL 中的锁大致上可以分为三种:

  • 表级锁(table-level locking):表级锁的特点是开销小,加锁快,不会出现死锁,但是锁定粒度较大,发生锁冲突的概率高,而且并发度也低。
  • 行级锁(row-level locking):行级锁的特点是开销大,加锁慢,有可能会出现死锁,但是它的锁定粒度小,发生锁冲突的概率低,并发度也高。
  • 页面锁:开销和加锁时间界于表锁和行锁之间,会出现死锁,锁定粒度界于表锁和行锁之间,并发度一般。

虽然理论上有三种锁,但是对于在坐的各位小伙伴包括松哥而言,我们日常开发接触最多的还是前两种,就是表级锁和行级锁。

在 MySQL 中,MyISAM 引擎是表级锁,而 InnoDB 引擎则支持行级锁,不过需要注意,其实 InnoDB 也支持表级锁,只不过默认情况下是行级锁。

2.表级锁

MySQL 的表级锁有两种模式:

  • 表共享读锁(Table Read Lock)。
  • 表独占写锁(Table Write Lock)。

MyISAM 引擎在执行 select 时会自动给相关表加读锁,在执行 update、delete 和 insert 时会自动给相关表加写锁。

2.1 表共享读锁

我们先来看表共享读锁,加了共享读锁的表,不会阻塞其他 session 的读请求,但是会阻塞其他 session 的写请求。

我们来演示一下这个效果。

在下面的案例中,我们会准备两个窗口,代表两个 session。

首先我们新建一张表,选择 MyISAM 作为存储引擎,DDL 如下:

1
2
3
4
5
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

添加一条测试数据:

1
insert into user(name) values('javaboy');

然后我们在两个命令行窗口分别登录 mysql,模拟出两个 session。

首先在第一个窗口为表添加读锁,如下:

1
lock table user read;

然后在第二个窗口去读取数据:

1
select * from user;

可以发现,是可以正常读取的。

然后我们尝试在第二个窗口写入一条数据:

1
insert into user(name) values('itboyhub');

这条写入语句会 卡住,如下:

卡住的原因是因为 user 表目前被上了共享读锁,这个时候需要我们去到第一个窗口中,解除表的锁定,这个时候第二个窗口中的这条插入 sql 就可以执行了。如下:

1
unlock tables;

当这条 SQL 完毕后,第二个窗口中的插入语句立马就执行了。

如下是窗口2的截图:

可以看到,加了共享读锁的表,不会阻塞其他 session 的读(select)请求,但是会阻塞其他 session 的写(insert、update、delete)请求。

需要注意的是,如果在同一条 SQL 中,同一个表名出线了 N 次,该表就要锁定 N 次,如下:

思考:

我们在窗口 1 中给 user 表加了锁,那么在窗口 1 中是否可以对 user 表执行 insert/update/delete 等写操作呢?评论区 show 出你的答案~

2.2 表独占写锁

这个独占写锁就是大家锁所熟知的排他锁,它会阻塞其他进程对同一表的读写操作,只有当当前锁释放后,才会执行其他进程的读写操作。

我们来演示一下这个过程。

还是两个窗口,首先我们我们在第一个窗口中执行锁表操作:

1
lock table user write;

然后去第二个窗口中做查询操作,如下:

可以看到,由于是排他锁,所以查询操作也被阻塞了。此时需要在窗口 1 中解除表的锁定,窗口 2 中的查询操作才会继续执行下去。

这就是表独占写锁,也就是排他锁。

在 MyISAM 存储引擎中,会自动为 SELECT 语句加上共享锁,为 update/delete/insert 操作加上排他锁。

2.3 concurrent_insert

前面我们讲的是表级锁的两种基本模式,在具体的使用过程中,我们还可以通过 concurrent_insert 去配置一些并发行为。

concurrent_insert 有三种不同的取值:

  • NEVER:加了读锁之后,不允许其他 session 并发插入。
  • AUTO:加了读锁之后,如果表里没有删除过数据,其他 session 就可以并发插入。
  • ALWAYS:加了读锁之后,允许其他 session 并发插入。

需要注意的是,在 MySQL5.5.3 之前,NEVER、AUTO 以及 ALWAYS 分别使用 0、1、2 代替。

通过 show global variables like '%concurrent_insert%' 命令我们可以查看当前数据库中 concurrent_insert 的取值,如下:

可以看到,数据库中默认的 concurrent_insert 取值为 AUTO。有小伙伴可能会说,啥?AUTO?那为啥我在 2.1 小结中,当表加了读锁之后,其他 session 无法插入数据呢?这其实跟加锁方式有关,我们一起来看下。

还是两个窗口,首先我们在第一个窗口中为表添加读锁,如下:

1
lock table user read local;

可以看到,最后多了一个 local,这就是关键。

接下来我们在窗口 2 中去尝试读写操作,如下:

从图中可以看到,读写操作都可以顺利执行。

但是这个时候,如果我们去窗口 1 中执行查询,如下:

可以看到,这里并看不到窗口 2 中刚刚添加的那条数据,换句话说,窗口 2 中添加的数据对窗口 1 是不可以见的,必须等窗口 1 中的锁释放之后,才可以看到窗口 2 中添加的数据。

如下图,释放锁之后,就可以看到另外一个窗口添加进来的数据了:

这是我给大家演示的默认的 concurrent_insert 的行为,大家也可以通过如下 SQL 修改该值:

1
set global concurrent_insert = ALWAYS;

2.4 锁的优先级

在 MyISAM 中,默认情况下,写锁的优先级要高,不过开发者也可以自行调整这个默认锁的优先级。

话说回来,由于 MyISAM 是表锁,所以不建议用在需要频繁更新的场景下,否则可能会造成长时间的锁等待。所以下面的优先级调整,仅仅作为技术层面的探讨。

修改 SQL 优先级

首先我们可以在执行 SQL 的时候,顺便修改其优先级:

例如执行 select 的时候可以使用 HIGH_PRIORITY 来提高该语句的优先级,如下:

在执行 delete/update/insert 等操作的时候,可以使用 LOW_PRIORITY 来降低其优先级,以便让读取操作先执行:

当然我们也可以通过如下 SQL 让所有支持 LOW_PRIORITY 选项的语句都默认地按照低优先级来处理。

1
set LOW_PRIORITY_UPDATES = 1

修改写锁上限

我们可以修改 MAX_WRITE_LOCK_COUNT 的值,该变量默认值如下图:

这个值表示当一个表的写锁数量达到给定的值后,就降低写锁的优先级,让读锁有机会执行。如果有需要,我们可以自行调整这个值,调整方式如下:

1
set GLOBAL MAX_WRITE_LOCK_COUNT=1024;

3.行级锁

行级锁松哥留到讲 InnoDB 的时候再和大家聊,今天我们就先扯这么多~

参考资料:

1.https://database.51cto.com/art/201910/604421.htm
2.https://zhuanlan.zhihu.com/p/123962424

# MySQL

喜欢这篇文章吗?扫码关注公众号【江南一点雨】【江南一点雨】专注于 SPRING BOOT+微服务以及前后端分离技术,每天推送原创技术干货,关注后回复 JAVA,领取松哥为你精心准备的 JAVA 干货!

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×