Error 1205
Severity Level 13
Message Text
Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Explanation
This error occurs when Microsoft® SQL Server™ encounters a deadlock. A deadlock occurs when two (or more) processes attempt to access a resource that the other process holds a lock on. Because 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 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. The program must then check for the deadlock indicator after every DB-Library call and should cancel the batch if a deadlock is detected.
Although 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 DEADLOCK_PRIORITY statement (LOW or NORMAL). SET DEADLOCK_PRIORITY 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 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 7 chosen as deadlock victim
== Deadlock Detected at: 1998-09-10 16:39:29.17
== Session participant information:
SPID: 7 ECID: 0 Statement Type: UPDATE
Input Buf: update t1 set c1 = c1 where c1 = 2
SPID: 8 ECID: 0 Statement Type: UPDATE
Input Buf: update t1 set c1 = c1 where c1 = 1
== Deadlock Lock participant information:
== Lock: KEY: 2:117575457:1 (010001000000)
Database: tempdb
Table: t1
Index: i1
- Held by: SPID 7 ECID 0 Mode "S"
- Requested by: SPID 8 ECID 0 Mode "X"
== Lock: KEY: 2:117575457:1 (020002000000)
Database: tempdb
Table: t1
Index: i1
- Held by: SPID 8 ECID 0 Mode "S"
- Requested by: SPID 7 ECID 0 Mode "X"
This deadlock information can be interpreted as follows:
- The first section displays the deadlock victim and time of deadlock, along with the sessions involved in the deadlock. For each session, the current SPID, statement type, and a portion of the input buffer are displayed.
- The second section displays details about the locks involved in the deadlock. From the output above, note that the deadlock involves key locks on table t1, index i1. The deadlock output shows which processes own the locks involved in the deadlock and which sessions are waiting for the locks to be granted as well as the associated lock modes.
- The process that has generated the least amount of log volume will, by default, be chosen as the deadlock victim and be rolled back automatically. To influence which session is rolled back, set the DEADLOCK_PRIORITY for a session.