06全局锁和表锁——给表加个字段怎么那么多阻碍笔记

MySQL的全局锁、表级锁

创建时间：2021年11月11日17:30:57

编辑时间：2021年11月12日00:44:26、2021年11月30日10:20:44、2022年6月28日

——————————————————————————————

问：MySQL的锁分哪几种类型？

答：全局锁、表级锁、行锁。

全局锁

问：介绍下MySQL全局锁的含义和使用场景。

答：MySQL提供加全局读锁的方法：

-- 加锁
Flush tables with read lock;

-- 解锁
unlock tables;

此时只允DQL操作，而DML和DDL会被阻塞（对所有线程包括自己）（本质是读写锁：读共享，读写、写互斥）。

主要用于对数据进行备份时保证数据一致性。

问：如果备份时不使用全局读锁，会怎么样？使用的话，又有什么问题？

答：可以采取别的方案，但如果什么都不做的话，备份时数据的变动将导致备份完的数据出问题。

使用的话，需要注意如果只对主库进行，则业务可能停摆，只对从库进行，则会产生主从延迟（主库来的binlog同步从库延迟）

问：除了使用全局读锁，有什么别的合适方案？

答：如果引擎是Innodb，则可以在可重复读隔离级别事务下进行备份。否则，还是要使用加全局读锁的方案。

问：备份有什么操作或重要参数可以做参考？

答：使用 mysqldump工具，只要引擎支持事务，就能使用 –single-transaction 参数， 开启一致性读事务后，在mvcc下进行数据备份操作。

问：你知道set global readonly=true这个参数吗？

答：知道，是设置库只可读。这个操作有时候被用来判断库的主备性质，不能用来做备份的处理，因为它是一个设置，如果备份期间客户端发生异常断连，那库将一直处于只读状态，影响大，反之FTWRL在客户端断连时，会自动释放全局读锁。

表级锁

问：MySQL有哪几种表级锁？

答：分表锁和MDL锁（meta data lock元数据锁）。

-- 加表锁
lock tables T read/write
-- 解锁
UNLOCK tables;

表锁与全局锁类似。

问：什么是MDL锁？

答：5.5版本引入，隐式追加，针对表结构层面的锁，

DML和DQL时加MDL读锁，DDL时加MDL写锁。随事务提交时释放

问：是否存在，当前MDL是读锁，但之后的DQL语句也被阻塞了的情况？

答：存在，申请DML锁的操作会形成一个队列，队列中写锁获取的优先级别高于读锁，只要出现写锁等待，写锁之后的读锁也会进入阻塞状态。

PG：

如果客户端设置了重试机制，那当发生MDL长阻塞时，就可能导致数据库线程爆满。

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

答：加表前处理掉长事务，然后设置等待时间。参数如下：

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

问：怎么操作对表进行备份？

答：（2022-6-28）

set session transaction level repeatable read; -- Q1
start transaction with consistent snapshot;  -- Q2

savepoint sp;  -- Q3
-- 时刻1

show create table `t1`;  -- Q4
-- 时刻2

select * from `t1`;  -- Q5
-- 时刻3

rollback to savepoint sp;  -- Q6
-- 时刻4

设置保存点，以便回滚到保存点，rollback释放MDL锁

问：如果通过-single - transaction对从表进行逻辑备份，此时从主表传来一个DDL语句会如何？

答：以上面为例

Q4前抵达（在执行获取表结构语句前抵达），则从库直接拿到DDL后的结构（因为此时还没追加MDL锁）；

时刻2抵达，由于还没加锁，此时执行修改表结构成功，但select获取的表结构变更，则Q5报错：Table definition has changed, please retry transaction（2022-6-28）

时刻2与3之间抵达（已经执行完获取表结构的语句），则mysqldump操作线程已获得MDL读锁，因此binlog阻塞，等到Q6（事务）执行结束，binlog才同步

时刻4（事务执行结束）时，释放了MDL读锁，binlog不会阻塞，备份（DDL前的表结构）也已经完成。