MySQL -- 锁
全局锁
- 全局锁:对整个数据库实例加锁
- 加全局读锁:
FLUSH TABLES WITH READ LOCK
,阻塞其他线程的下列语句- 数据更新语句(增删改)
- 数据定义语句(建表、修改表结构)
- 更新类事务的提交语句
- 主动解锁:
UNLOCK TABLES
- 典型使用场景:全库逻辑备份
- 把整库每个表都SELECT出来,然后存成文本
- 缺点
- 如果在主库上执行逻辑备份,备份期间不能执行更新操作,导致业务停摆
- 如果在备库上执行逻辑备份,备份期间从库不能执行由主库同步过来的binlog,导致主从延时
- 备份加全局锁的必要性
- 保证全局视图是逻辑一致的
mysqldump
--single-transaction
- 导数据之前启动一个事务,确保拿到_一致性视图_
- 由于MVCC的支持,在这个过程中是可以正常更新数据的
- 需要存储引擎支持_RR的事务隔离级别_
- MyISAM不支持事务,如果备份过程中有更新,总是能取到最新的数据,破坏了备份的一致性
- 因此MyISAM只能依赖于
FLUSH TABLES WITH READ LOCK
,不能使用--single-transaction
- 针对全库逻辑备份的场景,
--single-transaction
只适用于所有的表都使用了事务引擎的库- 如果有的表使用了不支持事务的存储引擎,那么只能依赖于
FLUSH TABLES WITH READ LOCK
- 这是MyISAM被InnoDB替代的一个重要原因
- 如果有的表使用了不支持事务的存储引擎,那么只能依赖于
- 在逻辑备份时,如果全部库都使用InnoDB,建议使用
--single-transaction
参数,对应用更加友好
逻辑备份 + DDL
在备库用--single-transaction
做逻辑备份的过程中,由主库的binlog传来了一个针对小表t1
的DDL语句
备份关键语句
1 | # 备份过程中的关键语句 |
- 备份开始时,为了确保RR的隔离级别,再设置一次(Q1)
- 启动事务,用
WITH CONSISTENT SNAPSHOT
确保可以得到一个一致性视图(Q2) - 设置一个保存点(Q3)
SHOW CREATE TABLE
是为了拿到表结构(Q4)SELECT * FROM
是正式导数据(Q5)ROLLBACK TO SAVEPOINT
的作用是释放t1的MDL锁(Q6)
DDL到达时刻
- 时刻1:备份拿到的是DDL后的表结构
- 现象为无影响
- 时刻2:Q5执行时,报异常:
Table definition has changed, please retry transaction
- 现象为mysqldump终止
- 在时刻2~时刻3之间(导数据期间):mysqldump占据着t1的MDL读锁,因此binlog会被阻塞,直到Q6结束
- 现象为主从延时
- 时刻4:mysqldump释放MDL读锁,备份拿到的是DDL前的表结构
- 现象为无影响
readonly
SET GLOBAL READONLY=true
也能让全库进入只读状态,推荐使用FLUSH TABLES WITH READ LOCK
- 在有些系统中,
readonly
的值会被用来做其他逻辑,因此修改global
变量的方式影响面会比较大 - 异常处理机制不同
- 执行
FLUSH TABLES WITH READ LOCK
命令后,客户端发生异常,MySQL会自动释放全局锁 - 执行
SET GLOBAL READONLY=true
命令后,客户端发生异常,MySQL会一直保持readonly状态
- 执行
表级锁
表锁
- 表锁:
LOCK TABLES ... READ/WRITE
- 解锁
- 主动解锁:
UNLOCK TABLES
- 自动解锁:客户端发生异常,断开连接
- 主动解锁:
LOCK TABLES
除了会限制其他线程的读写外,也会限制本线程接下来的操作- 线程A执行
LOCK TABLES t1 READ, t2 WRITE
,在线程A执行UNLOCK TABLES
之前 - 其他线程允许的操作:读t1
- 线程A允许的操作:读t1,读写t2,同样_不允许写t1_
- 线程A执行
- InnoDB支持行锁,所以一般不使用
LOCK TABLES
来进行并发控制
元数据锁(MDL)
- MDL是隐式使用的,在访问一个表的时候会被自动加上
- MDL的作用:保证读写的正确性,从MySQL 5.5引入
- 防止DDL与DML的并发冲突
- MDL读锁 + MDL写锁
- 对一个表做增删改查操作(DML)的时候,加MDL读锁
- 对表结构做变更操作(DDL)的时候,加MDL写锁
- 关系
- 读锁之间不互斥:多线程可以并发对同一张表进行增删改查
- 读写锁之间,写锁之间互斥:用于保证变更表结构操作的安全性
加字段的问题
- session A先启动,对表t加上一个MDL读锁
- session B需要的也是MDL读锁,不互斥,可以正常执行
- session C需要的是MDL写锁,session A的事务还未提交,持有的MDL读锁还未释放,session C会被阻塞
- session D只需要申请MDL读锁,但同样会被session C阻塞
- 所有对表的增删改查都需要先申请MDL读锁,此时表现为完全不可读写
- 如果该表上的查询比较频繁,而且客户端恰好有重试机制(超时后再起一个session去请求)
- 那么数据库的线程很快就会被占满
- 事务中的MDL锁,在语句开始执行时申请,但会等到整个事务提交后再释放
解决办法
- 首先要解决长事务的影响,因为只要事务不提交,就会一直占用相关的MDL锁
INFORMATION_SCHEMA.INNODB_TRX
中的trx_started
字段- 在做DDL变更之前,首先确认是否长事务在执行,如果有则先kill掉这个长事务
- 如果需要执行DDL的表是热点表,请求很频繁,kill长事务未必管用,因为很快就会有新的请求
ALTER TALE
语句设定等待时间,就算拿不到MDL写锁也不至于长时间阻塞后面的业务语句- 目前
MariaDB
和AliSQL
支持该功能
1 | ALTER TABLE T [WAIT [n]|NO_WAIT] ADD f INT |
关系
- 线程A在MyISAM表上更新一行数据,那么会加MDL读锁和表的写锁
- 线程B在同一个MyISAM表上更新另外一行数据,那么也会加MDL读锁和表的写锁
- 线程B加MDL读锁成功,但加表的写锁失败
- 表现:线程B被线程A阻塞
- 引申:如果有多种锁,必须全部锁不互斥才能并行,只要有一个锁互斥,就得等
行锁
- MySQL的行锁是在存储引擎层实现的
- MyISAM不支持行锁,而InnoDB支持行锁,这是InnoDB替代MyISAM的一个重要原因
两阶段锁
id为表t的主键,事务B的update语句会被阻塞,直到事务A执行commit之后,事务B才能继续执行
- 两阶段锁
- 在InnoDB事务中,行锁是在需要的时候加上
- 但并不是在不需要了就立刻释放,而是要等待事务结束后才释放
- 如果事务需要锁定多行,要就把最可能造成锁冲突和影响并发度的锁尽可能往后放
电影票业务
- 顾客A在电影院B购买电影票,涉及以下操作(同一事务)
- update:从顾客A的账户余额中扣除电影票价
- update:给电影院B的账户余额增加电影票价
- insert:记录一条交易日志
- 假设此时顾客C也要在电影院B买票的,两个事务冲突的部分就是第2个语句(同一个电影院账户)
- 所有操作所需要的行锁都是在事务结束的时候才会释放
- 将第2个语句放在最后,能最大程度地减少事务之间的锁等待,提升并发度
死锁
假设电影院做活动,在活动开始的时候,CPU消耗接近100%,但整个库每秒执行不到100个事务
- 事务A在等待事务B释放id=2的行锁,事务B在等待事务A释放id=1的行锁,导致死锁
- 当出现死锁后,有2种处理策略
- 等待,直至超时(不推荐)
- 业务有损:业务会出现大量超时
- 死锁检测(推荐)
- 业务无损:业务设计不会将死锁当成严重错误,当出现死锁时可采取:_事务回滚+业务重试_
- 等待,直至超时(不推荐)
等待
- 由参数
innodb_lock_wait_timeout
控制(MySQL 5.7.15引入) - 默认是50s,对于在线服务来说是无法接受的
- 但也不能设置成很小的值,因为如果实际上并不是死锁,只是简单的锁等待,会出现很多误伤
死锁检测(推荐)
- 发现死锁后,主动回滚锁链条中的某一事务,让其他事务继续执行
- 需要设置参数
innodb_deadlock_detect
- 需要设置参数
- 触发死锁检测:要加锁访问的行上有锁
- 一致性读不会加锁
- 死锁检测并不需要扫描所有事务
- 某个时刻,事务等待状态为:事务B等待事务A,事务D等待事务C
- 新来事务E,事务E需要等待D,那么只会判断事务CDE是否会形成死锁
- CPU消耗高
- 每个新来的线程发现自己要加锁访问的行上有锁
- 会去判断会不会由于自己的加入而导致死锁,总体时间复杂度为**
O(N^2)
**
- 会去判断会不会由于自己的加入而导致死锁,总体时间复杂度为**
- 假设有1000个并发线程,最坏情况下死锁检测的操作量级为100W(1000^2)
- 每个新来的线程发现自己要加锁访问的行上有锁
- 解决方法
- 如果业务能确保一定不会出现死锁,可以临时关闭死锁检测,但存在一定的风险(超时)
- 控制并发度,如果并发下降,那么死锁检测的成本就会降低,这需要在数据库服务端实现
- 如果有中间件,可以在中间件实现
- 如果能修改MySQL源码,可以在MySQL内部实现
- 设计上的优化
- 将一行改成逻辑上的多行来减少锁冲突
1 | mysql> SHOW VARIABLES LIKE '%innodb_deadlock_detect%'; |
更新无锁引字段
1 | # name字段没有索引 |
InnoDB内部会根据聚簇索引,_逐行扫描,逐行加锁,事务提交后统一释放锁_
参考资料
《MySQL实战45讲》
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.