【MySQL 45讲-6&7】第6&7讲数据库的锁

本文链接：https://blog.csdn.net/qq_41852212/article/details/122991782

总览图

在这里插入图片描述

06 | 全局锁和表锁

锁的设计初衷：处理并发问题

锁的分类
- 全局锁
- 表级锁
- 行锁

全局锁

对整个数据库实例加锁
加全局读锁的命令：
Flush tables with read lock（FTWRL）
- 数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。这些语句会被阻塞
典型使用场景：全库逻辑备份
- 即：把整个库的表都select出来存成文本
- 用mysqldump使用参数-single-transaction来启动一个事务，拿到一致性视图，这样数据就能够正常更新。使用前提: 引擎要支持这个隔离级别。适用使用事务引擎的库。
- InnoDB推荐使用-single-transaction
- MyISAM不支持事务的引擎，因此只能使用FTWRL
- 为何不使用set global readonly=true？
  - 1. readonly会被用来做其他逻辑，比如判断一个库是主库还是备库，所以修改global变量的影响太大了，不建议使用
  - 1. 异常处理机制上有差异。
    - 使用FTWRL，发生异常会自动释放锁
    - 使用readonly，发生异常不会释放锁

表级锁

表锁
- 语法：
  lock tables …… read/write
- 释放锁
  - 1. unlock tables
  - 1. 客户端断开时自动释放

元数据锁MDL

metadata lock

在访问一个表时，MDL会被自动加上。作用：保证读写的正确性

MySQL 5.5 版本中引入了 MDL

当对一个表做增删改查操作的时候，加 MDL 读锁；
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
当要对表做结构变更操作的时候，加 MDL 写锁
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

给一个小表加一个字段，导致整个库挂了？

加字段需要扫描全表数据
事务中的MDL锁，在语句执行开始时申请，语句结束后并不会马上释放，而是等整个事务提交后再释放。

所以在互相等待中，产生了死锁，然后资源消耗完毕。数据库挂掉。

那么，如何给小表安全的加字段？

1. 先解决长事务
在做DDL变更时有长事务：可先暂停DDL或者kill掉长事务
1. alter table中设定等待时间
时间到了自动放弃，不阻塞后面的业务语句，之后DBA在重试任务。

举个栗子

备份一般在备库上执行，再用-single-transaction做逻辑备份时，如果主库上的一个小表做了DDL（加了一列），会有什么现象？

分析

 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;
		/* 时刻 4 */
		/* other tables */

在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);

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

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

show create 是为了拿到表结构 (Q4)，然后正式导数据（Q5），回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁（Q6）。

DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

参考答案如下：

如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。

如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；

如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。

从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。