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
0 Comments.