SQL Server Deadlocks – How to Avoid Deadlocks in SQL Server

deadlockperformancesql serversql-server-2008t-sql

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!

Best Answer

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.

Related Solutions

SQL Server – Main Causes of Deadlocks and Prevention

tracking deadlocks is the easier of the two:

By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

Write deadlock info to the SQL Server error log: DBCC TRACEON(-1, 1204, 3605)

Turn it off: DBCC TRACEOFF(-1, 1204, 3605)

See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on. https://msdn.microsoft.com/en-us/library/ms178104.aspx

Prevention is more difficult, essentially you have to look out for the following:

Code Block 1 locks resource A, then resource B, in that order.

Code Block 2 locks resource B, then resource A, in that order.

This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back. Now you have deadlock.

To prevent this condition, you can do something like the following

Code Block A (psuedo code)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Code Block B (pseudo code)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

not forgetting to unlock A and B when done with them

this would prevent the deadlocking between code block A and code block B

From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data. Where I've seen the most issues occur is where you saw yours, inside a cursor. Cursors are notoriously inefficient, avoid them if at all possible.

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.

Best Answer

Related Solutions

SQL Server – Main Causes of Deadlocks and Prevention

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

Related Question