数据库锁的设计的初衷是为了解决并发问题，根据加锁的范围，Mysql 中的锁可以分为全局锁，表级锁和行锁三种。

6.1、全局锁

全局锁就是对整个数据库实例进行加锁，Mysql 提供了一个加全局读锁的命令：Flush tables with read lock (FTWRL 用读锁刷新表)，当你需要让整个库处于只读状态时，可以使用这个命令，之后会导致其他线程的以下语句被阻塞：数据更语句（数据的增删改），数据定义语句（包括建表，修改表结构等）和更新类事务的提交语句。
全局锁的典型的使用场景是做全库逻辑备份，也就是将数据库中的所有表对象使用 select 出来存储成文本。以前有个说法就是使用 FTWRL 确保不会有其他线程对数据库做更新，然后再对整个数据库进行整体的备份，注意的是，再备份过程中整个数据库处于只读状态。
由于使用全局锁的缺点
- 如果在主库上使用全局锁进行备份期间，使得主库处于只读状态，导致一些业务操作不能执行处于停摆的状态。（主库使用全局锁不能执行客户端的写命令请求）
- 如果在从库上使用全局锁进行备份期间，会导致从表不能执行主库同步过来的 binlog，从而导致主从同步延迟增加。（从库使用全局锁不能执行主库同步过来的 binlog 使得主从延迟增加）
- 但是如果在数据库备份期间，如果不使用全局锁，由于 Mysql 的 MVCC(多版本并发)，导致不同时刻的视图上的数据会有更新，使得备份的数据因此出现错误，但是使用事务隔离中的可重复读的事务隔离级别可以实现事务开启期间读取视图上的数据与事务启动的看到的视图上的数据保持一致因此不用担心在备份期间，不同时刻读取视图上的数据发生改变。
Mysql 中自带的数据库备份工具 mysqldump
- 当 mysqldump 使用参数 single-transaction 时，导数据前就会启动一个事务，来确保拿到一致性的视图。而由于 Mysql 的 MVCC （多版本并发）因此在此期间数据库中是可以进行数据正常更新的。
- 虽然有 Mysql 中自带的巩固 mysqldump 这个工具实现数据备份，那么为什么还要有 FTWRL (Flush Table With Read Lock) ，原因是由于工具 mysqldump 的实现原理时采用可重复读的事务隔离级别来实现备份期间获取一致性视图方式实现备份的一致性。但是并不是所有的引擎都支持事务，其中 MyISAM 引擎就不支持事务，因此不能使用 mysqldump 工具达到数据备份的效果，就只能使用 FTWRL 使用全局锁的方式实现数据备份的一致性。这往往也是 InooDB 代替 MyISAM 的原因之一。
虽然使用 set gloable readonly=true （设置全局只读状态为 true）命令仍然可以使得数据库进入只读状态，但是推荐还是使用 FTWRL 全局锁的方式，其中主要以下两个原因
- 在有些系统中，readonly 的值被用来做其他的逻辑，比如判断该数据库是主库还是从库（从库一般被设置为只读状态），因此，修改 gloable 全局变量的方式其影响太大，不建议使用
- 在异常处理机制上，如果采用 FTWRL 命令之后客户端发生了异常断开，那么 Mysql 会自动释放全局锁，整个数据库进入正常更新状态；但是如果采用设置 gloable 全局变量 readonly 为 true 的方式，之后客户端发生异常断开，其数据库任然保持 readonly 只读状态，导致整个数据库不能进入可写状态，不能进行正常的更新操作，因此相比较而言后者的风险较高。

6.2、表级锁

Mysql 中的表级锁有两种：表锁和元数据锁（meta date lock MDL）

表锁

表锁的语法是 lock tables …read/write，与 FTWRL 类似。可以使用 unlock tables 主动释放锁，也可以在客户端断开连接的时候释放锁，需要注意的是：lock tables 除了会限制别的线程的读写也会限制本线程接下来的操作对象。
- 例如：在线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程不能对表 t1 和 t2 进行读写操作，否则被阻塞，并且线程 A 自己根据语句中的规定只能对表 t1 进行读操作，对表 t2 可以进行读写操作，并且不能在释放锁之前不能访问其他的表。
在没有更细粒的锁以前，表锁是最常使用用于处理并发的方式，而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表对象其代价太大。

MDL（meta data lock）元数据锁

