Setting rowlock order

Question

We have a deadlock issue in our environment. We need to select some rows and then update them.
Those queries are very frequent so we use a rowlock and updlock hints in select statement.
Those queries sometimes are causing deadlocks.
We believe that the reason is that two queries start locking the same rows on the same index but in different index "direction".
So when the same queries run simultaneously they can sometimes lock each other.
Do this assumption makes sense?
If yes, is there anything we can do to force "rowlock locking order"?

• Tom V: Does the problem still occur if you remove all your hints and let SQL server handle it?
• Michael Cherevko: Deadlocks wouldn't happen between selects but then we'll face other issues when we'll try to update those rows from different processes.

Answer 1

Follow the Microsoft prescribed Detecting and Ending Deadlocks article. You should never "guess" at the reason for the deadlocks, otherwise you will be guessing at solutions. The short list of steps to take is enable trace flags 1204 and 1222. The output will give you explicitly the resource ID of what what's actually involved. Could be a foreign key constraint, index page. Only knowing that can you target what to try to fix with your queries, indexes or even isolation level.

• Michael Cherevko: Thanks - the "guess" is after running and collecting information from extended events session. Both of the queries looks the same and tries to lock the same index.