看完MySQL全局锁和表锁，你废了吗？

根据加锁粒度，MySQL的锁：

• 全局锁
• 表级锁
• 行锁

全局锁和表锁都实现在Server层。

1 全局锁

对整个DB实例加锁。

1.1 加全局的读锁

执行命令：

Flush tables with read lock (FTWRL)

让整个库只读，之后其他线程的如下语句会被阻塞：

• 数据更新语句（数据的增删改）
• 数据定义语句（建表、修改表结构等）
• 更新类事务的提交语句

若FTWRL前有读写，FTWRL都会等待读写执行完毕后再执行。

FTWRL执行时，要刷脏页数据到磁盘，因为要保持数据一致性，所以执行FTWRL的时机是所有事务都提交完毕后。

1.2 适用场景 - 全库逻辑备份

把整库的每个表都select出来存成文本。历史有做法通过FTWRL确保不会有其他线程对DB更新，然后对整库备份。

备份过程中，整库都只读。但让整库只读：

• 若你在主库备份，则备份期间，主库都不能再执行更新，业务直接停摆
• 若你在从库备份，则备份期间，从库不能执行主库同步过来的binlog，主从延迟加剧

看来加全局锁不太好。不妨反思：

1.3 备份为何要加锁？

即不加锁会导致什么问题？假设现在维护购课系统，关注用户的：

• 账户余额表(u_account)
• 用户课程表(u_course)

现在发起一个逻辑备份。假设备份期间，有一用户，他购买了一课程，业务逻辑里就要扣掉他的余额，然后往已购课程里加上一本书。

若时间顺序：

• 先备份 u_account
• 然后用户购买
• 然后备份 u_course
  

用户A的数据状态就是u_account没扣，但u_course里多了一门课。若后面用这个备份恢复数据，用户A就发现自己血赚！

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

1.4 mysqldump

有个办法能拿到一致性视图：在RR下开启一个事务。

官方的逻辑备份工具mysqldump。当mysqldump使用参数–single-transaction，导数据前会启动一个事务，确保得到一致性视图。
由于MVCC，该过程中数据依旧能正常更新。

若mysqldump备份的是整个schema，某个小表t1只是该schema上其中有一张表

① 情况1

master对小表t1的DDL传输到slave去应用时，mysqldump已备份完t1表数据，此时slave同步正常，没问题

② 情况2

master对小表t1的DDL传输到slave去应用时，mysqldump正在备份t1表数据，此时会发生MDL锁，从库上t1表的所有操作都会Hang住

③ 情况3

master对小表t1的DDL传输到slave去应用时，mysqldump还没对t1表进行备份，该DDL会在slave的t1表应用成功，但当导出到t1表时，会报“ERROR 1412 (HY000): Table definition has changed, please retry transaction” 错误，导致导出失败！

1.5 有这功能，何需FTWRL？

一致性读是好，那但也得引擎支持这隔离级别。
MyISAM不支持事务，备份过程中若有更新，只能取到最新数据，破坏了备份的一致性，就需要使用FTWRL。

所以single-transaction只适于所有的表使用支持事务引擎的库。一旦有表使用了不支持事务的引擎，则备份只能通过FTWRL。

这也是使用InnoDB替代MyISAM的主因之一。

1.6 那readonly不就够了？

既然要全库只读，何不使用：

set global readonly=true

readonly可让全库只读，但还是推荐FTWRL：

1.6.1 影响面太大

有些系统的readonly值会被用来做其他逻辑，比如判断一个库是主库or备库。因此，修改global变量的方式影响面太大！

1.6.2 异常处理差异

执行FTWRL后，由于客户端异常断开，MySQL会自动释放该全局锁，整库回到可正常更新的状态。

而将整库设为readonly后，若客户端异常，则数据库就一直保持readonly，导致整库长时间不可写。

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

即使没有被全局锁锁住，加字段也不是一帆风顺，还会碰到表级锁。

2 表级锁

表级锁有两种：

2.1 表锁

2.1.1 语法

lock tables … read/write

类似FTWRL：

• 可用unlock tables主动释放锁
• 也可在客户端断开时自动释放

lock tables语法除了会限制别的线程读写，也限定了本线程接下来的操作对象。

表级别的write锁，对本线程可读、写。
若在线程A执行：

lock tables t1 read, t2 write;

则其他线程写t1、读写t2的语句都会被阻塞。

线程A在执行unlock tables前，也只能执行读t1、读写t2的操作。写t1不允许，也不能访问其它表。

对InnoDB这支持行锁引擎，一般不推荐使用lock tables命令控制并发，毕竟锁的粒度过大。

2.2 元数据锁（meta data lock，MDL)

访问一个表时会被【自动加上】，以保证读写的正确性。

若一个查询正在遍历一个表数据，执行期间另一个线程在变更该表结构，删了一列，则查询线程拿到的结果就跟表结构对不上了，这肯定不行啊！

2.2.1 加锁规则

于是MySQL 5.5引入MDL：

• 对一个表做CRUD（DML），加MDL读锁
• 对表做结构变更操作（DDL），加MDL写锁

2.2.2 互斥规则

读锁之间不互斥，因此可多线程同时对一张表CRUD。

读、写锁之间，写锁之间互斥，以保证变更表结构操作的安全性。

所以MDL是为防止DDL和DML的并发冲突，而非解决select和update间的并发。

虽然MDL锁默认会加，但也不能轻视。比如给一个表加字段或修改字段或加索引，需扫描全表数据。即使是个小表，操作不慎也有问题。

2.2.3 案例

假设表t是个小表。

MySQL 5.6。

• S1先启动，对表t加个MDL读锁
• S2加的也是MDL读锁，可正常执行
• S3会被阻塞，因S1的MDL读锁还没释放，而S3需MDL写锁

