SQL Server 2005: Transaction Deadlock

Hi ppl,

I am getting this error very frequently but not consistently on 2 pages in an application which is in a production environment. I just have some screen shots of the error below.

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

What should the approach be to resolve this error. The db server is SQL Server 2005.

From stackoverflow

• This article by Brad McGehee is a good place to start.

• Here is the bible on deadlock troubleshooting:

  http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

  Mitch Wheat : didn't you just answer another question with exactly the same answer?! :-)

  SQLMenace : That was in September, are you talking about this one? http://stackoverflow.com/questions/52566/mssql-server-2000-debugging-deadlocks#52594

• See also here: Proactive Deadlock Notifications

  Mitch Wheat : I don't wish to seem negative but using the profiler deadlock events is the usual place to start in SQL Server 2005, rather than the trace flags.

• You could do as the error message suggests and get the program to retry the transaction!

  Depends very much on how "atomic" the transaction is though! i.e. If you were deadlocked its likly that some other process has succesfully updated the row you are interested in. Does it still make sense to apply the update to the row in htese circumstances?

  At the very least present the user with a nicer error message ("Another user has changed the xxxx you were attempting to update. Please review the new values and try again.)

• You need to run a Deadlock profile trace while the errors are occurring. The article by Brad McGehee is a overview. You need to identify the two offending processes. After that, review the code in the two pages to see what SQL commands are being issued and how often. Most of the time, I have found that simplying reviewing the SQL code being run and knowing how often it runs will qucikly identify the conflict. Fixing it sometimes takes longer...