文章目录

  • MySQL45讲
  • 基础篇
  • 06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
  • 全局锁
  • 表级锁


MySQL45讲

基础篇

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。
方式1：通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份，在备份过程中整个库完全处于只读状态。

让整个库处于只读状态有风险：

  1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  2. 如果在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog， 会导致主从延迟。

方式2：官方自带的逻辑备份工具 mysqldump。当 mysqldump 使用参数–single-transaction（single-transaction 方法只适用于所有的表使用事务引擎的库）的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC（多版本并发控制） 的支持，这个过程中数据是可以正常更新的。

方式2的一致性读是好，但前提是引擎要支持这个隔离级别。 比如， MyISAM 不支持事务（DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一），则需要使用 FTWRL 命令。

方式3：set global readonly=true 可以让全库进入只读状态。

不推荐，建议使用 FTWRL 方式，原因有二。

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

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。
MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

常见的坑：给一个小表加个字段，导致整个库挂了（MDL读锁未释放，MDL获取写锁被阻塞，后续获取MDL读写锁也都被阻塞）。
示例：

mysql添加字段导致表被锁住 mysql 加字段锁表方案_锁

  1. session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。
  2. 之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞（具体参考mysql MDL读写锁阻塞，以及online ddl造成的“插队”现象）。
  3. 之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。
  4. 如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务在语句执行开始时申请，一旦申请到MDL锁后，直到事务执行完才会将锁释放。

疑问：如何安全地给小表加字段？

在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果要做 DDL 的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

疑问：如果变更的表是一个热点表，请求很频繁，而此时又不得不加个字段，该怎么做？

在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后再通过重试命令重复这个过程。