Question

如何排查SQL死锁的错误

Answer 1

1. 开启死锁日志输出(deadlock trace)

DBCC TRACEON(1204,1222)

Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock.
Trace flag 1222 formats deadlock information, first by processes and then by resources.

开启了上面的选项之后, SQL会输出死锁的细节信息到SQL Error Log中(默认位置Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n)

2. 开启SQL Profiler.
Start SQL profiler
On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
Click the Events tab, only add Locks\Lock:deadlock and Locks\Lock:deadlock chain
Click the Data columns tab, add DatabaseID, IndexID, ObjectID
可以通过下面的语句把DatabaseID和ObjectID换成DatabaseName和ObjectName

SELECT database_name(DatabaseID)
SELECT object_name(ObjectID)

3. 使用下面的查询语句来检查那个进程被锁住了.

SELECT * FROM sys.sysprocesses WHERE blocked <> 0
从Blocked列中得到SPID
DBCC inputbuffer (SPID)
sp_who2
sp_lock2