How to troubleshoot SQL deadlock errors?

What is a deadlock?

====================

A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. for example, Andrew holds a lock on table A and requests a lock on table B; Lindsay holds a lock on table B and requests a lock on table.

 

What can lead to deadlocks?

====================

1. Locks
2. Worker threads
3. Memory
4. Parallel query execution-related resources
5. Multiple Active Result Sets (MARS) resources.

How to troubleshoot deadlocks?

====================

1. Enable the deadlock log output (deadlock trace)

 

    DBCC TRACEON(1204,1222) 

 

Trace flag1204Reports deadlock information formatted by each node involved in the deadlock.

Trace flag1222Formats deadlock information, first by processes and then by resources.

 

After the preceding options are enabled, SQL will output the deadlock details to the SQL Error Log (default position: Program Files \ Microsoft SQL Server \ MSSQL. n \ MSSQL \ LOG \ ERRORLOG and ERRORLOG. n)

 

2. enable SQL Profiler.

1. Start SQL profiler
2. On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
3. Click the Events tab, only addLocks \ Lock: deadlockAndLocks \ Lock: deadlock chain
4. Click the Data columns tab, addDatabaseID, IndexID, ObjectID

You can use the following statement to replace DatabaseID and ObjectID with DatabaseName and ObjectName.

 

           SELECT database_name(DatabaseID)           SELECT object_name(ObjectID)

 

3. Use the following query statement to check that the process is locked.

 

SELECT * FROM sys. sysprocesses WHERE blocked <> 0 get spid dbcc inputbuffer (SPID) sp_who2 sp_lock2 FROM the Blocked Column

References:

SQL: How to debug SQL deadlocks

Http://www.dalun.com/blogs/10.13.2006.htm

SQL Server technical bulletin-How to resolve a deadlock

Http://support.microsoft.com /? Kbid = 832524

Detecting and Ending Deadlocks

Http://msdn.microsoft.com/en-us/library/ms178104.aspx