MDL 不需要显式的使用，在访问一个表的时候会被自动加上，当需要对表进行增删改查操作的时需要获取该表的 MDL 读锁，读锁之间并不排除，即允许多个线程对同一张表进行增删改查操作。当需要对表结构进行修改（即 DDL 操作，例如：增删字段，增删索引都需要对整张表进行扫描）则需要该线程获取该表的 MDL写锁，读写锁之间，写锁之间是不能并发对同一张表进行访问的，其目的是为了保证表读写的正确性。
如果对表添加字段或者修改字段或者增加索引（修改表结构，需要获取 MDL 写锁），重建索引时都需要进行对整个表扫描，此时如何小心安全地给表进行加字段呢？
- 首先我们需要解决长事务，事务不提交会导致长时间地占用锁资源而导致阻塞其他线程，在 Mysql 中 information_schema 库中地 innodb_trx 表中，可以查看到当前正在执行地事务，如果你需要进行 DDL 的表中此时有长事务正在执行，要考虑暂停对该表的 DDL 操作，或者是 kill 掉这个事务。但是如果该表中有频繁的请求进来，那么此时采用 kill 可能未必管用，可能前脚刚 kill 掉可能后者又有新的请求开启长事务，但是不得不要加个字段的情况下，比较理想的机制就是在 alter table 语句的里面设定等待时间，如果在指定的等待时间里面能够拿到 MDL 写锁最好，如果拿不到就直接抛出错误返回，不去阻塞后面的业务语句的执行，选择先放弃，之后再通过重试命令重复进行这种尝试获取的操作的过程。使用的语句有两种方式：
  - Alter Table t_name Nowait and column…
  - Alter Table t_name Wait N add column…

6.3、小结

全局锁主要使用在进行逻辑备份的过程中，对于全部使用 InnoDB 引擎的数据库而言，最好采用的是工具 mysqldump 使用 single-transaction 参数，对应用会更加友好。
表锁一般在不支持行锁的数据库引擎中使用到，如果发现应用程序中有 lock tables 这样的语句，则需要追查一下是否有一下两种情况
- 如果系统现在使用的引擎仍然是 MyISAM 这种不支持事务的引擎，那么可以安排升级引擎
- 如果引擎升级了，但是代码没有升级，最后业务开发就是把 lock tables 和 unlock tables 换成 begin 和 commit 即可。

6.4、问题

备份一般在备库中进行，如果此时使用 single-transaction 方法做逻辑备份的时候，如果在主库上的一个表中做了 DDL 操作，比如在表中添加了一列，这时候在备库中看到什么现象？
首先看下其其备库的过程
- Q1：set session transaction isolation level repeatable read;
- Q2：start transaction with consistent snapshot;
- Q3：savepoint;
- （时刻 1）
- Q4：show create table ‘t1’;
- （时刻 2）
- Q5：select * from ‘t1’;
- （时刻 3）
- Q6：rollback to savepoint sp;
- （时刻 4）
备份过程的详情
- Q1：备份开始前，为了确保可重复读的隔离级别会在设置一次 repeatable read(RR 隔离级别)
- Q2：启动事务，其中这里使用 consistent snapshot（一致的快照(视图)）确保可以得到一致性的视图
- Q3：使用 savepoint 设置一个保存点用于之后的回滚操作可以指定回滚的位置。
- Q4：show create table 获取表结构，回滚到设置的保存点的位置之后释放表 t1 的 MDL 锁，之后开始正式的导出表数据。
- Q5：select 获取表数据
- Q6：rollback 回滚到开始获取表结构之前的位置。
DDL 执行操作在四种不同位置的影响
- 1.如果 DDL 在时刻 1 的位置执行，此时是在获取表结构之间执行的，因此其备份后的表结构是 DDL 后的表结构，不会对备份过程有任何的影响。
- 2.如果 DDL 在时刻 2 的位置执行，此时在此之前已经获取了表 t1 的结构，因此之后再进行 DDL 操作修改了表 t1 的结构会导致之后导出表数据时抛出：Table definition has changed, please retry transaction（表结构发生了修改，请重试事务处理），产生的现象：mysqldump 终止；
- 3.如果 DDL 在时刻 2 和时刻 3 之间执行，由于此时 mysqldump 占有了表 t1 的 MDL 锁，binlog 被阻塞，导致主库中的 SQL 被积压，不能同步更新服务器中，就发生了主从延迟。直到执行完 Q6 语句，备份完成之后，mysqldump 释放表 t1 的 MDL 锁，binlog 结束阻塞，则 DDL 操作能够结束阻塞执行。
- 4.如果 DDL 在时刻 4 执行，此时由于备份已经完成了，备份的是 DDL 前的表，因此不会对备份过程有任何影响。

原文链接：https://blog.csdn.net/wmt0501/article/details/124123217