六、全局锁、表级锁和行锁

MySQL 为什么要设计锁？ 当多个请求并发时，数据库需要合理地控制资源的访问规则，而锁就是用于实现这些访问规则的重要数据结构。

根据加锁的范围，MySQL的锁大致可以分为：全局锁、表级锁和行锁

全局锁

FTWRL
全局锁，指的就是对整个数据库实例加锁。
MySQL 提供了一个加全局锁的方法： Flush tables with read lock (FTWRL)。当使用这个命令后，整个库就处于了只读状态，数据更新语句（DML）、数据定义语句（DDL）和更新类事务的提交语句都会被阻塞。

使用全局锁的典型场景是，做全库逻辑备份。备份过程中，整库都处于只读状态，这是因为，为了确保备份和数据库能在一个逻辑时间点，对应的视图是一致的。以保证能用备份恢复某个时间点的准确的数据库状态。

数据库备份，是对表一张张备份的，如果备份时，数据库实例不处于只读状态，那么就可能出现：在时间点T，进行备份表A，表B。 先备份好表A，同时表B的数据被修改，再备份表B时，就是备份的修改过后的表B’。 也就是备份成了 表A，表B’。那么备份出来的数据，就不是同一逻辑时间视图一致的了。

但是，让整库都处于只读状态，其实是很危险的。 这意味着在备份期间，是不能处理业务逻辑的。

有没有既保证视图一致，又能让数据库能执行更新的办法呢？ 其实在之前有说过“可重复读隔离级别”下开启事务，是能满足以上条件的。

single-transaction
在可重复读隔离级别下，进行数据库备份。 官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 single-transation 的时候，导数据之前就会启动一个事物，来确保拿到一致性视图。 而由于 MVCC 的支持，这个过程是可以正常更新的。

在可重复读隔离级别下备份数据库，再考虑之前的，在时间点T，进行备份表A，表B。 先备份表A，同时表B被修改成表B’，但是在备份事务的视图中，表B还是原来的。 也就是，备份的最终结果为：时间点T的表A和表B。

虽然使用 single-transaction 的方法进行备份更好，但是，这个方法只适用于所有的表都使用了 支持事务的引擎（比如 InnoDB）。如果有的表使用了不支持事务的引擎(比如 MyISAM)，就只能通过 FTWRL 的方法。

现在考虑一个场景，当备库用 -single-transaction做逻辑备份时，如果从主库的 binlog 传来一个 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 */

Q1：备份开始时，为了确保是在可重复读隔离级别下，再设置一次隔离级别为 Repeatable read
Q2：启动事务，使用 with consistent snapshot 确保得到一个一致性视图
Q3：设置保存点
Q4：拿到 t1 表结构
Q5：从 t1 到导数据
Q6：回滚到 savepoint sp，释放 t1 的MDL 锁

现在考虑 DDL 语句在不同时刻到达时的影响：
1、时刻1之前到达，备份拿到的是 DDL 后的表结构
2、时刻2到达，表结构已被修改，执行Q5时会报错 Table definition has changed, please retry transaction。 mysqldump 终止。
3、时刻3到达，mysqldump 占着 t1 的MDL 读锁，binlog 阻塞。 主从延迟，直到 Q6 执行完成。
4、时刻4到达，mysqldump 已经释放了 MDL 读锁。备份拿到的是 DDL 前的表结构。

其实，全库只读的方式还有 set global readonly=true。但是还是建议使用 FTWRL 的方式。原因如下：
一、readonly 的值有可能被用于做其他逻辑，比如判断一个库是否是备库。因此，修改 global 变量的方式影响面更大。
二、在异常处理机制上有差异。如果执行 FTWRL 命令只有，由于客户端发生异常段开，那么 MYSQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。 而修改 readonly 字段，如果客户端发生异常，整个库就一直保持着不可更新的状态。

表级锁

MySQL 中的表级锁有两种，分别是 表锁 和 元数据锁

表锁
表锁的语法是 lock tables T read/write，在客户端段开时自动释放。
lock table A read。这个语句执行后，其他线程不可写表A。
lock table B write。这个语句执行后，其他线程不可读写表B。
但是表锁一般是数据库引擎不支持行锁时才会被用到（比如 MyISAM）

元数据锁 MDL(metadata lock)。MDL是 Server 层的锁，不需要显式使用，在访问一个表时会被自动加上，直到事务提交后释放锁
当对一个表进行增删改查操作，加MDL读锁
当对一个表进行表结构变更，加MDL写锁

• 读锁之间不互斥，可以有多个线程同时对一张表增删改查
• 读写锁之间、写锁之间互斥，用于保证变更表结构操作的安全性。

行锁

行锁是在引擎层实现的，但是不是所有引擎都支持行锁。 InnoDB是支持行锁的，MyISAM 不支持行锁。 不支持行锁意味着处理并发请求只能使用表锁，也就是同一张表，同一时间内只能处理一个更新请求。而使用行锁，就能更大程度提升并发度。

行锁，就是针对数据表中行记录的锁。

两阶段锁
在以下操作序列中，事务B的 update 执行语句会是什么现象呢？假设字段 id 是表 t 的主键

image.png

事务A在执行语句 update t set k=k+1 where id=1; 时，拿到了 id=1 这行的行锁。 在执行语句 update t set k=k+1 where id=2; 时，又拿到了 id=2 这行的行锁。 而这两个锁，在事务A commit 后，才被释放。也就是说，等待事务A结束后，事务B才能拿到 id=1 的行锁。

两阶段协议：在 InnoDB 事务中，行锁是在需要时才加上，等到事务结束才释放

根据这个设定，我们在使用事务时应该注意，如果事务中需要锁多个行，那么要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测
当数据库中的并发事务，出现资源依赖，互相都在等待对方的行锁资源而陷入无限等待时，就出现了死锁。

为了解决死锁问题，可以使用死锁检测，也即是将 innodb_deadlock_detect 设置为 on。事务的某条语句，需要加锁访问的行上有锁时，发起死锁检测。

但是死锁检测也是需要一定代价的。如果有个行锁，同时被多个请求需要，那么就会陷入锁等待。对于后来的每个新来的需要这个行锁的请求，都需要进行死锁检测，判断会不会由于自己的加入导致了死锁，这个时间复杂度为 O(n)。最后判断出来没有死锁，但是死锁检测的过程会消耗大量的CPU资源。

对于以上这种，高并发请求同个行锁的情况，应该在业务角度去控制并发度，以降低死锁检测的成本。
做法一：想办法将这多个请求在进入引擎前排队，或者做到分批请求，控制每批请求的请求量
做法二：从业务角度出发，将个请求到同个行锁的情况，想办法分散请求到多个行锁。