Post reply

SQL Server Deadlock Explanation

  • October 20, 2004 at 1:15 pm

    #361367

    Can someone please give me some insight into this SQl Server error:

    Diagnostic: ODBC Error Code = 40001 (Serialization failure)<P> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    It just recently started happening (when an UPDATE query is run). Is the problem happening because 2 users are hitting the internet page simultaneously? Can anyone give me some advice?

    Thanks, in advance, for taking the time to provide me with some assistance.

    Gary

     

  • October 21, 2004 at 3:31 am

    #527421

    In brief, a deadlock occurs when two transactions are blocking each other, and there is no way to resolve the situation. For example, suppose transaction X involves locking tables A and B in that order, and transaction Y involves locking the same two tables in the other order, B then A. In a system where X and Y are run often enough and concurrently, eventually the following will happen:

    Time 1: X locks A

    Time 2: Y locks B

    Time 3: X seeks to acquire a lock on B, and blocks waiting on Y

    Time 4: Y seeks to acquire a lock on A, and blocks waiting on X

    X and Y can now never recover. Fortunately (maybe), SQL Server can detect such situations, and will look at the two transactions, decide which has 'done the least work', and force that one to rollback, issuing it with the message you have seen. Earlier and less sophisticated DBMSs made you work out the deadlock on your own.

    You have broadly two options:

    1) Work out why you are getting deadlocks, and change whatever needs to be changed to avoid them. In the ultra-simple example above, ensuring that every transaction always seeks to acquire locks on objects in the same order will fix the problem

    2) Provide code that catches the deadlock message from any transaction, waits a short random time (0-1 seconds), and retries the transaction. A random time because otherwise you can end up just repeating the problem.

    The complexity of your system and how good you are with concurrency will decide which is the easier option. SQL Server Books Online has some introductory material on locking, concurrency, and deadlocking. And there's plenty more literature if you really want to delve into it

  • October 21, 2004 at 8:04 am

    #527467

    You can run the following code to trace dead lock. SQL server will log detail infos into error log including the command it's running in the involved two processes when a dealock happens.

    DBCC TRACEON(3605)

    DBCC TRACEON(1204)

    You need to run it whenever sql server is restarted.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply