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 = 1653632984
To obtain the index, run the following code:
select * from sysindexes where indid = 2 and id = 1653632984
相关文章
- MICROSOFT SQL SERVER TO POSTGRESQL MIGRATION USING PGLOADER
- sql server 2012远程链接的方法及步骤
- SQL SERVER使用ODBC 驱动建立的链接服务器调用存储过程时参数不能为NULL值
- MS SQL 错误:The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "test" was unable to begin a distributed transact
- 解决SQL Server管理器无法连接远程数据库的问题(转)
- SQL Server 取前一天的0点和23点59分59秒
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (
- mysql启动时报错:Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql/data/mysql.pid) 的解决方法
- Sql Server用管理器建表后如何查看创建表的语句
- 如何让Activiti-Explorer使用sql server数据库
- Sql Server中sql语句自动换行
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- Sql Server REPLACE函数
- Sql Server 分页存储过程
- Atitit.mysql oracle with as模式临时表模式 CTE 语句的使用,减少子查询的结构性 mssql sql server..
- how is my real odata request hijacked by Mock server
- 全网多种方法解决You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
- [0] 数据库监测SQL Server Profiler
- 无法Debug SQL: Unable to start T-SQL Debugging. Could not attach to SQL Server process on
- 【大数据开发运维解决方案】记一次同事不慎用root起动weblogic以及启动日志卡在The server started in RUNNING mode 问题解决过程
- SQL Server 2005 实现数据库同步备份 过程--结果---分析
- 如何启动 SQL Server Agent(SQL Server 配置管理器)
- SQL Server GOTO使用实例详解
- SQL server多表联合查询
- SQL Server 基础系列篇
- SQL Server查询设计器
- Zabbix 4.0 zabbix_server.conf配置参数含义