Export (0) Print
Expand All

Handling Deadlocks

SQL Server 2000

Handling Deadlocks

When a transaction submitted by an application is chosen as the deadlock victim, the transaction is terminated automatically and rolled back, and error message 1205 is returned to the application. Because any application submitting SQL queries can be chosen as the deadlock victim, applications should have an error handler that can trap error message 1205. If an application does not trap the error, it can proceed unaware that its transaction has been rolled back, and errors can occur.

Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation and take remedial action (for example, automatically resubmitting the query that was involved in the deadlock). Resubmitting the query automatically can mean that the user does not need to know that a deadlock occurred.

Before resubmitting a query automatically, client programs should pause to give the transaction holding the required locks a chance to complete and release those locks. This minimizes the likelihood of the transaction being deadlocked again as it attempts to obtain those locks.

Note  A deadlock does not always cancel the batch in which the error was returned. It is important for the client program to check for errors because a deadlock does not always return a failed return code. In most cases, if a deadlock has occurred and the batch has not been canceled automatically, the application should cancel the current query. If this is not done, SQL Server may still have results pending on the connection that it expects the client to process. If any pending results are not processed, an error occurs when the application next tries to send a command to SQL Server.

See Also

Handling Errors and Messages

© 2014 Microsoft