记最近遇到的一次数据库死锁问题

问题描述
度假后台在更新完DB的数据后会通知dumper进行一次全量dump，但不时会遇到dumper没有收到通知的情况。通过查看度假后台的日志，发现在发送全量dump通知的时候抛出了"dead lock detached"的错误，由于后台代码此处并没有采用多线程，进而怀疑是DB死锁，于是请求老何支援。查看DB的错误日志，发现是后台系统和dumper的sql产生了死锁。

死锁原因
产生死锁的两项DB操作分别如下：
1. 度假后台：发送全量dump通知前的数据更新会对名为route的表进行循环update操作，且整个循环被保持在一个事务当中，整个事务成功后才会发送全量dump通知，否则rollback。

2. dumper：dumper除了监听全量dump通知之外，还会对route表中update_flag字段为true的记录进行增量dump，dump完成后会将update_flag重新置为false，使用的sql如下。

update route set update_flag = false where update_flag = true and id in (...);

其中，括号内的id为无序，这也是产生死锁的关键。
假设度假后台代码中是按ID升序更新，更新序列为"1,2,3,4,5,6..."，而dumper某次增量dump需要更新ID为3,5的记录，且更新序列为所"5,3"，那么将可能出现下面的情况。
度假后台依次更新ID为1,2,3,4的记录，并且获得了这四条记录的行锁，此时需要获取ID为5的记录的行锁；而不巧dumper的update操作正好刚更新完ID为5的记录，正需要获取ID为3的记录的行锁...
于是死锁产生了

解决办法
解决办法很简单，只需要给dumper的"where id in (...)" sql中的ID排个序，且保证其顺序与度假后台事务中的更新顺序一致。这样，上述的死锁情况就会变为：
度假后台需要顺序获得ID为"1,2,3,4,5,6,..."的行锁，dumper则需要顺序获得ID为"3,5"的行锁，无论两项操作谁先开始，都最多只可能有一项操作处于等待锁的状态。

另一种解决方法是，将dumper中的"where id in (...)"操作修改为每个ID执行一条SQL，且该批量更新操作无需保证其事务性。在保证使用同一数据库连接的前提下，拆分开的多个update操作应该不会比之前的"update ...按 where id in (...)"操作慢多少。小插曲：在比较这两种操作的性能时，老何提到说"集中型的操作会造成资源占用的尖峰，如果这个尖峰引起了系统资源的紧张，那么执行的效率或许还不如把操作切分为多个小份，那样每一份操作会很快地执行完"。

后续思考
在了解了死锁产生的原因之后，我对数据库获取和释放行锁的顺序有了一丝疑问：

1.为什么产生死锁的两项操作不会在一开始就把需要的行锁全部拿到，从而杜绝和其他操作产生死锁的可能；

2.为什么释放锁的时候不是用完一个释放一个，而是要等所有操作都进行完了才一起释放？

于是上stackoverflow发了个问(http://stackoverflow.com/questions/11454638/how-do-the-db-lock-rows-and-release-them).
答案里提到，获取和释放锁的顺序和DB的数据库隔离级别有关。在默认的隔离级别Read Committed下，锁的获取和释放就像问题中所描述的那样，获取的时候会依次获取，而释放则统一在操作完成后释放。如果是使用最为严格的Serializable隔离级别，那么情况就会变成和我第一个疑问中描述的那样：在操作的一开始便把需要的行锁全部拿到，直到操作完成才全部释放，但是这样自然会造成性能的下降。

至于我第二个疑问所描述的情况，则是无法保证事务性的，如果操作还未完成便释放部分行锁的话，其他操作可能会这一部分记录做修改，从而破坏了整个事务。

评论

1 楼 bneliao 2013-01-29  

不错，最近我也遇到相类似的问题，看了你的资料后，更加清楚这类问题的原因了