First, and to be sure, I'm not trying to be a contrarian here. Out-of-Order DML between two queries can certainly be a problem. I've just personally not found it to be the primary problem.
95% of all the deadlocks problems that I've repaired have simply been due to either inappropriate use of explicit transactions or just junk code that used too many resources and took too long. "Too long" can be surprisingly short on an active system and needs to be fixed even though you may initially be unsure that it can be fixed (and go for it because it can be fixed).
The DML doesn't need to be "Out-of-Order" to cause a deadlock, either. The worst deadlock problem I ever fixed was a SELECT from a table followed by an UPDATE to the table. Both actions were to the same single row (very properly indexed table, BTW) and was lightning fast but both actions were within an explicit transaction and the code was called upon very frequently within the same second. The code would deadlock against other copies of the code even though the DML was identical.
I can't speak for anyone else but here's what I've been very successful with when it comes to deadlocks.
- First, find them. They show up in the SQL Server logs if you have your startup trace flags setup to capture them. (1204 and 1222).
- Realize that WITH (NOLOCK) does NOT prevent deadlocks.
- Rewrite the code to do any validations, lookups, other heavy lifting, and get the data you want to insert and/or update properly staged before you even think of starting a transaction. All of that will help to make the transaction shorter and it always needs to be as short as possible.
- During the rewrite, write good code. "Good enough" frequently just isn't good enough... especially if it's code generated by an ORM. If you can't write the front end code so that the ORM generates good code, then bite whatever bullet you think you need to bite and write an excellent stored procedure.
- During the rewrite and all the pre-work and validation you do, you may actually find that you no longer need an explicit, multi-statement transaction. Get rid of it if you can. If you can't, the Item #4 needs to come to bear in a serious fashion.
I have an old saying... "Performance is in the code". So are the deadlocks. If your code doesn't perform well, then there's more of a chance for deadlocks.