三、全局锁和表锁详解（Mysql实战45讲笔记-基础）
5. 全局锁和表锁
5.1 全局锁
# 对整个数据库实例加锁，加锁后只能读
flush tables with read lock(FTWRL)
# 释放全局锁
unlock tables
5.1.1 使用全局锁的场景：
在某些情况下，我们需要使用到全局锁，比如在做全库逻辑备份。
5.1.2 不使用全局锁会出现的问题：
备份数据和源数据不是一个逻辑时间点，视图是逻辑不一致的。
我在商城种购买一个商品，我付钱后，余额会减少，购买的商品会增多。
1. 现在需要备份商品表和用户余额表，先备份了余额表
2. 此时备份表中我的余额有100元，我在这个时间购买了商品，我的商品表中也有了我新买的产品
3. 而后备份了商品表，则在备份表中，我的余额并没有减少，我所购买的商品也有了。
5.1.3 使用全局锁的弊端：
- 在主库上备份，则在备份期间，不能在数据操作，业务停摆
- ==？在从库上备份，备份期间不能执行主库同步过来的binlog，导致主从延迟==
5.1.4 如何使用全局锁进行数据备份:
进行数据备份，要保证的就是和源数据是从一个逻辑时间点进行备份，在整个备份的事务中的视图都是一致的，与可重复读事务级别相同。
mysql官方自带的逻辑备份工具是mysqldump,当使用参数-single-transaction的时候，导数据之前会启动一个事务，保证整个事务拿到一致性视图。
5.1.5 mysqldump中-single-transaction和FTWRL区别：
single-transaction 方法只适用于所有的表使用事务引擎的库，MyISAM就不持支持事务，就需要使用FTWRL
5.1.6 set global readonly=true与FTWRL的区别：
将表设置为全局只读状态，也能实现只读的需求，但是要不建议使用，原因：
- 有的系统readonly作为判断一个库是主库还是备库，影响范围比较大
- 在异常处理机制上有差异
- 执行FTWRL后遇到客户端异常断开，mysql会自动释放全局锁，整个库恢复正常状态
- 设置readonly后，若客户端异常断开，会导致整个库出于不可写状态，风险比较高
5.2 表级锁
表级锁有两种：
- 表锁
- 元数据锁（meta data lock)
表锁使用的场景并不多，一般都是引擎不支持行锁的时候才会用，
5.2.1 表锁
# 锁表，写是排他锁，写锁意味着其他线程不能读也不能写。读锁是共享锁，加上后其他锁只能读不能写，本线程也不能写
lock tables … read/write
# 其他线程：t1只读，t2不能读写 ，当前线程：t1只读，t2能读能写
lock tables t1 read, t2 write;
# 释放锁
unlock tables
5.2.2 表锁:元数据锁（meta data lock）
mysql5.5之后引入了MDL，MDL在访问表时会自动加上，不需要显式使用
5.2.3 MDL的作用：
保证读写的正确性。若没有MDL，一个查询执行中遍历表中数据， 执行之前另外的线程对表结构做出更改，删除一列，则查询结果和表结构不符。
5.2.4 MDL的读写锁的作用时间：
- 对一个表进行增删改查操作时，加MDL读琐
- 修改表结构，加MDL写琐
5.2.5 MDL读锁写琐之间作用关系：
- 读锁之间不互斥，多个进程可以同时对表增上改查
- 读写锁、写琐之间互斥，保证更改表结构操作的安全性。如果两个线程同时对一个表加字段，串行执行，后来的等待。
5.2.6 需要注意MDL的机制，避免把数据库拖垮：
- session a、session b执行的时候，都是读锁，不互斥，不影响
- session c执行的时候是写锁，和session a 和session b 的读锁互斥，被阻塞
- 因为session c被阻塞，导致session d也被堵塞，之后的所有增删改查操作都被阻塞
- 如果客户端还有重连机制，超时后又重新开session发请求，很快这个库的线程就满了
- 整个库被拖垮
5.2.7 如何安全地给小表（数据量小）加字段：
-
首先解决长事务，事务不提交，就会一直站着MDL锁
-
通过查information_schema 库的 innodb_trx 查询执行中的事务，可以先暂停DDL或者kill这个长事务后执行ddl的语句，但是kill可能也不管用，
-
在alter table 语句里面设定等待时间，若等待时间内拿到MDL写锁，就正常执行，若等待时间拿不到，就放弃执行，这样也不会阻塞之后的业务。
# mysql 8.0 后才有这个语法，这个功能是AliSQl的功能，MariaDB 已经合并了这个功能 # MariaDB、AliSQl是mysql的分支，由开源社区维护，alisql和MariaDb都有这个功能 ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...
注意：该方法的前提是小表，数据量不高，可以重复通过过时时间不断尝试获取mdl锁来去执行ddl 语句，因为即使扫描全部数据也用不了多少时间。
