SQL Server死锁修复：强制加入订单，还是自动重试？ [英] SQL Server Deadlock Fix: Force join order, or automatically retry?

问题描述

我有一个存储过程执行 TableB 到 TableA 的连接：

  SELECT< ---嵌套< --- TableA 
循环<  -  
 | 
 --- TableB 
  

同时，在一个事务中，行被插入 TableA ，然后进入 TableB 。

这种情况偶尔会导致死锁，因为存储过程从 TableB 中选择抓取行，而插入将行添加到 TableA ，然后每个都希望另一个放弃另一个表：

  INSERT SELECT 
 ========= ======== 
锁A锁B 
插入A选择B 
要B要A 
 ....死锁... 
  

逻辑需要 INSERT 首先将行添加到 A ，然后添加到 B ，而我个人并不关心SQL Server执行其加入的顺序 - 只要它加入。

修复死锁的常见建议是确保每个人以相同的顺序访问资源。但在这种情况下，SQL Server的优化器告诉我，相反的顺序是更好。我可以强制执行另一个加入订单，并执行更差的查询。

但是我应该吗？

覆盖优化器，现在和永远，使用我想要使用的连接顺序？

或者我应该只是陷阱错误本机错误1205 ，然后重新提交select语句？

当我重写优化器并执行某些非最优化时，查询可能会更糟的是问题。问题是：最好自动重试，而不是运行更差的查询？

解决方案

最好自动重试死锁。原因是你可能会修复这个死锁，只能稍后再打一个。如果表的大小更改，服务器硬件规格的变化以及服务器上的负载是否发生变化，则SQL版本之间的行为可能会发生变化。如果死锁频繁，您应该采取积极措施消除它（索引通常是答案），但是对于罕见的死锁（例如每10分钟左右），重试应用程序可以掩盖死锁。您可以重试读取或写入，因为写入当然是由适当的开始事务/提交事务包围，以保持所有写入操作为原子，因此能够重试它们的w / o问题。 >

另一个要考虑的方面是打开阅读提交的快照。启用此功能后，SELECT将不会采取任何锁定，而是产生一致的读取。

i have a stored procedure that performs a join of TableB to TableA:

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

At the same time, in a transaction, rows are inserted into TableA, and then into TableB.

This situation is occasionally causing deadlocks, as the stored procedure select grabs rows from TableB, while the insert adds rows to TableA, and then each wants the other to let go of the other table:

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

Logic requires the INSERT to first add rows to A, and then to B, while i personally don't care the order in which SQL Server performs its join - as long as it joins.

The common recommendation for fixing deadlocks is to ensure that everyone accesses resources in the same order. But in this case SQL Server's optimizer is telling me that the opposite order is "better". i can force another join order, and have a worse performing query.

But should i?

Should i override the optimizer, now and forever, with a join order that i want it to use?

Or should i just trap error native error 1205, and resubmit the select statement?

The question isn't how much worse the query might perform when i override the optimizer and for it to do something non-optimal. The question is: is it better to automatically retry, rather than running worse queries?

解决方案

Is it better to automatically retry deadlocks. The reason being that you may fix this deadlock, only to hit another one later. The behavior may change between SQL releases, if the size of the tables changes, if the server hardware specifications change, and even if the load on the server changes. If the deadlock is frequent, you should take active steps to eliminate it (an index is usually the answer), but for rare deadlocks (say every 10 mins or so), retry in the application can mask the deadlock. You can retry reads or writes, since the writes are, of course, surrounded by proper begin transaction/commit transaction to keep all write operations atomic and hence able to retry them w/o problems.

Another avenue to consider is turning on read committed snapshot. When this is enabled, SELECT will simply not take any locks, yet yield consistent reads.

这篇关于SQL Server死锁修复：强制加入订单，还是自动重试？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！