选择/更新或多次选择之间的SQL Server死锁 [英] SQL Server deadlocks between select/update or multiple selects

问题描述

有关SQL Server死锁的所有文档都讨论了以下情况：操作1锁定资源A，然后尝试访问资源B，操作2锁定资源B，然后尝试访问资源A。

All of the documentation on SQL Server deadlocks talks about the scenario in which operation 1 locks resource A then attempts to access resource B and operation 2 locks resource B and attempts to access resource A.

但是，在我们一些繁忙的应用程序中，我经常看到选择和更新之间，甚至多个选择之间出现死锁。我发现死锁跟踪输出的一些优点很难理解，但是我真的很想了解是什么导致两个单一操作之间的死锁。当然，如果选择具有读取锁，更新应该只在获得排他锁之前等待，反之亦然？

However, I quite often see deadlocks between a select and an update or even between multiple selects in some of our busy applications. I find some of the finer points of the deadlock trace output pretty impenetrable but I would really just like to understand what can cause a deadlock between two single operations. Surely if a select has a read lock the update should just wait before obtaining an exclusive lock and vice versa?

这是在SQL Server 2005上发生的，并不是我认为这使得

This is happening on SQL Server 2005 not that I think this makes a difference.

推荐答案

之所以会发生这种情况，是因为select锁定了两个不同的索引，而更新则锁定了在相同索引上的顺序相反。该选择需要两个索引，因为第一个索引并未覆盖它需要访问的所有列；该更新需要两个索引，因为如果更新索引的键列，则需要对其进行锁定。

This can happen because a select takes a lock out on two different indexes, meanwhile an update takes a lock out on the same indexes in the opposite order. The select needs two indexes because the first index doesn't cover all of the columns it needs to access; the update needs two indexes because if you update an index's key column you need to take a lock on it.

http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx 有一个奇妙的解释。建议的解决方案包括添加一个索引，该索引覆盖选择所需的所有列，切换到快照隔离或显式强制选择获取通常不需要的更新锁。

http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx has a fantastic explanation. Suggested fixes include adding an index that covers all of the columns the select needs, switching to snapshot isolation, or explicitly forcing the select to grab an update lock that it wouldn't normally need.

这篇关于选择/更新或多次选择之间的SQL Server死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！