MySQL 中的表级锁很差劲吗？

[TOC]

当然不是！

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

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

我们先来大致说一下 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 如下：

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;

添加一条测试数据：

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

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

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

lock table user read;

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

select * from user;

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

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

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

这条写入语句会 卡住，如下：

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

unlock tables;

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

如下是窗口2的截图：

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

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

思考：

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

2.2 表独占写锁

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

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

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

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 无法插入数据呢？这其实跟加锁方式有关，我们一起来看下。

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

lock table user read local;

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

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

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

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

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

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

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

set global concurrent_insert = ALWAYS;

2.4 锁的优先级

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

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

修改 SQL 优先级

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

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

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

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

set LOW_PRIORITY_UPDATES = 1

修改写锁上限

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

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

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