SQL Server technical bulletin - How to resolve a deadlock
How to identify a deadlock
Step 1
To identify a deadlock, you must first obtain log information.
If you suspect a deadlock, you must gather information about the (SPIDs) and the resources that are involved in the deadlock.
To do this, add the -T1204 and the -T3605 startup parameters to SQL Server.
To add these two startup parameters, follow these steps:
- Start SQL Server Enterprise Manager. 【这里可以使用sql server configuration manager】
- Select, and then right-click the server.
- Click Properties.
- Click Startup Parameters.
- In the Startup Parameters dialog box, type -T1204 in the Parameters text box, and then click Add.
- In the Parameters text box, type
-T3605, and then click Add. - Click OK.
The startup parameters will take effect when SQL Server is stopped and then re-started.
The -T1204 startup parameter collects information about the process and the resources when the deadlock detection algorithm encounters a deadlock.
The -T3605 startup parameter writes this information to the SQL Server error logs.
1205
The -T1205 startup parameter collects information every time that the deadlock algorithm checks for a deadlock, not when a deadlock is encountered. You do not have to use the -T1205 startup parameter.
If you do use the -T1205 startup parameter, the following is a sample of the output that will be in the SQL Server error log:
2003-05-14 11:46:26.76 spid4 Starting deadlock search 1
2003-05-14 11:46:26.76 spid4 Target Resource Owner:
2003-05-14 11:46:26.76 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4
2003-05-14 11:46:26.76 spid4 End deadlock search 1 ... a deadlock was not found.
2003-05-14 11:46:26.76 spid4 ----------------------------------
2003-05-14 11:46:31.76 spid4 ----------------------------------
2003-05-14 11:46:31.76 spid4 Starting deadlock search 2
备选
Sometimes, you might not be able to stop and re-start SQL Server. In that case, you can use Query Analyzer to run the following command to enable the deadlock trace flags.
Note This way you can gather information about the deadlocks immediately. The "-1" indicates all SPIDs.
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go
Step 2
Next, you must collect a SQL Profiler trace.
If you turn on the deadlock trace flag, you will get most of the required information, but not always.
For example, in a case study the trace flag output identified that a sp_cursoropen system stored procedure and an "UPDATE tblQueuedEvents set notifyid = 3, ResynchDate" statement were involved in a deadlock.
Unfortunately, you do not know the definition of the sp_cursoropen system stored procedure.
You also do not have the complete UPDATE statement because it was truncated.
SQL
Profiler can obtain the full statements in addition to the execution
plans of the statements.
A SQL Profiler trace also has a lock event for "deadlock" and for "deadlock chain."
"Deadlock" corresponds to the -T1204 flag, and "deadlock chain" corresponds to the -T1205 flag.
Turning on the deadlock trace flags and running a SQL Profiler trace during the occurrence of a deadlock should provide you the data that you must have to troubleshoot a deadlock.
In this case, and in others, running SQL Profiler changes the timing of execution enough to prevent the deadlock.
Therefore, you will typically capture the deadlock information with the trace flags, and then you run SQL Profiler.
Troubleshooting a deadlock
After a deadlock occurs, you can gather information about the deadlock by using the
sqldiag
utility and by using SQL Profiler. In the output of the SQLDiag.txt
file, look for a "Wait-for-graph" entry. A "Wait-for graph" entry
indicates that a deadlock was encountered.
The following is a
sample of the output that you might see in the SQL Server error log when
you use the -T1205 startup parameter.
2003-05-05 15:11:50.80 spid4 Wait-for graph
2003-05-05 15:11:50.80 spid4 Node:1
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4 Victim Resource Owner:
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:60 ECID:0 Ec:(0x1F1BB5B0) Value:0x193
2003-05-05 15:11:50.80 spid4 Requested By:
2003-05-05 15:11:50.80 spid4 Input Buf: RPC Event: sp_cursoropen;1
2003-05-05 15:11:50.80 spid4 SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 1
2003-05-05 15:11:50.80 spid4 Owner:0x1937f2a0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0
2003-05-05 15:11:50.80 spid4 Grant List 0::
2003-05-05 15:11:50.80 spid4 KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0
2003-05-05 15:11:50.80 spid4 Node:2
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4 Requested By:
2003-05-05 15:11:50.80 spid4 Input Buf: Language Event: Update tblQueuedEvents Set NotifyID = 2, ResynchDate
2003-05-05 15:11:50.80 spid4 SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2003-05-05 15:11:50.80 spid4 Owner:0x1936e420 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0
2003-05-05 15:11:50.80 spid4 Grant List 0::
2003-05-05 15:11:50.80 spid4 KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0
In the "Wait-for-graph" entry, you have Node 1 and Node 2. In each node, you have a grant section and a request section. The grant section is the "Grant List", and the request section is the "Request By."
In each node, you can identify the following:
- The SPID.
- The command the SPID was executing.
- The resource.
- The lock mode on the resource.
For example, in Node 1, the Grant List, SPID 55 had been granted an
update lock, Mode: U, on resource KEY: 8:1653632984:2. 8=DBID,
1653632984=ObjectID, and 2=Indid.
To obtain the database identification number, run the sp_helpdb stored procedure. To obtain the table, run the following code:
select * from sysobjects where id = 1653632984To obtain the index, run the following code:
select * from sysindexes where indid = 2 and id = 1653632984
