代码之家 › 专栏 › 技术社区 › Ian Boyd

SQL Server死锁修复:强制联接顺序,还是自动重试?

database-deadlocks deadlock sql-server-2000 error-handling sql-server

Ian Boyd · 技术社区 · 15 年前

我有一个存储过程,它执行 TableB 到 TableA :

 SELECT <--- Nested <--- TableA
             Loop   <--
                      |
                      ---TableB

同时,在事务中,行被插入到 表 ,然后进入 塔布莱 。

这种情况有时会导致死锁,因为存储过程select从 塔布莱 ,而插入将行添加到表 ,然后每个人都希望对方放开另一张桌子:

INSERT     SELECT
=========  ========
Lock A     Lock B
Insert A   Select B
Want B     Want A
....deadlock...

逻辑要求 INSERT 首先将行添加到一 ,然后乙 ,而我个人并不关心sql server执行连接的顺序—只要它连接。

解决死锁的常见建议是确保每个人以相同的顺序访问资源。但在这种情况下,sql server的优化器告诉我相反的顺序是“更好的”。我可以强制执行另一个连接顺序,但查询的性能较差。

但我应该吗?

我应该用我希望它使用的连接顺序重写优化器吗?

或者我应该抓住错误 本机错误1205 ,然后重新提交select语句?

问题不在于当我重写优化器并让它做一些非优化的事情时,查询的性能会差多少。问题是:自动重试比运行更糟糕的查询更好吗?

3 回复 | 直到 15 年前

Remus Rusanu 15 年前

最好是自动重试死锁。原因是你可以解决这死锁,后来又撞了一个。如果表的大小改变了,如果服务器硬件规范改变了,即使服务器上的负载改变了,sql版本之间的行为也可能改变。如果死锁很频繁,您应该采取主动措施消除它(索引通常是答案),但是对于罕见的死锁(比如每10分钟左右),应用程序中的重试可以掩盖死锁。您可以重试读取或写操作,因为写操作当然被适当的begin transaction/commit事务包围,以保持所有写操作是原子的,因此能够在没有问题的情况下重试它们。

另一个考虑的途径是 read committed snapshot . 启用此选项后,select将不接受任何锁,但会产生一致的读取。

A-K 15 年前

为了避免死锁,最常见的建议之一是“以相同的顺序获取锁”或“以相同的顺序访问对象”。显然这是完全有道理的,但它总是可行的吗?总有可能吗?当我不能遵循这个建议时,我总是遇到一些情况。

如果我将一个对象存储在一个父表和一个或多个子表中,我就完全不能遵循这个建议。插入时,我需要先插入父行。删除时,我必须按相反的顺序执行。

如果我使用的命令涉及一个表中的多个表或多行,那么通常我无法控制获取锁的顺序(假设我没有使用提示)。

因此,在许多情况下,试图以相同的顺序获取锁并不能防止所有死锁。所以,无论如何,我们需要某种处理死锁的方法——我们不能假设我们能全部消除它们。 当然,除非我们使用service broker或sp_getapplock序列化所有访问。

当我们在死锁后重试时,很可能会覆盖其他进程的更改。我们需要知道,很可能是其他人修改了我们打算修改的数据。特别是如果所有读卡器都在快照隔离下运行,那么读卡器就不能参与死锁,这意味着死锁中涉及的所有方都是编写器、修改或试图修改同一数据。如果我们只捕获异常并自动重试,则可以覆盖其他人的更改。

这称为丢失更新,通常是错误的。通常,在死锁之后正确的做法是在更高的级别上重试-重新选择数据并决定是否以与原始保存决定相同的方式保存。

例如,如果用户按下save按钮,而saving事务被选为死锁牺牲品,那么最好在死锁之后在屏幕上重新显示数据。

sidereal 15 年前

捕获和重新运行可以工作,但是您确定select始终是死锁的牺牲品吗?如果插入是死锁的牺牲品,则必须更加小心地重试。

我认为,在这种情况下,最简单的解决方案是取消锁定或重新未提交(同样的事情)您的选择。人们有理由担心脏读,但多年来,为了获得更高的并发性,我们到处运行nolock,从来没有遇到过问题。

我还将对锁语义做更多的研究。例如,我相信如果将事务隔离级别设置为snapshot(需要2005或更高版本),问题就会消失。