06|06 | 全局锁和表锁 （给表加个字段怎么有这么多阻碍（（待评论）））
MySQL 的锁：全局锁、表级锁和行锁
全局锁（FTWRL ：Flush tables with read lock ）
整库实例加锁。整库只读态，其他线程被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
典型使用场景：做全库逻辑备份。整库只读危险
（1）主库备份，业务停；
（2）从库备份，不能执行主库同步binlog，主从延迟。
（3）备份不加锁：用户账户余额表和课程表
备份期间，用户购买，先备份账户余额表 (u_account)，购买，再备份用户课程表 (u_course)；
备份结果：“余额没扣，多了课”。
文章图片
图 1 业务和备份状态图 官方自带备份工具 mysqldump。用–single-transaction时（只适用于所有的表使用事务引擎的库），导数据之前启动事务，确保拿到一致性视图（可重复读隔离级别）。 MVCC 支持，过程可以正常更新。
为什么还需要 FTWRL 呢？一致性读是好，前提引擎要支持这个隔离级别。 MyISAM 不支持事务引擎，备份过程中更新，只能取到最新数据，破坏了一致性。这时需 FTWRL 命令。
既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
（1）readonly 值被用来做其他逻辑，如判断主/备库，修改 global 影响大.
（1）异常处理有差异。FTWR异常，自动释放，readonly 异常，一直readonly 状态，不可写状态。
业务更新不只增删改数据（DML)，还可能加字段等修改表结构的操作（DDL）全局锁会锁。
二、表级锁
表级锁：（1）表锁，（2）元数据锁（meta data lock，MDL)。
1、lock tables … read/write。 FTWRL 类似， unlock tables 主动释放锁，断开时自动释放。限制别的线程读写，也限定本线操作对象。
线程 Alock tables t1 read, t2 write;
其他线程写 t1、读写 t2 被阻塞。 unlock tables 之前，只能读 t1、读写 t2 。
InnoDB 支持行锁，不使用 lock tables 命令，锁表影响大。
2、MDL（metadata lock)。不需显式使用，访问表时自动加上。保证读写正确性。查询时，表结构变，查询结果跟表结构对不上。
增删改查时，加 MDL 读锁（读锁之间不互斥）；表做结构变更，加 MDL 写锁(保证变更表结构安全)。事务提交才释放，结构变更时，不要导致锁住线上查询和更新。
MDL 锁是系统默认加，但却是你不能忽略的一个机制。给小表加字段，导致整个库挂了。
给表加字段，修改字段，加索引，需要扫描全表。小表操作不慎也会出问题。
文章图片
session A 先加 MDL 读锁。session B 也MDL 读锁。（读锁不冲突，隔离级别原因）
session C 写锁被 blocked，A 读锁没释放。之后新请求被C 阻塞。表完全不可读写。
重试机制，再起session请求，爆满，内存升高。
ps：没有begin的话，select执行完成，MDL自动释放
如何安全地给小表加字段？
解决长事务，事务不提交，占着 MDL 锁。information_schema库 innodb_trx 表中，查到当前执行中长事务。要做 DDL 变更，kill 掉长事务。
热点表加个字段
kill 未必管用，新请求来。alter table 设定等待时间，拿到 MDL 写锁最好，拿不到也不要阻塞后面，通过重试命令重复这个过程。
MariaDB 已经合并了 AliSQL 的这个功能，都支持 DDL NOWAIT/WAIT n 这个语法。
ALTER TABLEtbl_name NOWAIT add column ...
ALTER TABLEtbl_name WAIT N add column ...
小结
（1）全局锁：逻辑备份。InnoDB 引擎库，用–single-transaction更好。
（2）表锁：不支持行锁时用。程序里有 lock tables 追查，可能情况：
1）用 MyISAM 不支持事务的引擎，换引擎；
2）引擎升级了，代码没升级。lock tables 和 unlock tables 改成 begin 和 commit，问题解决
MDL事务提交才释放，表结构变更时，不锁住线上查询和更新。
问题：
备库上执行备份，用–single-transaction方法，主库上小表做 DDL（），加一列。从备库上会看到什么现象呢？关键语句：
DDL: CREATE TABLE/VIEW/INDEX/SYN/CLUSTER；隐性提交，不能rollback
Q1:SET SESSIONTRANSACTION ISOLATION LEVEL REPEATABLE READ;
确保 RR（可重复读）隔离级别，再设置一次
Q2:STARTTRANSACTIONWITH CONSISTENT SNAPSHOT；启动事务用，确保执行完，得一致性视图
Q3:SAVE POINT sp;
设置保存点/* 时刻 1 */
Q4:show createtable `t1`;
拿到表结构/* 时刻 2 */
Q5:SELECT * FROM`t1`;
导数据/* 时刻 3 */
Q6:ROLLBACK TOSAVEPOINT sp;
/* 时刻 4 */ 回滚到 SAVEPOINT，释放 t1 的 MDL 锁
1. 拿到表结构Q4 之前到达，没有影响，备份的是 DDL 后
2. “时刻 2”到达，表结构改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，mysql dump 终止；
3. “时刻 2”和“时刻 3”之间到达，mysql dump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
4. “时刻 4”开始，mysql dump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。
评论1
mysql 5.6不是支持online ddl了吗？增加字段，实际上不阻塞读写？
DDL过程：
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁
1、2、4、5如果没有锁冲突，执行时间非常短。第3步占DDL大部分时间，期间表正常读写，称“online ”.文中例子，第一步就堵住
评论2
FTWRL 前有读写 ,要等待完执行。执行时要刷脏页数据到磁盘,保持一致性
【06|06 | 全局锁和表锁 （给表加个字段怎么有这么多阻碍（（待评论）））】MDL 并发时维护一致性,有事务时,不可对元数据写,server层实现。
推荐阅读
- 急于表达——往往欲速则不达
- 第三节|第三节 快乐和幸福（12）
- 20170612时间和注意力开销记录
- 2.6|2.6 Photoshop操作步骤的撤消和重做 [Ps教程]
- 对称加密和非对称加密的区别
- 眼光要放高远
- 樱花雨
- 前任
- 2020-04-07vue中Axios的封装和API接口的管理
- 烦恼和幸福