Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Yeah, step one in our environment was to stop the CLR from calling one proc PER record updated. Next step would be to get rid of it completely. I will look into sp_getapplock. –  Mark Wilkinson Feb 26 at 17:20

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks Jon, this sounds like a good approach and I will likely implement SB in the future. I watched your video earlier today and gave it a Tweet as well. Thanks for your help! –  Mark Wilkinson Feb 26 at 19:46
    
@Mark: Oh, nice, I didn't see that yet; thanks! And you're welcome! –  Jon Seigel Feb 26 at 19:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.