实例分析数据库死锁的产生

近日由于系统操作过程中会提示“事务(进程 ID 54)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。”

以前也出现过，但是无从下手，不知道该从哪里下手。朱总提示应该以出错这条语句访问到的表为中心查找所有跟此表有关的sql语句，看有没有可能造成死锁。其实听到这个提示，我脑子里也是懵的。后来一看被牺牲的这条sql语句有三个表，而且是使用最频繁的三个表，如果按朱总的想法那工作量有多大啊？再加上我对数据库死锁的一些细节还有点不清楚，这不是大海捞针嘛？

第二天我想了个办法，首先重现这种死锁，然后通过数据库找到造成死锁的前后sql，这样范围就小多了（说不准还能悟透死锁的细节，当然这个是事前没有想到的）。

说做就做，第一步就是重现死锁，正好很久以前为测试这个系统的并发写了一个机器人，能模仿多个客户端做常规操作。但是有些日子没更新了，修改了大概2个小时，终于跑起来了，还不错，能用。18个机器人做常规操作，我自己在客户端做非常规操作，客户端一停顿下来，马上运行exec sp_who_lock。

经过手动整理如下：

54被86阻塞update SALE_Object set ObjStateID=@ObjStateID

71、76、86、101被54阻塞select * from SALE_ObjState, SALE_Object, SALE_SubSvc

60、66被71阻塞update SALE_Object set ObjStateID=@ObjStateID

 

通过了解“独占锁X” “共享锁S”的含意总结如下：“”

-- select在ReadCommitted事务中加共享锁，在RepeatableRead事务中加排它锁；

-- INSERT、UPDATE、DELETE过程中始终应用排它锁；

-- update必须等待select事务释放共享锁转为排它锁后才能执行；

-- 若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁

 

并查询代码中调用上面相关sql语句之前的语句，结论如下：
-- 86号进程ReadCommitted
86 insert SALE_ObjState 得到 SALE_ObjState 独占锁
86 select SALE_ObjState 保持独占锁
86 update 需要 SALE_Object 独占锁

-- 54号进程RepeatableRead
54 select * from SALE_SubSvc, SALE_Object, SALE_ObjState
   依次得到SALE_SubSvc, SALE_Object的独占锁，需要SALE_ObjState独占锁但是被86占了，得不到（这里根据sql中的from语句依次得到表的锁是我自己悟到的，因为作为程序不可能给三个表同时加锁，必然是一个一个加的锁，so……）

-- 71号进程与86号进程的过程是一样的（此处省略）

 

由于54号进程中使用的是级别比较高级的事务，这也是造成死锁最关键的一点。

解决办法：

1.如果程序中54进程的查询没有必要使用RepeatableRead事务，那么就将其修改为ReadCommitted事务。

2.如果程序中必须使用RepeatableRead事务，那么就必须通过调换sql语句select * from SALE_SubSvc, SALE_Object, SALE_ObjState中SALE_Object和SALE_ObjState的顺序来达到目的。但是这个调换也是有风险的，因为你不清楚是否还有其它位置和此处产生死锁。这个解决方案比较复杂，不好从理论上进行细致地讨论。

此文的目的是让读者理解数据库死锁产生的一些细节，如果有什么描述得不清楚的地方请指出，我一定答复。

由于本人的水平是菜鸟级的，文中有理解得不对的地方欢迎指正！