Avoiding Deadlocks

Question

We currently have a trigger-based system in place that does the following:

• When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in another table, we'll call that table [LRPP].
• A trigger on [LRPP] then fires off a procedure that does some large calculations based on which records were marked as NULL. This call is made in a separate thread using a CLR, so after it is executed, the current transaction continues without waiting for it to finish.
• When the calculations are done (in their separate thread) they do INSERTs AND/OR UPDATEs AND/OR DELETEs on [LRPP]

The issue is that if this process simultaneously fires off multiple times for the same set of records, due to highly related data being changed, it will almost always deadlock. The deadlock occurs between the portion of the trigger that initially updates [LRPP] with the NULL values, and the DELETE operation executed by the procedure that runs the calculations.

What kind of things could I do to fix this issue? I was thinking of wrapping the INSERT/UPDATE/DELETE portion in an explicit transaction, but I am not sure this will help at all.

I have the following restrictions:

• The overall architecture cannot be changed right now, the trigger-based system with the NULL values must be used.
• All the operations that are currently happening must continue to happen in real time, none of this process can be deferred or aggregated in any way.

My initial thought is that we need to replace the 'NULL setting' portion with some sort of tracking table that gets truncated on a regular basis, but this is a big change that will take time. I am looking for something a little quicker to alleviate the issue until a larger fix can be designed and tested.

Thanks!

Answer 1

As long as the code path is very predictable, a quick solution would be to use sp_getapplock requesting an Exclusive lock. This method does not take a lock on the table itself -- it's up to the application (database) code to respect the lock.

What this will do is serialize running the asynchronous process, so that only one is running against the data at a time. This means you may still have deadlocks, but it will eliminate the deadlocks between multiple instances of the process.

You'll need to be careful that the number of processes and the amount of time it takes to execute is balanced correctly, otherwise you may end up creating a queue that can never be cleared out completely. Because of the way it's implemented now in CLR, this may very well end up starving SQL Server of threads, which is Really Bad. Mind you, that's certainly a possibility right now anyway.

Long-term, you'll probably want to switch this solution to use Service Broker, which gives you messaging and queuing functionality right in the database itself. I have a video here that explains the fundamentals of Service Broker if you're new to it.