根据加锁粒度，知道MySQL有哪些锁吗？

• 全局锁
• 表级锁
• 行锁

首先明确：全局锁和表锁实现在Server层

1 全局锁

对整个数据库实例加锁。

加全局读锁的命令是什么？

Flush tables with read lock (FTWRL)

当需要让整个库只读时，可使用该命令，之后其他线程的以下语句会被阻塞：

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

但注意 FTWRL 前有读写的话，FTWRL 都会等待，读写执行完毕后才执行。

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

1.1 适用场景

1.1.1 全库逻辑备份

把整库的每个表都select出来存成文本。

历史上流传着一种做法，通过FTWRL确保不会有其他线程对数据库更新，然后对整库备份。

备份过程中，整个库都处于只读。

但让整库都只读，听着就危险：

• 若你在主库备份，则备份期间都不能执行更新，业务基本歇业
• 若你在从库备份，则备份期间从库不能执行主库同步过来的binlog，导致主从延迟

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

备份为何要加锁？

让我们来看不加锁会产生的问题。假设现在维护购课系统，关注用户账户余额表(u_account)、用户课程表(u_course)。

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

若时间顺序：

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

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

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

mysqldump

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

官方的逻辑备份工具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” 错误，导致导出失败！

有这功能，何需FTWRL？

一致性读是好，那也得引擎支持这隔离级别。

MyISAM这类不支持事务的引擎，备份过程中有更新，总是只能取到最新数据，破坏了备份的一致性，就需要使用FTWRL。

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

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

readonly不就行？

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

set global readonly=true

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

影响面太大

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

异常处理差异

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

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

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

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

2 表级锁

表级锁有两种：

2.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)

无需显式使用，在访问一个表时会被自动加上，以保证读写的正确性。

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

于是MySQL 5.5引入MDL：

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

读锁之间不互斥，因此可多线程同时对一张表增删改查。读写锁之间、写锁之间互斥，以保证变更表结构操作的安全性。所以MDL作用是防止DDL和DML并发的冲突，而非解决select和update之间的并发。

虽然MDL锁默认加，但不能轻视。

比如有人只是给一个小表加个字段，就能把整个库搞挂。

给一个表加字段或修改字段或加索引，需扫描全表数据。即使是小表，操作不慎也有问题。

假设表t是一个小表。

MySQL 5.6。

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

若只有S3被阻塞还没啥，但之后所有要在表t上新申请MDL读锁的请求也会被S3阻塞。

所有对表的增删改查操作都要先申请MDL读锁，就都被锁住，等于这个表此时完全不可读写。

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

为了确保事务可序列化，mysql不允许一个会话对在另一会话中未完成的显式或隐式启动的事务中使用的表执行DDL语句。服务器通过获取事务中使用的表上的元数据锁并将这些锁的释放推迟到事务结束之前来实现。表上的元数据锁可防止更改表的结构。这种锁定方法的含义是，一个会话中事务正在使用的表在事务结束之前不能被其他会话在DDL语句中使用。

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

若某个表上的查询语句频繁，且客户端还有重试机制，即超时后会再起一个新session再请求，该库的线程很快就会爆满。

事务中的MDL锁，在语句执行开始时申请，但语句结束后并不会马上释放，会一直等到整个事务提交了再释放。

Online DDL

MySQL5.6支持Online DDL，也就是对表操作增加字段等功能，不会阻塞读写，为啥还会出现案例结果呢？

首先搞清楚Online DDL的过程：

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

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

但我们的例子，在第一步就阻塞了。

2.3 如何如何正确加字段呢？

首先要解决长事务，事务不提交，就会一直占着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出现延迟。

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

/