Description of handling deadlock conditions in SQL Server (118552)

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".