MySQL45讲笔记——全局锁与表级锁

锁

全局锁

使用Flush tables with read lock(FTWRL)对库加全局读锁，加锁之后整库处于只读状态。使用场景 全库逻辑备份。

在可重复读隔离级别下，Mysqldump使用-single-transaction参数，在导数据之前会启动一个事务确保一致性视图，但前提在于引擎需要支持隔离级别。

为什么不用 set global readonly = true ?
一个是有些系统中readonly被用作逻辑操作，影响面较大，例如判断库是否主库
另一个是执行命令后发生异常，Mysql会自动断开FTWRL全局锁，而readonly在异常之后，数据库会一直保持readonly状态。

表级锁

分为表锁，元数据锁。

表锁

表锁语法 lock tables … read / write。 使用unable tables 释放或断开自动释放。
对于锁定资源的线程，只允许对锁表进行读/读写，不允许对其他表进行读/写操作。
对于其他线程，只允许对被锁定的表进行读/nul操作，对其他表无影响

元数据锁（MDL）

在执行语句是自动加锁，锁住表的数据结构等元信息。
对表进行增删查改是加MDL读锁；在对表结构进行变更时，加MDL写锁；锁在整个事务提交时才会释放。
读锁之间不互斥，因此可以有多个线程对同一张表进行增删查改。
读写，写之间互斥，用来保证变更表结构的安全性。

如何安全的给小表加字段：
先解决长事务，事务不提交会一直占用MDL锁。
若请求频繁，理想机制是在alter语句中加等待时间，Mysql8.0.19暂不支持。

问题：

使用-single-transaction备份时，主库上对小表增加一个字段，备库会看到什么现象？

假设如下为备库执行备份的执行步骤：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; //确认隔离级别
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；//确定一个一致性视图
/* other tables */
Q3:SAVEPOINT sp;//设置保存点
/* 时刻 1 */
Q4:show create table `t1`;//获取表结构
/* 时刻 2 */
Q5:SELECT * FROM `t1`;//获取表数据
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;//回滚保存点，释放MDL读锁
/* 时刻 4 */
/* other tables */

分析DDL语句的到达时间：
1.在Q4语句前到达，由于还并未加MDL读锁，所以值行不会受影响，会在备份MDL执行之后的表。
2.在时刻二到达，表结构被修改过，Q5执行的时候报 Table definition has changed，please retry transaction，现象：mysqldump终止。
3.在时刻2到时刻3之间到达，因为Q5语句加MDL读锁，DDL语句会被阻塞，主从延迟。直到Q6执行完成。
4.在时刻4到达，已经释放MDL锁，没有影响，备份的是DDL前的数据。