Search code examples
sqldeadlockdatabase-deadlocks

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?


Solution

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