Guideline for Resolving Deadlock
Guideline for Resolving Deadlock
By : Kasim Wirama, MCDBA
In shared database resource where concurrent access always occurs, you might see deadlock. If you want to get more information about deadlock in SQL Server 2005 such as latest SQL statement before deadlock and types of deadlock, you need to turn on 1222 trace flag in SQL Server 2005.
After you get those information and analyze it, then follow these guidelines to resolve deadlock.
1. Be aware which solution you will choose, short term or long term.
For example in short term solution, you add hints such as NOLOCK at SELECT statement, and for long term, you need to change to code that participates in transaction and test it.
2. Shorten the transaction if possible
3. Identify missing index and add it.
4. Retry transaction if deadlock happens.
To identify if the deadlock happens, use error handling function ERROR_NUMBER(), error number for deadlock is 1205
5. Lower deadlock priority of one process with SET DEADLOCK_PRIORITY <number>, with number ranging from -10 to 10 or one of this options (LOW, NORMAL, or HIGH) in SQL Server 2005.
Other resource you need to refer is SQL Server Books Online, and search for topic “Minimizing Deadlocks”.