MySQL系列(三)——锁机制

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

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

全局锁

  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语句

备份关键语句
1
2
3
4
5
6
7
8
9
10
11
12
13
# 备份过程中的关键语句
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写锁也不至于长时间阻塞后面的业务语句
    • 目前MariaDBAliSQL支持该功能
1
2
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内部实现
    • 设计上的优化
      • 将一行改成逻辑上的多行来减少锁冲突
1
2
3
4
5
6
7
8
9
10
11
12
13
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),会人为造成锁冲突。

MySQL系列(三)——锁机制

https://liduos.com/mysql-lock.html

作者

莫尔索

发布于

2020-08-27

更新于

2024-05-24

许可协议

评论