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

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

全局锁

命令：flush tables with read lock

可以通过命令 flush tables with read lock(FTWRL) 开启全局锁，开启后整个库处于只读状态，开启后其它线程的以下操作会被阻塞：

• 数据的增删改
• DDL 语句，包括：建表、修改表结构
• 更新类事务的提交语句

全局锁的使用场景是做全库逻辑备份，即把整个库每个表 select 出来保存为文本。

如果做备份的时候，会引起多个表之间数据不一致，备份得到的库不是一个逻辑时间点。

参数：mysqldump --single-transaction

官方自带的 mysqldump 工具可以使用参数 --single-transaction，导出数据的时候就会开启事务，确保导出的数据是一致的，而且由于有 MVCC 的支持，导出数据的时候其它线程可以执行更新操作。

参数：set global readonly = true

readonly 的方法也能使整个库进入只读状态，但是会有问题：

1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大
2. 执行 FTWRL 命令后如果客户端断开，MySQL 会自动释放全局锁；而 readonly 设置后，客户端断开后整个数据还是会保持只读状态，风险高

表级锁

MySQL 中表级锁有两种：

• 表锁
• 元数据锁（meta data lock, MDL）

表锁：lock tables ... read/write

表锁可以用 unlock tables 释放，也可以在客户端断开连接时候释放

使用举例：

sql
lock tables t1 read, t2 write;

上面例子的作用是：

• 其它线程写 t1 会被阻塞，本线程只能读 t1
• 其它线程读写 t2 会被阻塞，本线程能读写 t2，不能写 t1

元数据锁

• MDL 锁不需要显式使用，在访问一个表的时候会自动加上。
• MDL 锁是 MySQL 5.5 引入的
• 当对一个表做增删改查的时候，自动加 MDL 读锁
• 当修改表结构时，自动加 MDL 写锁
• 读锁之间不互斥
• 读写锁之间、写锁之间互斥

如上图，修改线上数据库的表结构是有风险的。

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

1. 解决长事务，事务不提交就会一直占着 MDL 锁。可以查询 information_schema 库的 innodb_trx 表看是否有长事务在执行，如果要修改结构的表正在有事务执行，要么等待完成，要么 kill 掉，再执行修改表结构的操作。

2. 可以在 alter table 里设置等待时间

sql
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...