Error 1205

Severity Level 13

Message Text

Your server command (process id#%d) was deadlocked with another process and has been chosen as deadlock victim. Re-run your command.

Explanation

This error occurs when SQL Server encouters a deadlock. A deadlock occurs when two (or more) processes attempt to access a resource that the other process holds a lock on. Since each process has a request for another resource, neither process can be completed. When a deadlock is detected, SQL Server rolls back the command that has the least processing time and returns this error message 1205 to the client application. This error is not "fatal" and may not cause the batch to be terminated.

Action

In some instances, a deadlock condition will cause a DB-Library function (such as dbsqlexec, dbsqlok, dbresults, or dbnextrow) to return FAIL. It is always the responsibility of the program to check the return codes from each DB-Library function. If FAIL is returned by one of these DB-Library functions, the program should cancel the batch and not attempt to continue. In some cases, it is possible to continue execution of subsequent functions in the batch. However, because a deadlock situation occurred and the functions that caused it were rolled back, later functions in the batch will probably fail with a more serious error, such as "object not found."

In other instances, a deadlock condition will not cause a DB-Library function to return FAIL. In these cases, the program must check for error message 1205 in the message handler and use the dbsetuserdata function to communicate this to the application (an example can be found in the Microsoft SQL Server Programming DB-Library 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 terminate the batch in a deadlock situation. If the batch is not canceled, any attempt to submit a new batch can result in a DB-Library error 10038 "Results Pending."

You can also use the SET statement with the DEADLOCKPRIORITY option (LOW | NORMAL). SET DEADLOCKPRIORITY controls how the session reacts when in a deadlock situation. If set to LOW, the process will be the preferred victim of a deadlock situation. If set to NORMAL, the session will use the default deadlock-handling method.

If a deadlock situation continues, it is often useful to use the DBCC trace flag 1204 to gather more information. Trace flag 1204 prints out the deadlock chains and victim, as shown in this sample output:

***Deadlock detected - process 5 trying to wait on a process 1 
Deadlock chain -->
LOCK REQUEST INITIATING DEADLOCK: LOGICAL:
SH_PAGE at 0x1887551e
    lockid=6074 spid=5 dbid=6
Process 1 waiting on Process 5 for resource: LOGICAL
Lock requested by spid 1:
SH_PAGE at 0x1867151e
    lockid=4301 spid=1 dbid=6

BLOCKED by spid 5 with the following lock:
EX_PAGE Blocking at 0x256752c0
    lockid=4301 spid=5 dbid=6
    pstat=0x0100[]

    VICTIM: process 1;pstat 0x0100[]; cputime=26
    VICTIM: process5;pstat 0x0100[]; cputime=8
    Process 5 was selected as victim
Your server command (process id j#5 was deadlocked with
another process and has been chosen as deadlock victim.
Re-run your command.
(Msg 1205, Level 13, State 1)
    Caller is deadlock victim (5: 0x0100)

You can extract the following information from this output:

• The spid and dbid numbers accurately reflect the spid and dbid involved.
• For a page lock, the lockid value is the logical page number in the database on which the deadlock occurred. For information on associating a page with an object to determine the table name that the page belongs to, see Chapter 24, "Additional Problem-solving Techniques." For a table lock, the lockid is the object id.
• The hex values for the lock pointers (SH_Page at 0x1887551e) are lock pointers. They are not of immediate value in debugging a deadlock problem.
• The deadlock victim is accurately reflected.
• The process with the least CPU time is selected as deadlock victim.

For details about deadlocking and an example of how to detect a deadlock condition, see the Microsoft SQL Server Database Developer's Companion.