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.