Deadlock happens when 2 processes are trying to update a resource in reverse logical order.

SQL Server then decides based on the cpu/memory etc., consumed and chooses one as deadlock victim.

HOW TO RESOLVE DEADLOCKS

— enable trace flag to identify deadlocks to avoid in future

DBCC TRACEON (1204)

It is written to SQL server error log when a deadlock does happen.

Once you get SPID information from the errorlog, find for the spid from sysprocesses table

[cc lang=”SQL”]
SELECT id, name FROM sysobjects WHERE xtype = ‘U’
[/cc]

or use
select object_name (spid number from above)

— Then we get information about the tables involved in the deadlock.
— But still struck with knowing which SQL statements caused it.

To find it, we need to do server side trace.

— Capture the events Lock Events Lock:Deadlock and Lock:Deadlock Chain
— Capture object ID data column