SQL Server Deadlock Fix: Force join order, or automatically retry?

Question

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?

Answer 1

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.