记一次死锁问题的排查

最近遇到了一个死锁问题, 记录一下排查的过程.

SQL Server中的死锁是DBA们经常会遇到的问题. 

常有人混淆了死锁和阻塞, 其实他们的区别还是很明显的.
 阻塞的一般原因可能是进程在等待事务提交,或者某个操作的完成, 是资源上的冲突, 只要等待足够长的时间,是一定可以解决的;  但是死锁是逻辑上的冲突, 也就是说无论等待多长的时间, 更换多么强大的硬件, 都是无法解决死锁的.
在SQL 2005以后的版本中, 用profiler去跟踪死锁的方式, 是最方便的. 使用 deadlock graph事件进行跟踪, 可以生成非常直观的图示, 将死锁发生的原因直接呈现出来.

以下是最近碰到的一次死锁, 可以看到有两个进程,78和74,在页496081和页5102680上发生了死锁.


接下来看一下进程74当时在执行什么语句


可以看出, 进程74当时执行了一条update语句

update atm_status set status='C', date='20120220', time='122016' where atm_no='97793914'

所以这个死锁问题就可以这样解释: 进程74在更新表时,在页496081上获得了排他锁,并且请求页5102680的更新锁, 而进程78却获得了页5102680的排他锁,而请求页496081的更新锁, 这样两个进程互相等待对方所拥有的资源, 于是就发生了死锁.

但是接下来在跟开发人员讨论的中, 遇到了一个问题.  表atm_status明明中只有一条记录被这条update语句更新了, 并且通过dbcc page看到页496081上确实存储着要更新的那条记录,  那为什么进程74要获得另一个页上的更新锁呢?

仔细琢磨了一会儿, 查看了一下表的结构, 找到了原因. 

表atm_status上是没有任何索引的, 所以在做update时, 是用表扫描的方式, 逐页的得到要更新的记录. 在update的过程中,进程会对表中的每个页都请求更新锁, 如果得到了更新锁并且要更新的数据在此页内,就把更新锁变为排他锁,进行更新. 这样处理完一页后,再依次处理表中的下一页. 由于一条语句就是一个事务, 所以之前在其他页上获得的排他锁会一直保持着到语句执行结束.  这也正是进程74虽然只更新一条记录, 却在持有排他锁的同时, 还要在另一个页上请求更新锁的原因.

分析出了原因, 解决这个死锁的问题就比较容易了. 只要在列atm_no上加上合适的索引, 用索引搜索替代表扫描的方式去更新数据, 那么进程就只在需要更新的那一个页上申请更新锁就行了, 不会再发生这样的死锁.

由此可见, 因为缺少合适的索引而导致的低效率查询, 是产生死锁的可能原因之一


摘自 lucky7_2000的专栏