Deadlocks

A deadlock occurs when one process locks a page or table needed by another process, and the second process locks a page that the first process needs. A deadlock is also known as a deadly embrace. SQL Server automatically detects and resolves deadlocks. If a deadlock is found, the server terminates the user process that has completed the deadly embrace.

After every data modification, your program code should check for message number 1205, which indicates a deadlock. If this message number is returned, a deadlock has occurred and the transaction was rolled back. In this situation, your application must restart the transaction.

Deadlocks can usually be avoided by using a few simple techniques:

• Access tables in the same order in all parts of your application.
• Use a clustered index on every table to force an explicit row ordering.
• Keep transactions short.

For more information, see the Microsoft Knowledge Base article, “Detecting and Avoiding Deadlocks in Microsoft SQL Server.”