若只有S3被阻塞还没啥，但之后所有要在表t上新申请MDL读锁的请求也会被S3阻塞。
所有对表的CRUD操作都要先申请MDL读锁，就都被锁住，等于该表此时完全不可读写！

为何被未完成执行的S3阻塞

为确保事务的可序列化，MySQL不允许一个会话对在另一会话中未完成的显式/隐式启动的事务中使用的表执行DDL。

服务器通过获取事务中使用的表上的MDL，并将这些锁的释放推迟到事务结束之前来实现。表上的MDL可防止更改表的结构。这种锁定方法的含义是，一个会话中事务正在使用的表在事务结束前不能被其他会话在DDL语句中使用。

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

若某表的查询语句频繁，且客户端还有重试机制，即超时后会再起一个新session再请求，该库的线程很快就会爆开（全部都在等待）。

事务中的MDL锁，在语句执行开始时申请，会一直等到整个事务提交了再释放。

2.3 Online DDL

MySQL5.6支持Online DDL，对表操作增加字段等功能时，不会阻塞读写，那为啥还会出现上述案例的结果？

2.3.1 Online DDL过程

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

1、2、4、5若无锁冲突，执行时间很短。第3步占用了DDL绝大部分时间，这期间该表可正常读写数据，因此称为Online。

但案例中，第1步就阻塞了。所以必须等待之前的事务全部提交后，才能开始执行。

2.4 如何正确加字段？

先要解决长事务，事务不提交，就会一直占MDL锁。

在MySQL的information_schema 库的 innodb_trx 表中，可查到当前执行中的事务。

若你要做DDL变更的表刚好有长事务在执行，要考虑：

• 原地等待，先暂停DDL
• 或kill掉这长事务

但若变更的表是一个热点表，虽数据量不大，但对表请求频繁，又不得不加字段，咋办？
在alter table语句设定等待时间，若在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

MariaDB已经合并了AliSQL的这个功能，所以这两个开源分支目前都支持DDL NOWAIT/WAIT n语法。

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

主库上的一个小表做了一个 DDL，同步给slave，由于这时有了先前的single-transaction，所以slave就会出现该表的锁等待，并且slave出现延迟。

2.5 哪些操作会加表级锁？

• 执行增删改时，默认加行锁
• 然后执行DDL语句时，如alter table，会默认在表级别加表锁

这么说也不太正确，但也有一定道理，因为确实你执行DDL时，会阻塞所有增删改操作； 执行增删改时，会阻塞DDL操作。

InnoDB提供了自己的表级锁，跟这里DDL语句用的元数据锁不是一个概念。只不过DDL语句和增删改操作，确实互斥。

2.6 再谈表锁

MySQL的表锁很鸡肋，几乎很少用，表锁分为：

• 表锁
• 表级的意向锁

2.6.1 语法

# 加表级共享锁
LOCK TABLES xxx READ

# 加表级独占锁
LOCK TABLES xxx WRITE

几乎没人会用这两个语法加表锁。

另外两个情况会加表级锁

• 若有事务在表里执行增删改操作，那在行级会加独占锁，此时同时会在表级加一个意向独占锁
• 若有事务在表执行查询操作，会在表级加一个意向共享锁

平时操作DB常见的两种表锁：

• 更新操作加的意向独占锁
• 查询操作加的和意向共享锁

但这意向独占锁和意向共享锁暂时可当是透明的，因为两种意向锁不互斥。

因为假设有个事务在表里更新id=10的一行数据，在表上加个意向独占锁，此时另外一个事务要在表里更新id=20的一行数据，也会在表上加一个意向独占锁，明显不应互斥，因为他们俩更新表里不同数据，让他们俩在表上加的意向独占锁互斥有何意义？ 所以意向锁间不会互斥。

同理，假设一个事务要更新表里的数据，在表级加个意向独占锁，另外一个事务要在表里读数据，在表级加个意向共享锁，此时表级的意向独占锁和意向共享锁应互斥吗？不应该！一个人要更新数据，一个人要读取数据，俩人在表上加的意向锁，凭什么互斥？

因此，所谓的表级的意向独占锁和意向共享锁，似乎是脱了裤子放屁？

但：

• 手动加表级【共享锁】和【独占锁】
• 及更新和查询时自动在表级加的【意向共享锁】和【意向独占锁】

之间反而有一定互斥关系：

在表上面：

• 手动加的独占锁、共享锁
• 更新数据和查询返数回据默认自动加的意向独占锁、意向共享锁

锁是互斥的，所以更新数据自动加的表级【意向独占锁】，和你用

# 手动加了表级独占锁
lock tables xxx write

此时任何人都不能执行更新操作。

或你用

# 手动加【表级共享锁】
lock tables xxx read

则任何人也不能执行更新操作，因为更新就会默认加意向独占锁，和手动的表级共享锁互斥。

【手动】加表级的共享锁或独占锁，此时会阻塞其他事务的一些正常读、写操作，因为和他们自动加的【意向锁】都互斥。然而一般都不会去手动加表级锁，所以一般读写操作自动加的表级意向锁，互相之间不会因为互斥而导致阻塞。

一般都是：

• 对同一行数据的更新操作加的行级独占锁之间互斥
• 跟读操作都不互斥，读操作默认都走MVCC读快照版本

3 总结

全局锁主要用于全库逻辑备份。对于全部是InnoDB引擎的库，推荐–single-transaction参数。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有lock tables这样的语句，你需要追查一下，比较可能的情况是：

• 要么是你的系统现在还在用MyISAM这类不支持事务的引擎，那要安排升级换引擎
• 要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了

MDL会直到事务提交才释放。

参考

• https://blog.csdn.net/sz85850597/article/details/106993582
• https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html