3.全局锁和表锁2022-02-15

根据加锁的范围，mysql的锁可以分为全局锁、表锁和行锁。

一、全局锁

全局锁让整个数据库处于一个只读状态。

1.语句

Flush table with read lock(FTWRL)

2.使用场景

全局备份，把整个表select出来形成文本。

3.使用全局锁的方法

（1）Flush table with read lock 命令：建议使用，会锁住全局，不管是InnoDB引擎还是MyISAM引擎都会被锁住。
（2）官方自带工具mysqldump使用single-transaction方法，但是此方法对MyISM引擎不适用。
（3）set global readonly = true,但是有两个缺点：一是会用来判断主库和备库，修改后影响面大；二是客户端发生异常后，会保持readonly状态，会导致整个库长时间不可写。

二、表级锁

Mysql中的表锁有两种：一种是表锁、一种是锁元数据（meta data lock）MDL

1.表锁（多线程不建议使用）

表锁的语句：

lock table t1 read t2 wirte

解锁的语句：

unlock tables

表锁后会使得其它线程的写t1和读写t2操作会被阻塞，连当前线程的读t1操作也会被限制。所以对于InnoDB这种支持行级锁的引擎，一般不用表锁。

2.MDL

meta data lock表级锁，线程之间读锁不会互斥，写锁才会互斥，会让线程写完后释放锁

（1）避坑指南：怎么在表中增加一个字段?

因为写操作会获取写锁，如果此时有访问线程有读取的操作，读锁还没有被释放，那么此时写操作会被阻塞，更麻烦的是之后的读操作也会被阻塞。相当于整个表都不能读写了。

（2）如何安全的在表中增加一个字段呢？

思路1.杀掉长事务
可以在information_schema 库的 innodb_trx 表中查到当前执行的事务，并杀掉。但是未必管用，因为请求随时都可能会来。
思路2.获取写锁，增加重试
比较理想的机制是alert table 的语句里面获取到写锁，如果没获取到增加重试机制。
MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

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

三、思考题

1.理解DML、DDL、DCL区别 .

DML(data manipulation language)：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

DDL(data definition language)：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

DCL(Data Control Language)：是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括(grant,deny,revoke等)语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

2.题目

备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

以下是备份表经历的几个步骤

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1)

Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
/* other tables */

启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)

Q3:SAVEPOINT sp;

设置一个保存点，这个很重要（Q3）

/* 时刻 1 */
Q4:show create table t1;

show create 是为了拿到表结构 (Q4)

/* 时刻 2 */
Q5:SELECT * FROM t1;

然后正式导数据（Q5）

/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;

回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁（Q6）

/* 时刻 4 /
/ other tables */

1.如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
2.如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
3.如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
4.从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

3.全局锁和表锁2022-02-15