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


No comments:

Post a comment