How to automatically re-run deadlocked transaction? (ASP.NET MVC/SQL Server)

Question

I have a very popular site in ASP.NET MVC/SQL Server, and unfortunately a lot of deadlocks occur. While I'm trying to figure out why they occur via the SQL profiler, I wonder how I can change the default behavior of SQL Server when doing the deadlocks.

Is it possible to re-run the transaction(s) that caused problems instead of showing the error screen?

Oriole

Answer 1

You are barking up the wrong tree. You will never succeed in doing automated deadlock retries by the SQL engine, such concept is fundamentally wrong. The very definition of deadlock is that the state you base your decision on has changed therefore you need to read again the state and make a new decision. If your process has deadlocked, by definition another process has won the deadlocks, and it meas it has changed something you've read.

Your only focus should be at figuring out why the deadlocks occur and eliminate the cause. Invariably, the cause will turn out to be queries that scan more data that they should. While is true that other types of deadlock can occur, I bet is not your case. Many problems will be solved by deploying appropriate indexes. Some problems will send you back to the drawing board and you will have to rethink your requirements.

There are many, many resources out there on how to identify and solve deadlocks:

• Detecting and Ending Deadlocks
• Minimizing Deadlocks

You may also consider using snapshot isolation, since the lock-free reads involved in snapshot reduce the surface on which deadlocks can occur (ie. only write-write deadlocks can occur). See Using Row Versioning-based Isolation Levels.

Cavender

Answer 2

Remus's answer is fundamentally flawed. According to https://mcmap.net/q/282425/-zero-sql-deadlock-by-design-any-coding-patterns a consistent locking order does not prevent deadlocks. The best we can do is reduce their frequency.

He is wrong on two points:

1. The implication that deadlocks can be prevented. You will find both Microsoft and IBM post articles about reducing the frequency of deadlocks. No where do they claim you can prevent them altogether.
2. The implication that all deadlocks require you to re-evaluate the state and come to a new decision. It is perfectly correct to retry some actions at the application level, so long as you travel far back enough to the decision point.

Side-note: Remus's main point is that the database cannot automatically retry the operation on your behalf, and he is completely right on that count. But this doesn't mean that re-running operations is the wrong response to a deadlock.

Uda

Answer 3

A lot of deadlocks occurring is often an indication that you either do not have the correct indexes and/or that your statistics are out of date. Do you have regular scheduled index rebuilds as part of maintenance?

Your save code should automatically retry saves when error 1205 is returned (deadlock occurred). There is a standard pattern that looks like this:

catch (SqlException ex) 
{ 
    if (ex.Number == 1205) 
    { 
        // Handle Deadlock by retrying save...
    } 
    else 
    {
        throw; 
    }
} 

The other option is to retry within your stored procedures. There is an example of that here: Using TRY...CATCH in Transact-SQL

Fari

Yeah, I know: there must be a glitch in my logic. But the question is how can I change the behavior of the server? – Oriole

He's not saying your code is bad. He's saying that you should review the indices on your tables in the database, and either recreate them or add new ones. – Armandoarmature

Okay... Another question then. Let's say deadlock occurs on page A, but page B is trying to access the locked data. The error will be displayed on page B, but it doesn't mean that the deadlock occurred on page B. It still occurred on page A. Sorry if this is stupid, it's really hard for me to understand this concept. – Oriole

Answer 4

One option in addition to those suggsted by Mitch and Remus, as your comments suggest you're looking for a fast fix. If you can identify the queries involved in the deadlocks, you can influence which of the queries involved are rolled back and which continue by setting DEADLOCK_PRIORITY for each query, batch or stored procedure.

Looking at your example in the comment to Mitch's answer:

Let's say deadlock occurs on page A, but page B is trying to access the locked data. The error will be displayed on page B, but it doesn't mean that the deadlock occurred on page B. It still occurred on page A.

If you consistently see a deadlock occuring from the queries issued from page A and page B, you can influence which page results in an error and which completes successfully. As the others have said, you cannot automatically force a retry.

Post a question with the problem queries and/or the deadlock trace output and theres a good chance you'll get an explanation as to why its occurring and how it could be fixed.

Nevadanevai

Thank you. The problem is that the deadlock occurs in a stored procedure. SQL Profiler shows the name of that procedure, but I can't get the query where it occurs, and there are a lot of queries there. How can I track in which query the problem occurred? – Oriole

You can enable trace flags 1204 and 1222, both of which are covered in the "Detecting and Deadlocks" article Remus linked. That will give you the details of the queries and which resources. The rows, pages, indexes or tables involved and the types of lock held and required by each process will be reported. From this, you may be able to determine a fix, else post a question with the deadlock trace and source of the deadlocking procedures. Are you unable to modify the stored procedures where the problem occurs? – Nevadanevai

No, I can modify the SPs. I'll check out the article, and enable the flags. Hopefully it will work. – Oriole

Ok, I enabled the flags, now I can see where in the stored procedure dead lock occurs. But I can't see the conflict query in other pages. And since it occurs on most of 20-30 pages, it's hundreds and hundreds of queries! I'm getting sick of this problem.... – Oriole

Update your question with a more detailed description of the problem. Could you sketch out the functionality in pseudo code? – Nevadanevai

I managed to get info about where the deadlocks are coming from. I posted a detailed information about the queries that cause the deadlock here: #2945635 – Oriole

Answer 5

in some cases, you can do below. Between begin tran and commit is all or nothing. So either @errorcode take 0 as value and ends loop, or, in case of failure, decrease counter by 1 and retry again. It may not work if you provide variables to code from outside begin tran/commit. Just an Idea :)

declare @errorcount int = 4 -- retry number
while @errorcount >0
begin
begin tran 
<your code here>
set @errorcount =0
commit 
set @errorcount=@errorcount-1
end

Ranjiv