Deadlocking occurs when two or more user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has locked. When this happens, SQL Server resolves the deadlock by automatically aborting one process, the "victim" process, allowing the other processes to continue.

The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to roll back is the transaction that is aborted.

Deadlocks can cause a strain on a SQL Server's resources, especially CPU utilization.

Dealing with deadlocks

Most well-designed applications will resubmit the aborted transaction after receiving a deadlock message, which is then likely to run successfully. This process can affect performance. If the application has not been written to trap deadlock errors and automatically resubmit deadlocked transactions, users may receive deadlock error messages on their computer.

Tips on avoiding deadlocks

• Ensure the database design is properly normalized.
• Develop applications to access server objects in the same order each time.
• Do not allow any user input during transactions.
• Avoid cursors.
• Keep transactions as short as possible.
  • Reduce the number of round trips between your application and SQL Server by using stored procedures or by keeping transactions within a single batch.
  • Reduce the number of reads. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
• Reduce lock time. Develop applications that obtain locks at the latest possible time, and release them at the earliest possible time.

• If appropriate, reduce lock escalation by using ROWLOCK or PAGLOCK.

• If appropriate, use the lowest possible isolation level for the user connection running the transaction.
• Consider using bound connections.
• As a last resort, you may consider using NOLOCK with extreme caution on non-critical databases. It may increase response times, but may also cause inconsistent or incorrect results to be returned.

Changing default deadlock behavior

When a deadlock occurs, by default, SQL Server chooses a deadlock "victim" by identifying which of the two processes will use the least resources to roll back, and then returns error message 1205. You can change the default behavior by updating the deadlock priority. For example:

SET DEADLOCK_PRIORITY LOW

Or:

SET DEADLOCK_PRIORITY HIGH

For more details about alternative arguments used in this syntax, see http://msdn.microsoft.com/en-us/library/ms186736.aspx

For more information about deadlocks, see SQL Server 2008 Books Online: Detecting and Ending Deadlocks: http://msdn.microsoft.com/en-us/library/ms178104.aspx