asked on

Deadlock between an update and select query. Trap error.

Hi, I've got a website where various users ,at the same time,can be connected and perform actions such updates or select queries.
I'm getting a deadlock error from the database (using MS SQL). I think this is happening since I added some non-clustered indexes to some columns. The lock is happening between an update and a select statement.

Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Open in new window

This is sending an error to my website and dispaying and error page when this happens. I was thinking of trapping the deadlock error in code (1205) and ignore it.

catch (SqlException ex)
{
    if (ex.Number == 1205)
    {
        // Deadlock 
        // Rerun query??
    }
    else
        throw;
}

Open in new window

Is this a good solution?? I'm afraid I cannot alter the table and its indexes as this would cause some problems.
Any ideas how to solve this??

Vitor Montalvão

You shouldn't just ignore this error as it means that somebody's process was just killed and won't be completed successfully.
You need to find the queries that are causing the deadlock, analyze them and try to solve the root cause issue.
This may not be an easy task but try to read and follow this article that explains how to troubleshoot and solve a deadlock issue.

noci

If a database declares a deadlock that means it found somecirular reference and the update that got the Deadlock error was declared the victim. (so others could continue). (Any changed from the "declared" victim session should be rolled back).
The best action would be to completely restart the transaction from the beginning...

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial