首页 > mysql > MySQL行锁的最佳实践
2022
01-18

MySQL行锁的最佳实践

MySQL的行锁是在引擎层由各个引擎实现。MyISAM不支持行锁,意味着并发控制只能用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。
InnoDB支持行锁的,这也是MyISAM被InnoDB替代的重要原因之一。

行锁就是针对数据表中行记录的锁。事务A更新了一行,而这时候事务B也要更新同一行,则必须等事务A的操作完成后才能更新。

两阶段锁
id是表t的主键。

事务 1    事务 2
begin
update t set k=k+1 where id=1;
update t set k=k+1 where id=2;    
begin
update t set k=k+2 where id=1;
commit    
事务B的update语句会被阻塞,直到事务A执行commit后,事务B才能执行。

事务A持有的两个记录的行锁,都是在commit时才释放。
InnoDB事务中,行锁在需要时才加,但不是不需要了就立刻释放,而要等事务结束时才释放。

这就是两阶段锁协议。

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

案例
顾客A要在影院B购买电影票,业务涉及操作:

从顾客A账户余额中扣除电影票价
给影院B的账户余额增加这张电影票价
记录一条交易日志
要完成交易,需要update两条记录,insert一条记录。
为保证交易的原子性,要把三操作放在一个事务。

如何安排三语句在事务中的顺序?
若同时顾客C要在影院B买票,这两事务冲突部分就是语句2。因为它们要更新同一个影院账户的余额,要修改同一行数据。

两阶段锁协议下不论怎样安排语句顺序,所有操作需要的行锁都在事务提交时才释放。
所以,若把2安排在最后,比如3、1、2,则影院账户余额这行锁的时间就最少。最大程度减少了事务之间的锁等待,提升并发度。
由于你的正确设计,影院余额这一行的行锁在一个事务中不会停留很长时间。但这并未完全解决问题。

影院做活动,低价预售一年内所有电影票,活动只做一天。于是活动开始时,你的MySQL就挂了。
登上服务器一看,CPU消耗近100%,但整个数据库每秒执行不到100个事务。
什么原因?

死锁和死锁检测
当并发系统不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源,就会导致这几线程都无限等待=》死锁。

事务 1    事务 2
begin
update t set k=k+1 where id=1;    begin;
update t set k=k+1 where id=2;
update t set k=k+1 where id=2;    
update t set k=k+1 where id=1;
事务1在等待事务2释放id=2的行锁
事务2在等待事务1释放id=1的行锁
事务1和事务2在互相等待对方的资源释放,导致死锁
死锁后的策略
超时等待
该超时时间可通过参数innodb_lock_wait_timeout设置。

InnoDB中,innodb_lock_wait_timeout默认值50s,若采用策略1,当死锁后,第一个被锁住的线程要50s才超时退出,然后其他线程才可能继续。对在线服务,这等待时间无法接受!
也不可能直接把这时间设成一个小值。这样当死锁时,确实很快解开,但若不是因为死锁,而只是正常的锁等待呢?所以,超时时间设太短,会误伤友军。

主动死锁检测
发现死锁后,主动回滚死锁链中的某一事务,让其他事务继续执行。
将参数innodb_deadlock_detect设置为on,开启该逻辑。

正常情况采用该策略,而且innodb_deadlock_detect默认值就是on。
主动死锁检测在发生死锁时,能够快速发现并处理,但有额外负担:
每当一个事务被锁,就看看它所依赖的线程是否被别人锁住。如此循环,最后判断是否出现循环等待,即死锁。

若是说到的所有事务都更新同一行数据?
每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,时间复杂度O(n)。若有1000个并发线程要同时更新同一行,则死锁检测操作就是100万量级。虽然最终检测结果是没有死锁,但这期间要消耗大量CPU。因此,你就会看到CPU占用率很高,但是每秒却执行不了几个事务。
优化热点行更新
死锁检测关掉
问题在于,死锁检测耗费大量CPU资源。若你能确保该业务一定不会出现死锁,可以临时把死锁检测关掉。但这操作有一定风险,因为业务设计时一般不会把死锁当做一个严重错误:

毕竟出现死锁,就回滚,然后通过业务重试一般就没问题,业务无损
而关掉死锁检测意味着可能会出现大量超时,业务有损
控制并发度
如果并发能够控制住,比如同一行同时最多10个线程更新,那么死锁检测成本低了,就不会出现这问题。
一个直接的想法,在客户端做并发控制。但很快发现这不太可行,因为客户端很多的!!!
因此并发控制要做在数据库服务端。若有中间件,可考虑在中间件实现。若团队有能修改MySQL源码的人,也可做在MySQL。

基本思路
对于同行更新,在进入引擎之前排队。这样在InnoDB内部就不会有大量死锁检测工作。

若团队没有DB专家,不能实现这样方案,能否做设计优化?

分段锁
可以考虑将一行改成逻辑上的多行,以减少锁冲突。

以影院账户为例,可考虑放在多条记录,比如10个记录,影院的账户总额等于这10个记录值总和。这样每次给影院账户加金额时,随机选其中一条记录加。这样每次冲突概率变成原来1/10,减少锁等待个数,也就减少了死锁检测的CPU消耗。

这方案看上去无损,但这类方案需根据业务逻辑做详细设计。若账户余额可能减少,比如退票操作,则此时就需要考虑当一部分行记录变成0时,代码要有特殊处理。

调整语句顺序并不能完全避免死锁,以上方案都只是减少死锁对数据库影响。减少死锁的主要方向也就是控制访问相同资源的并发事务量。

作者:mysql面试宝典
mysql面试宝典

本文》有 0 条评论

留下一个回复