CODEWITHSUNDEEP
sqldeadlockdatabase-deadlocks
Sam
Sam

Reputation: 42437

After a deadlock, why should application code wait before retrying?

I'm wanting to write some code to detect deadlocks, and if they occur, retry whatever DB operation was attempted up to n times. I've noticed that people often add a time delay in between retries. Here's some C# code to clarify what I mean:

void RetryIfDeadlocks(Action dbOperation, int maximumRetries)
{
    try
    {
        dbOperation();
    }
    catch (DeadlockException)
    {
        var shouldRetry = maximumRetries > 0;

        if (shouldRetry)
        {
            Task.Delay(millisecondsDelay: 300).Wait();
            RetryIfDeadlocks(dbOperation, maximumRetries - 1);
        }
        else
            throw;
    }
}

Why should such retry logic include such a time delay between retries?

Upvotes: 2

Views: 1452

Answers (2)

usr
usr

Reputation: 171236

Waiting is not necessary to make progress. The conflicting transaction that survived was likely granted the lock that the conflicting transactions contended on.

On the other hand, the other transaction is likely still active and might be doing similar things. Another deadlock is likely. After a small delay, the other transaction(s) are probably one or are doing other things now.

A retry makes succeeding in the second try more likely. It is not required for correctness.

Upvotes: 1

Makita
Makita

Reputation: 746

Without a delay, deadlock retries could 'slam' the network/disk/database with activity until the loop stops. It's much better to place a small delay in the loop, allowing other traffic to get through first (which may in fact be necessary to resolve the deadlock), before trying again.

Upvotes: 2

Related Questions