Description of handling deadlock conditions in SQL Server

Article translations Article translations
Article ID: 118552 - View products that this article applies to.
This article was previously published under Q118552
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

SUMMARY

A deadlock is a condition where 2 (or more) processes attempt to access a resource that the other holds a lock on. Since each process has a request for the other's resource, neither process can be completed. When a deadlock is detected, SQL Server rolls back the transaction that has the least processing time and returns error message 1205 to the application. Error message 1205 terminates the current batch and rolls back the current transaction.

MORE INFORMATION

In some instances, a deadlock condition will cause a DB-Library (DB-Lib) command (such as dbsqlexec, dbsqlok, or dbresults) to return FAIL. It is always the responsibility of the program to check the return codes from each 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 some cases, it is possible to continue execution of subsequent commands in the batch. However, because a deadlock situation occurred and the command that caused it was rolled back, later commands in the batch will probably fail with a more serious error, such as an object not found.



In other instances, a deadlock condition will not cause a DB-Lib command to return FAIL. To handle this condition, the program must check for message 1205 in the message handler and use the dbsetuserdata function to communicate 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 deadlock indicator after every DB-Library call and should cancel the batch if a deadlock is detected.



While it may seem unnecessary to cancel a batch after receiving a 1205 deadlock message, it is necessary because the server does not always abort the batch in a deadlock situation. If the batch is not canceled, any attempt 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 a deadlock condition in "Appendix E Maximizing Consistency and Concurrency" in the SQL Server for Windows NT "Programmer's Reference for C".

Properties

Article ID: 118552 - Last Review: February 28, 2014 - Revision: 5.3
APPLIES TO
  • 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
Keywords: 
kbnosurvey kbarchive kbinfo kbprogramming KB118552

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com