MySQL系列（三）——锁机制

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

我的新书《LangChain编程从入门到实践》 已经开售！推荐正在学习AI应用开发的朋友购买阅读！

全局锁

1. 全局锁：对整个数据库实例加锁
2. 加全局读锁：FLUSH TABLES WITH READ LOCK，阻塞其他线程的下列语句
   • 数据更新语句（增删改）
   • 数据定义语句（建表、修改表结构）
   • 更新类事务的提交语句
3. 主动解锁：UNLOCK TABLES
4. 典型使用场景：全库逻辑备份
   • 把整库每个表都SELECT出来，然后存成文本
5. 缺点
   • 如果在主库上执行逻辑备份，备份期间不能执行更新操作，导致业务停摆
   • 如果在备库上执行逻辑备份，备份期间从库不能执行由主库同步过来的binlog，导致主从延时
6. 备份加全局锁的必要性
   • 保证全局视图是逻辑一致的

mysqldump

1. --single-transaction
   • 导数据之前启动一个事务，确保拿到一致性视图
   • 由于MVCC的支持，在这个过程中是可以正常更新数据的
2. 需要存储引擎支持RR的事务隔离级别
   • MyISAM不支持事务，如果备份过程中有更新，总是能取到最新的数据，破坏了备份的一致性
   • 因此MyISAM只能依赖于FLUSH TABLES WITH READ LOCK，不能使用--single-transaction
3. 针对全库逻辑备份的场景，--single-transaction只适用于所有的表都使用了事务引擎的库
   • 如果有的表使用了不支持事务的存储引擎，那么只能依赖于FLUSH TABLES WITH READ LOCK
   • 这是MyISAM被InnoDB替代的一个重要原因
4. 在逻辑备份时，如果全部库都使用InnoDB，建议使用--single-transaction参数，对应用更加友好

逻辑备份 + DDL

在备库用--single-transaction做逻辑备份的过程中，由主库的binlog传来了一个针对小表t1的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 */

1. 备份开始时，为了确保RR的隔离级别，再设置一次（Q1）
2. 启动事务，用WITH CONSISTENT SNAPSHOT确保可以得到一个一致性视图（Q2）
3. 设置一个保存点（Q3）
4. SHOW CREATE TABLE是为了拿到表结构（Q4）
5. SELECT * FROM是正式导数据（Q5）
6. ROLLBACK TO SAVEPOINT的作用是释放t1的MDL锁（Q6）

DDL到达时刻

1. 时刻1：备份拿到的是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前的表结构
   • 现象为无影响

readonly

1. SET GLOBAL READONLY=true也能让全库进入只读状态，推荐使用FLUSH TABLES WITH READ LOCK
2. 在有些系统中，readonly的值会被用来做其他逻辑，因此修改global变量的方式影响面会比较大
3. 异常处理机制不同
   • 执行FLUSH TABLES WITH READ LOCK命令后，客户端发生异常，MySQL会自动释放全局锁
   • 执行SET GLOBAL READONLY=true命令后，客户端发生异常，MySQL会一直保持readonly状态

表级锁

表锁

1. 表锁：LOCK TABLES ... READ/WRITE
2. 解锁
   • 主动解锁：UNLOCK TABLES
   • 自动解锁：客户端发生异常，断开连接
3. LOCK TABLES除了会限制其他线程的读写外，也会限制本线程接下来的操作
   • 线程A执行LOCK TABLES t1 READ, t2 WRITE，在线程A执行UNLOCK TABLES之前
   • 其他线程允许的操作：读t1
   • 线程A允许的操作：读t1，读写t2，同样不允许写t1
4. InnoDB支持行锁，所以一般不使用LOCK TABLES来进行并发控制

元数据锁（MDL）

1. MDL是隐式使用的，在访问一个表的时候会被自动加上
2. MDL的作用：保证读写的正确性，从MySQL 5.5引入
   • 防止DDL与DML的并发冲突
3. MDL读锁 + MDL写锁
   • 对一个表做增删改查操作（DML）的时候，加MDL读锁
   • 对表结构做变更操作（DDL）的时候，加MDL写锁
   • 关系
     • 读锁之间不互斥：多线程可以并发对同一张表进行增删改查
     • 读写锁之间，写锁之间互斥：用于保证变更表结构操作的安全性

加字段的问题

1. session A先启动，对表t加上一个MDL读锁
2. session B需要的也是MDL读锁，不互斥，可以正常执行
3. session C需要的是MDL写锁，session A的事务还未提交，持有的MDL读锁还未释放，session C会被阻塞
4. session D只需要申请MDL读锁，但同样会被session C阻塞
   • 所有对表的增删改查都需要先申请MDL读锁，此时表现为完全不可读写
   • 如果该表上的查询比较频繁，而且客户端恰好有重试机制（超时后再起一个session去请求），那么数据库的线程很快就会被占满
