How to automatically re-run deadlocked transaction? (ASP.NET MVC/SQL Server)_问答_开发者

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 err开发者_如何转开发or screen?

Remus's answer is fundamentally flawed. According to https://stackoverflow.com/a/112256/14731 a consistent locking order does not prevent deadlocks. The best we can do is reduce their frequency.

He is wrong on two points:

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.
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.

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.

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

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.

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