I have 2 queries which when run at the same time are causing a deadlock.
Query 1 – update a column which is included in an index (index1):
update table1 set column1 = value1 where id = @Id
Takes X-Lock on table1 then attempts an X-Lock on index1.
Query 2:
select columnx, columny, etc from table1 where {some condition}
Takes an S-Lock on index1 then attempts an S-Lock on table1.
Is there a way to prevent the deadlock while maintaining the same queries? For example can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order – which should prevent the deadlock?
Isolation level is Read Committed. Row and page locks are enabled for the indexes. It's possible that the same record is participating in both queries – I can't tell from the deadlock graph as it doesn't show the parameters.
Best Answer
The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case):
As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in different orders. The
SELECT
query needs to access the index before the table due to the RID lookup, whereas theUPDATE
query modifies the table first, then the index.Eliminating the deadlock requires removing one of the deadlock ingredients. The following are the main options:
SELECT
query returns 26 columns.Proposal
. This is worth considering, though it appears this column is of typeuniqueidentifier
, which may or may not be a good choice for a clustered index, depending on broader issues.READ_COMMITTED_SNAPSHOT
orSNAPSHOT
database options. This would require careful testing, especially with respect to any designed-in blocking behaviours. Trigger code would also require testing to ensure the logic performs correctly.READ UNCOMMITTED
isolation level for theSELECT
query. All the usual caveats apply.You can try this, by wrapping the update in an explicit transaction and performing a
SELECT
with anXLOCK
hint on the nonclustered index value before the update. This relies on you knowing for certain what the current value in the nonclustered index is, getting the execution plan right, and correctly anticipating all the side-effects of taking this extra lock. It also relies on the locking engine not being smart enough to avoid taking the lock if it is judged to be redundant.In short, while this is feasible in principle, I do not recommend it. It is too easy to miss something, or to outsmart oneself in creative ways. If you really must avoid these deadlocks (rather than just detecting them and retrying), I would encourage you to look instead at the more general solutions listed above.