none
Transaction was deadlocked on lock resources with another process

    Question

  • We are getting the following logged by our C# web application a few times a day:

     

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

     

    The database is 2.1GB and growing. We are running Microsoft SQL 2005 Express Edition, on a 2x (Quad Core Xeon 2.33ghz) with 8 Gigs of ram.

     

    Any ideas of things to try? Optimize?

     

    Friday, September 19, 2008 8:23 PM

All replies

  • Hi Justin,

    Deadlocks can be tricky things to figure out.  First, you'll want to turn on Trace Flag 1222, which will write details about the deadlock into the ERRORLOG file.  See Bart Duncan's excellent article at http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

    Alternatively to the trace flag, if you have a full version of SQL Server which includes Profiler (ie Standard, Developer or Enterprise), you can use Profiler to trace your Express instance to capture Deadlock Graph events, which will show similar information.

    Once you have some deadlock information, you can post the XML and we can help analyse what resources are blocked and why.
    Friday, September 19, 2008 10:01 PM
    Answerer
  • Jim,

     

    Thanks for the assistance. Once I get something logged in the error_log about the details of the dead-lock I will post it. Thanks again.

    Friday, September 19, 2008 10:33 PM
  • Justin,

    Adding to what Jim said there are a few good articles written by Jonathan and Madhu regarding the deadlocks which would help you. Please have a look at them in the below given links,

    1. Troubleshooting Deadlocks
    2. Troubleshooting Deadlocks Part II
    3. Anatomy of Deadlocks
    Once the queries are captured we can analyze it. I recently encountered deadlocks and managed to minimize them using proper indexing and setting Isolation level as Read Committed.

    - Deepak

    Saturday, September 20, 2008 12:52 AM
    Moderator