MySQL45讲
基础篇
07 | 行锁功过:怎么减少行锁对性能的影响?
MySQL 的行锁是在引擎层由各个引擎自己实现的(全局锁、表锁是在 server 层实现的)。 并不是所有的引擎都支持行锁,比如 MyISAM,这也是 MyISAM 被 InnoDB 替代的重要原因之一。
两阶段锁协议
在 InnoDB 事务中,行锁是在需要的时候才加上,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
两阶段锁协议主要的思想
对锁的操作应该分为两个阶段,膨胀阶段和收缩阶段——在膨胀阶段,只能获取锁,禁止释放锁。在收缩阶段,只能释放锁,禁止获取锁。
显然,如果行锁用完立即释放,就走完了一个膨胀-收缩。之后再获取锁就破坏了这个规定——不能在收缩阶段获取锁。因此,“在事务结束前不释放锁,让锁在事务结束的时候才释放”就成了达成两阶段锁协议的方式。
根据两阶段锁协议,如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁和死锁检测
示例:
事务 A 在等待事务 B 释放 id=2 的行锁,而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放,就是进入了死锁状态。
当出现死锁以后,有两种策略:
- 直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
- 发起死锁检测。发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。
在 InnoDB 中,innodb_lock_wait_timeout 的默认值是 50s,对于在线服务来说,这个等待时间往往是无法接受的。而超时时间设置太短的话,会出现很多误伤。
正常情况下还是要采用第二种策略,即:主动死锁检测,而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的。
主动死锁检测也是有额外负担。 每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是 O(n) 的操作。这期间要消耗大量的 CPU 资源。因此,会出现 CPU 利用率很高,但是每秒却执行不了几个事务。
怎么解决由热点行更新导致的性能问题?
- 如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。关掉死锁检测意味着可能会出现大量的超时,这是业务有损的。
- 控制并发度。在客户端做并发控制不可行,客户端可能有很多,合计下来并发度并不低。因此,并发控制要做在数据库服务端,可以考虑在中间件实现;也可以修改 MySQL 源码,对于相同行的更新,在进入引擎之前排队,这样在 InnoDB 内部就不会有大量的死锁检测工作了。
- 通过将一行改成逻辑上的多行来减少锁冲突,比如将一行记录拆分成多行。以影院账户为例,可以考虑放在多条记录上,比如 10 个记录,影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候,随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10,可以减少锁等待个数,也就减少了死锁检测的 CPU 消耗。