Deadlocks happen when two or more user processes have locks on separate objects, and the processes try to acquire locks on objects the other processes have locked. Deadlocks can strain SQL Server's resources, especially CPU utilization.

SQL Server resolves deadlocks by automatically aborting one process, the "victim" process, so other processes can continue. SQL Server rolls back the aborted transaction and sends an error message to the user of the aborted process. Generally, SQL Server aborts the transaction that requires the least overhead to roll back.

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