5. 事务中的MDL锁，在语句开始执行时申请，但会等到整个事务提交后再释放

解决办法

1. 首先要解决长事务的影响，因为只要事务不提交，就会一直占用相关的MDL锁
   • INFORMATION_SCHEMA.INNODB_TRX中的trx_started字段
   • 在做DDL变更之前，首先确认是否长事务在执行，如果有则先kill掉这个长事务
2. 如果需要执行DDL的表是热点表，请求很频繁，kill长事务未必管用，因为很快就会有新的请求
   • ALTER TALE语句设定等待时间，就算拿不到MDL写锁也不至于长时间阻塞后面的业务语句
   • 目前MariaDB和AliSQL支持该功能

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

行锁

1. MySQL的行锁是在存储引擎层实现的
2. MyISAM不支持行锁，而InnoDB支持行锁，这是InnoDB替代MyISAM的一个重要原因

两阶段锁

id为表t的主键，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行

1. 两阶段锁
   • 在InnoDB事务中，行锁是在需要的时候加上
   • 但并不是在不需要了就立刻释放，而是要等待事务结束后才释放
2. 如果事务需要锁定多行，要就把最可能造成锁冲突和影响并发度的锁尽可能往后放

电影票业务

1. 顾客A在电影院B购买电影票，涉及以下操作（同一事务）
   • update：从顾客A的账户余额中扣除电影票价
   • update：给电影院B的账户余额增加电影票价
   • insert：记录一条交易日志
2. 假设此时顾客C也要在电影院B买票的，两个事务冲突的部分就是第2个语句（同一个电影院账户）
   • 所有操作所需要的行锁都是在事务结束的时候才会释放
   • 将第2个语句放在最后，能最大程度地减少事务之间的锁等待，提升并发度

死锁

假设电影院做活动，在活动开始的时候，CPU消耗接近100%，但整个库每秒执行不到100个事务

1. 事务A在等待事务B释放id=2的行锁，事务B在等待事务A释放id=1的行锁，导致死锁
2. 当出现死锁后，有2种处理策略
   • 等待，直至超时（不推荐），业务有损：业务会出现大量超时
   • 死锁检测（推荐），业务无损：业务设计不会将死锁当成严重错误，当出现死锁时可采取：事务回滚+业务重试

等待

1. 由参数innodb_lock_wait_timeout控制（MySQL 5.7.15引入）
2. 默认是50s，对于在线服务来说是无法接受的
3. 但也不能设置成很小的值，因为如果实际上并不是死锁，只是简单的锁等待，会出现很多误伤

死锁检测（推荐）

1. 发现死锁后，主动回滚锁链条中的某一事务，让其他事务继续执行
   • 需要设置参数innodb_deadlock_detect
2. 触发死锁检测：要加锁访问的行上有锁
   • 一致性读不会加锁
3. 死锁检测并不需要扫描所有事务
   • 某个时刻，事务等待状态为：事务B等待事务A，事务D等待事务C
   • 新来事务E，事务E需要等待D，那么只会判断事务CDE是否会形成死锁
4. CPU消耗高
   • 每个新来的线程发现自己要加锁访问的行上有锁
     • 会去判断会不会由于自己的加入而导致死锁，总体时间复杂度为O(N^2)
   • 假设有1000个并发线程，最坏情况下死锁检测的操作量级为100W（1000^2）
5. 解决方法
   • 如果业务能确保一定不会出现死锁，可以临时关闭死锁检测，但存在一定的风险（超时）
   • 控制并发度，如果并发下降，那么死锁检测的成本就会降低，这需要在数据库服务端实现
     • 如果有中间件，可以在中间件实现
     • 如果能修改MySQL源码，可以在MySQL内部实现
   • 设计上的优化
     • 将一行改成逻辑上的多行来减少锁冲突

mysql> SHOW VARIABLES LIKE '%innodb_deadlock_detect%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+

mysql> SHOW VARIABLES LIKE '%innodb_lock_wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 30    |
+--------------------------+-------+

实际问题

1. 如果要删除一个表里面的前10000行数据，有以下三种方法可以做到：
   第一种，直接执行delete from T limit 10000;
   第二种，在一个连接中循环执行20次 delete from T limit 500;
   第三种，在20个连接中同时执行delete from T limit 500。

2. 第二种方式是相对较好的。
   第一种方式（即：直接执行delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
   第三种方式（即：在20个连接中同时执行delete from T limit 500），会人为造成锁冲突。