SQL Server在select/update或多个选择之间发生死锁

所有关于SQL Server死锁的文档都讨论了操作1锁定资源A然后尝试访问资源B并且操作2锁定资源B并尝试访问资源A的情况.

但是,我常常在一些繁忙的应用程序中看到select和更新之间甚至多个选择之间的死锁.我发现死锁跟踪输出的一些细节非常难以理解但我真的只想了解什么可能导致两个单独操作之间的死锁.当然,如果一个选择具有读锁定,则更新应该在获得独占锁之前等待,反之亦然？

这种情况发生在SQL Server 2005上,而不是我认为这会产生影响.

这可能是因为select对两个不同的索引进行了锁定,同时更新以相反的顺序锁定相同的索引.select需要两个索引,因为第一个索引不包含它需要访问的所有列; 更新需要两个索引,因为如果更新索引的键列,则需要对其进行锁定.

http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx有一个很棒的解释.建议的修复包括添加一个索引,该索引覆盖select所需的所有列,切换到快照隔离,或显式强制select获取通常不需要的更新锁.

我曾经在SQL-Server-Performance.com上为一篇关于高级SQL Server锁定的好文章添加了书签.那篇文章超出了你提到的经典僵局情况,可能会让你对你的问题有所了解.

我很惊讶没人提到WITH (UPDLOCK)锁定提示.如果您有死锁涉及例如并行运行的两个select-insert对,那么它非常有用.

在SQL Server中,如果发出选择WITH (UPDLOCK),则第二个选择将等到第一个选择完成.否则他们会获得共享锁,当他们同时尝试升级到独占锁时,他们会死锁.

单个查询之间的锁定可能会发生,因为它们锁定单个行,而不是整个表:

更新查询在表中的几行上获取更新锁定,并且select查询在表中的某些其他行上获取读锁定.然后,更新查询尝试对读取锁定的行获取更新锁定,并且select查询尝试对更新锁定的行获取读取锁定.

升级锁可能会变得更加复杂,即数据库决定事务锁定的单行太多,因此应该将其升级为锁定表的一部分或整个表.这意味着锁可能会影响不直接参与查询的行.

我的猜测是，选择语句需要一个读锁，当您附带更新语句时，则需要将其升级为写锁。

升级到写锁要求删除所有其他读锁（它们的选择事务完成）。但是，如果另一个进程已经有了升级到写入锁的绝妙主意，那么您突然会有两个进程互相等待以释放读取锁，因此它们可以获取写入锁。

如果使用select-for-update（UPDLOCK），则它将从头开始获取写锁，然后就不会出现死锁问题。