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?
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.aspxAlternatively 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.
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,
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.