AAsk AAsk - 2 months ago 13
SQL Question

Retries in Catch block?

How can I implement the code in the catch block?

try
{
// Call a MS SQL stored procedure (MS SQL 2000)
// Stored Procedure may deadlock
}
catch
{
// if deadlocked Call a MS SQL stored procedure (may deadlock again)
// If deadlocked, keep trying until stored procedure executes
}
finally
{

}

Answer

Doing this isn't recommended and could cause serious problems in your program. For example, what if the database was down?

But, here's how to do it in a loop:

for(int attempts = 0; attempts < 5; attempts++)
// if you really want to keep going until it works, use   for(;;)
{
    try
    {
        DoWork();
        break;
    }
    catch { }
    Thread.Sleep(50); // Possibly a good idea to pause here, explanation below
}

Update: As Mr. Disappointment mentioned in a comment below: The Thread.Sleep method pauses the execution for the specified number of milliseconds. No error is completely random, most that would work simply by trying again only work because something has changed in the time it took between the tries. Pausing the execution of the thread will give a much bigger window of opportunity for this to happen (for example, more time for the database engine to start up).