Dead Lock In SQL Server

     

     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