You are currently offline, waiting for your internet to reconnect

Description of handling deadlock conditions in SQL Server

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q118552
This article has been archived. It is offered "as is" and will no longer be updated.
A deadlock is a condition where 2 (or more) processes attempt to access aresource that the other holds a lock on. Since each process has a requestfor the other's resource, neither process can be completed. When a deadlockis detected, SQL Server rolls back the transaction that has the leastprocessing time and returns error message 1205 to the application. Error message1205 terminates the current batch and rolls back the current transaction.
In some instances, a deadlock condition will cause a DB-Library (DB-Lib)command (such as dbsqlexec, dbsqlok, or dbresults) to return FAIL. It isalways the responsibility of the program to check the return codes fromeach DB-Lib command. If FAIL is returned by one of these DB-Lib commands,the program should cancel the batch and not attempt to continue. In somecases, it is possible to continue execution of subsequent commands in thebatch. However, because a deadlock situation occurred and the command thatcaused it was rolled back, later commands in the batch will probably failwith a more serious error, such as an object not found.

In other instances, a deadlock condition will not cause a DB-Lib command toreturn FAIL. To handle this condition, the program must check for message1205 in the message handler and use the dbsetuserdata function tocommunicate this to your application (an example can be found in Chapter 4"DB-Library Functions" in the SQL Server "Programmer's Reference for C"under dbsetuserdata). The program must then check for the deadlockindicator after every DB-Library call and should cancel the batch if adeadlock is detected.

While it may seem unnecessary to cancel a batch after receiving a 1205deadlock message, it is necessary because the server does not always abortthe batch in a deadlock situation. If the batch is not canceled, anyattempt to submit a new batch may result in a DB-Library error 10038"Results Pending."

You can find a description of deadlocking and an example of how to detect adeadlock condition in "Appendix E Maximizing Consistency and Concurrency"in the SQL Server for Windows NT "Programmer's Reference for C".
DB-Library deadlock Windows NT dblib

Article ID: 118552 - Last Review: 02/28/2014 07:55:17 - Revision: 5.3

Microsoft SQL Server 4.21a Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbnosurvey kbarchive kbinfo kbprogramming KB118552