【MySQL优化(九)】MySQL锁机制

原创已于 2022-12-26 15:49:01 修改 · 410 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #database

于 2022-06-12 12:01:05 首次发布

MySQL 专栏收录该内容

11 篇文章

订阅专栏

本文详细解析了数据库锁设计的初衷，包括全局锁（如FTWRL和readonly）、表级锁（表锁和MDL锁）以及行锁的原理、应用场景和解决策略。特别关注死锁检测与避免，以及如何通过优化并发控制提高系统性能。

锁的作用

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

锁的分类

按作用范围分

1. 全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令如下

Flush tables with read lock (FTWRL)

使用该命令后会将整个库改为只读状态，阻塞所有的数据变更语句（增删改）以及数据定义语句（表结构创建与变更）和更新类事务的提交语句。

应用场景分析：

当需要备份数据库某一个逻辑时间点的快照时，可以使用该语句，使得在备份期间数据不会变化。

风险点：

如果在主库执行，那么备份期间，主库将无法执行更新语句，基本上业务就需要暂停服务
如果在从库执行，那么备份期间，从库无法执行主库同步过来的Binlog，会导致主从延迟

解决方案：

当整个库都使用支持事务的数据引擎表时，假设都是InnoDB表，那么可以利用MVCC机制实现不阻塞更新操作的一致性读。

例如：官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。

问题：

是否可以使用设置全库只读的方式来替代，set global readonly=true。

readonly可以让全库进入只读状态，但是建议使用FTWRL方式，原因如下：

修改global 变量影响范围太大，可能带来其他问题，例如如果有系统使用readonly值来判断是主库还是备库就会导致业务逻辑判断错误。
在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

FTWRL 前有读写的话 ,FTWRL 都会等待读写执行完毕后才执行
FTWRL 执行的时候要刷脏页的数据到磁盘,因为要保持数据的一致性，理解的执行FTWRL时候是所有事务都提交完毕的时候

让数据处于只读的两种方式

全局锁
readonly

保证某一个时刻开始读取数据的过程中数据不再变化的方式

全局锁 - 数据实际不变化
readonly - 数据实际不变化
RR级别的事务隔离（MVCC-RR 或 mysqldump –single-transaction） - 数据实际会变化

2. 表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

语法：lock tables … read/write

与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则

	t1	t2	其他表
线程A	可读	可读写	阻塞
其他线程	可读	阻塞	可读写

其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

由于表锁影响面太大，一般不使用lock tables来控制并发

MDL锁（metadata lock）

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

即MDL锁是为了防止DML语句和DDL语句并发时的冲突。

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

虽然MDL是系统自动加的，但是了解其机制有助于避免一些MySQL使用过程中的风险，例如：给一个小表加个字段，导致整个数据库挂掉

在这里插入图片描述
我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。

之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

在上面这个例子中：

D查询等待时间 = C执行时间 + C提交后 A或B事务提交前剩余的执行时间（取其中大者，因为C需要等待A和B都提交事务释放MDL读锁后才能拿到写锁开始执行）

如果A 或 B是大事务，哪怕C执行很短，D被阻塞的时间也很长，如果高并发场景下，有很多D被阻塞，连接数很快将被打满，此时就只有KILL掉DDL语句或者KILL掉A或B这种大事务。如果是热点表，那么kill掉事务可能只有（在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。）

Online DDL的过程:

拿MDL写锁
降级成MDL读锁
真正做DDL
升级成MDL写锁
释放MDL锁

1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”

解决方案：

在 alter table 语句里面设定等待时间

如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

MariaDB，AliSQL两个开源分支支持 DDL NO WAIT/WAIT n 语法。
```
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
```

3. 行锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

行锁实际上是最终是加在主键索引上的，即数据上，只有主键索引包含数据

死锁和死锁检测

如果这个影院做活动，可以低价预售一年内所有的电影票，而且这个活动只做一天。于是在活动时间开始的时候，你的 MySQL 就挂了。你登上服务器一看，CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。这是什么原因呢？

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

在这里插入图片描述

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

避免死锁检测导致大量超时的解决方案

主要方向：控制访问相同资源的并发事务量

避免死锁
1. 减少锁冲突，将热点行数据拆分成多条，这种方式对业务有入侵，需要业务兼容异常情况
2. 减少死锁的几率，以相同的顺序更新表记录。
3. 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放
控制并发的数量
1. 减少形成死锁后检测工作的开销，如果100并发，检测复杂度就是 100 * 100 = 1万，如果是1000并发，复杂度就是1000 * 1000 = 100万

发生死锁时，show engine innodb status 里面有信息，但不是很全

隐式锁定：不同的事务隔离级别自动加锁释放锁

显示锁定：SELECT … LOCK IN SHARE MODE
SELECT … FOR UPDATE
LOCK TABLES、 UNLOCK TABLES（尽量禁止使用 LOCK TABLES）

锁类型
共享锁（S Lock）

允许事务读一行数据

排他锁（X Lock）

允许事务删除或者更新一行数据

意向共享锁（IS Lock）

事务想要获得一张表中某几行的共享锁

意向排他锁

事务想要获得一张表中某几行的排他锁

锁算法

Record Lock
锁定一个记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Lock
锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Lock
它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

锁问题

脏读
脏读指的是不同事务下，当前事务可以读取到另外事务未提交的数据。

例如：

T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

死锁如何检测

show engine innodb status 里面有信息，不过不是很全…

begin;
select * from t where d=5 for update;
commit;

语句执行完成后，是只有行锁的。而且语句执行完成后，InnoDB 就会把不满足条件的行行锁去掉。

当然了，c=5 这一行的行锁，还是会等到 commit 的时候才释放的

系列文章

上一篇：【MySQL优化(八)】InnoDB查询优化理论与实践(SQL优化)
下一篇：【MySQL优化(十)】MVCC详解