SQL Server deadlock

You have written a very useful enterprise application. All of a sudden users complain that data they entered is lost. You hurriedly start taking a look at the SQL Server log or run a trace using SQL Profiler, and see the dreaded message:

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

Quite obviously your code does not expect this error and does not rerun that transaction. You start analyzing the deadlock scenario, if you can reproduce it. After a while you reach the conclusion that you just can’t change anything in those complex stored procedures that write data to the database.

In this particular scenario, one solution that did help me was to lock the entire table using TABLOCK or TABLOCKX. It might seems like something really drastic to do, but if you have long running procedures, that hopefully do not exceed the timeout period of your database transaction, then it can be one quick-fix alternative.

You can use the NOLOCK hint on other SELECT queries so that they don’t wait for your transaction to be committed, before returning useful results.

The long term solution is obviously more elaborate. You can:

  • Optimize you queries or redesign your code (and requirements) so that the transaction time is as short as possible
  • Detect transaction failures and rerun the transaction again
  • Run long transactions at programmed downtimes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s