一文看懂MySQL的全局锁和表级锁

全局锁

全局锁就是对整个数据库进行加锁， MySQL提供了一个加全局锁的方法，命令是： Flush tables with read lock ，简称：(FTWRL) ，当使用这个语句的时候，整个库会变的只能读，可读。其他命令：像DML（增删改查），DDL（修改表结构）和更新类事物的语句提交等这些命令都会被阻塞。

可以用 unlock tables主动释放锁。

适用场景：全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。有两种情况：

​ ①：如果在主库上进行备份，那么在备份期间都不能更新，线上的业务基本就停摆了。

​ ②：如果在从库上进行备份，那么 在备份期间都不能执行主库同步过来的binlog，会导致主从延 迟，并且从库读出来的数据可能不是最新数据。

问题：备份数据库为什么要加锁？

如果不加锁的情况下，可能会导致数据不一致。

就比如说电商吧，有一个余额库balance，有一个订单库order。

如果先备份余额库，那么余额库肯定是会不能进行任更新的，只能查，那么有这种case，订单有了，余额没扣，如果用这个备份来恢复数据的话，这时候用户笑了，公司亏了，白嫖一单，公司哭了，程序员哭了。

如果先备份订单库，case，余额扣了，订单没生成。如果用这个备份来恢复数据的话，公司赚了，用户哭了，其实公司也没赚，名声不好了，造成的亏损更大，某某程序员明天不用来上班了。

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

你一定在疑惑，有了这个功能，为什么还需要 FTWRL 呢？一致性视图是好，但前提是引擎要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。所以，single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

问题 那为什么不使用set global readonly = true呢？

简单的说，FTWRL在出现异常的时候会自动释放全局锁。而这个它不会在出现异常的时候释放全局锁，数据库会在很长的一段时间之内不可写，这种行为非常危险。

表级锁

表级锁有两种：一种是表锁，一种是元数据锁（meta data lock），也叫MDL。

表锁的语法是 lock tables … read/write ，与FTWRL类似，也可以用unlock tables主动释放锁，也可以在客户端断开连接的时候自动释放锁。

加上读锁，不会限制其他的线程读，但是会限制其他线程写。

加上写锁，限制其他线程读写，本线程可以进行读写。

总结：读锁读共享，写锁是独占的。

元数据锁（MDL）

MDL不需要显示使用，在访问一个表的时候会自动被加上。

读锁之间不互斥。加读锁则所有线程可正常读元数据，不影响增删改查操作，但是不允许更改表结构。

读写锁之间、写锁之间互斥，用来保证表结构变更的安全性。如果有两个线程都要想给一个表加字段，那么只能等待另一个执行完再开始执行。

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

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

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

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

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 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 ...  

MySQL的行锁（也是我写的）：MySQL的行锁解析