Andy Brownsword shares a tip:
Deadlocks are an enduring feature of SQL Server. They’ve been a source of pain for many over the years and there are various ways to diagnose, mitigate or resolve them. Here I want to demonstrate approach I haven’t seen discussed – using an application lock to segregate processes.
An example where I’ve used this effectively was for a queue table where work would be placed and a number of processes would nibble away at it throughout the day. Separate to this was a maintenance routine which ran each evening to manage partitions on the table. The maintenance job would kick in and would deadlock all those processes.
This was solved with an Application Lock.
Read on to learn more about application locks, how they work, and why they fixed the problem in this scenario. Generally, this happens when two operators access two resources in different orders. For example stored procedure A has a transaction which locks table 1 and then locks table 2 and then commits. Meanwhile, stored procedure B has a transaction which locks table 2 and then locks table 1 and then commits. The classical solution is to fix the ordering such that both are consistent. But not all deadlocking behavior is that straightforward or that simple, and so other solutions like app locks can be quite helpful to know about.