小艾的自留地

Stay foolish, Stay hungry

本文是基于 极客时间——MySQL 实战 45 讲 整理的学习笔记,仅供学习参考,请勿用于商业用途,如若侵权,请联系并删除。

课程重点:

  • 了解 Mysql 全局锁
  • 了解 Mysql 表级锁
  • 为什么给小表增加字段也会导致表锁

全局锁

顾名思义,全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是:

1
mysql> Flush tables with read lock (FTWRL)

当你需要让整个库处于只读状态的时候,可 以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数 据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

为什么备份需要加锁呢?

不加锁的话,备份系统备份的得到的库不是一个逻辑时间点,这个视图是逻辑不一致的。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持, 这个过程中数据是可以正常更新的。

你一定在疑惑,有了这个功能,为什么还需要 FTWRL 呢?一致性读是好,但前提是引擎要 支持这个隔离级别。比如,对于 MyISAM 这种不支持事务的引擎,如果备份过程中有更 新,总是只能取到最新的数据,那么就破坏了备份的一致性。这时,我们就需要使用 FTWRL 命令了。

所以,single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了 不支持事务的引擎,那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员 使用 InnoDB 替代 MyISAM 的原因之一。

你也许会问,既然要全库只读,为什么不使用 set global readonly=true 的方式呢?确 实 readonly 方式也可以让全库进入只读状态,但我还是会建议你用 FTWRL 方式,主要有 两个原因:

  • 一是,在有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库 还是备库。因此,修改 global 变量的方式影响面更大,我不建议你使用。
  • 二是,在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开, 那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设 置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这 样会导致整个库长时间处于不可写状态,风险较高。

业务的更新不只是增删改数据(DML),还有可能是加字段等修改表结构的操作(DDL)。 不论是哪种方法,一个库被全局锁上以后,你要对里面任何一个表做加字段操作,都是会被 锁住的。

表级锁

MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock, MDL)。

表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动 释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别 的线程的读写外,也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程 写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执 行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。

在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于 InnoDB 这 种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还 是太大。


另一类表级的锁是 MDL(metadata lock)。MDL 不需要显式使用,在访问一个表的时候 会被自动加上。MDL 的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在 遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线 程拿到的结果跟表结构对不上,肯定是不行的。

MDL 是MySQL 5.5版本引入的,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

  • 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
  • 读写锁、写锁之间是互斥的,用来保证变更表结构操作的安全性。也就是说,如果有两个
    线程要同时给一个表加字段或者分别进行增删改查和加字段,那么其中一个要等另一个执行完才能开始执行。

Mysql 5.6 之后,支持Online DDL,也就是不会堵塞增删改查。

Online DDL的过程是这样的:

  1. 拿MDL写锁
  2. 降级成MDL读锁
  3. 真正做DDL
  4. 升级成MDL写锁
  5. 释放MDL锁

下面通过一个示例来说明,为什么有时候给小表增加字段也可能会导致整个表被锁住。

这里的实验环境是 MySQL 5.7。

从上图可以看到 session A 先启动,这时候会对表 t 加一个 MDL 读锁。由于 session B 需要 的也是 MDL 读锁,因此可以正常执行。

需要注意的是,此时 session A 开启了事务,但是并没有释放。

之后 session C 会被 blocked,是因为 session A 的 MDL 读锁还没有释放,而 session C 需要 MDL 写锁,因此只能被阻塞(阻塞原因就是前面提到的,MDL 读写锁是互斥的)。

如果只有 session C 自己被阻塞还没什么关系,但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了,所有对表的增删改查操作都需要先申请 MDL 读锁,就都被锁住,等于
这个表现在完全不可读写了。

如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新 session 再请求的话,这个库的线程很快就会爆满。

事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,MDL 需要等到整个事务提交后再释放

那么如何安全的给小表加字段呢?

  • 非热点表:解决长事务、慢查询占着 MDL 锁不释放的问题
  • 热点表:在 alter table 语句里面设定等待时间

所以在做 DDL 操作之前,最好看一下是否有大事务的提交,或者慢查询存在,尽量避免与这些同时执行。

总结

  1. 备份整库时,需要加全局锁。有两种方式:FTWRL 和 mysqldump 的 -single-transaction。一个库被全局锁上以后,要对里面任何一个表做加字段操作,都是会被锁住的。
  2. 表级锁有两种:表锁和 MDL。前者需要显式使用,后者则不需要,是系统默认加的。
  3. update table set is_enable = 1 这个 SQL 语句,获取的还是行锁,只是是这个表的所有行。
  4. Mysql 5.6 以后支持 Online DDL,也就是对表做 DDL 操作,不会阻塞增删改查。
  5. 不过做 DDL 操作之前,还是要看一下是否有长事务未提交,否则 DDL 操作会被阻塞。

评论