A
deadlock is a situation where in two transactions wait for each other
to give up their respective locks. Transaction A attempts to update
table 1 and subsequently read data from table 2, then transaction B
attempts to update table 2 and subsequently read data from table 1.
In such situations, transaction A holds locks that transaction B
needs to complete its task and vice versa; neither transaction can
complete until the other transaction releases locks. So in this
situation SQL server throws exception and kill the process and roll
back it for other process
how
to trace DeadLock in SQL Server
Below
administrative query will help you to find the DeadLock, If you click
on xml data you will get a dead lock report
SELECT
xed.value('@timestamp', 'datetime2(3)') as CreationDate,
xed.query('.') AS XEvent
FROM
(
SELECT CAST([target_data] AS XML) AS TargetData
FROM sys.dm_xe_session_targets AS st
INNER JOIN sys.dm_xe_sessions AS s
ON s.address = st.event_session_address
WHERE s.name = N'system_health'
AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY CreationDate DESC
