MySQL的行锁是在引擎层由各个引擎自己实现的。不是所有的引擎都支持行锁,MyISAM就不支持。不支持行锁意味着并发控制只能用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。 InnoDB是支持行锁的,这也是MyISAM被InnoDB替代的重要原因之一。
行锁就是针对数据表中行记录的锁。 事务A更新了一行,而这时候事务B也要更新同一行,则必须等事务A的操作完成后才能更新。
下面操作序列,事务B的update执行时是什么现象。假设id是表t的主键。
取决于事务A在执行完两条update语句后,持有哪些锁,以及在什么时释放。 实际上事务B的update语句会被阻塞,直到事务A执行commit后,事务B才能执行。
你一定知道了事务A持有的两个记录的行锁,都是在commit时才释放。 在InnoDB事务中,行锁是在需要时才加上的,但不是不需要了就立刻释放,而要等事务结束时才释放。
这个就是两阶段锁协议。
如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
顾客A要在影院B购买电影票,业务涉及操作:
要完成交易,需要update两条记录,insert一条记录。 为了保证交易原子性,要把三操作放在一个事务。 怎样安排这三个语句在事务中的顺序?
若同时顾客C要在影院B买票,这两事务冲突部分就是语句2。因为它们要更新同一个影院账户的余额,要修改同行数据。
两阶段锁协议下不论怎样安排语句顺序,所有操作需要的行锁都在事务提交时才释放。 所以,如果把2安排在最后,比如3、1、2,那么影院账户余额这行锁时间就最少。最大程度减少了事务之间的锁等待,提升并发度。 由于你的正确设计,影院余额这一行的行锁在一个事务中不会停留很长时间。但是,这并没有完全解决困扰。
影院做活动,低价预售一年内所有电影票,活动只做一天。于是活动开始时,你的MySQL就挂了。 登上服务器一看,CPU消耗近100%,但整个数据库每秒执行不到100个事务。 什么原因?
当并发系统不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源,就会导致这几线程都无限等待,死锁。
事务A在等待事务B释放id=2的行锁 事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源释放,进入死锁。
InnoDB中,innodb_lock_wait_timeout的默认值50s,如果采用策略1,当死锁后,第一个被锁住的线程要50s才超时退出,然后其他线程才可能继续。对在线服务,这等待时间无法接受。 但又不可能直接把这时间设成一个很小值。这样当死锁时,确实很快解开,但若不是死锁,而是简单的锁等待?所以,超时时间设太短,会误伤。
所以正常情况采用策略2,主动死锁检测,而且innodb_deadlock_detect的默认值就是on。 主动死锁检测在发生死锁的时候,能够快速发现并处理,但有额外负担。 每当一事务被锁,就看看它所依赖线程有无被别人锁住,如此循环,最后判断是否出现循环等待,也就是死锁。
怎么解决这种热点行更新导致的性能问题? 症结在于,死锁检测耗费大量CPU资源。
一种头痛医头的方法,就是如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。但是这种操作本身带有一定的风险,因为业务设计的时候一般不会把死锁当做一个严重错误
另一思路:控制并发度。 如果并发能够控制住,比如同一行同时最多10个线程更新,那么死锁检测成本低了,就不会出现这问题。 一个直接的想法,在客户端做并发控制。但很快发现这不太可行,因为客户端很多的!!! 因此并发控制要做在数据库服务端。如果有中间件,可考虑在中间件实现 如果团队有能修改MySQL源码的人,也可以做在MySQL。 基本思路:对于同行更新,在进入引擎之前排队。这样在InnoDB内部就不会有大量死锁检测工作。
如果事务中需要锁多行,把最可能造成锁冲突、影响并发度的锁的申请时机尽量往后放。
但调整语句顺序并不能完全避免死锁。所以引入死锁和死锁检测及三个方案,来减少死锁对数据库影响。 减少死锁的主要方向就是控制访问相同资源的并发事务量。
《MySQL 实战 45 讲》