Select Query with S lock wins on Insert with IX lock in deadlock in Sql Server 2008R2.

Attaching the deadlock xml file.Please help!


This is from the error log by enabling deadlock trace flags

Also,I am using below query to get the deadlock report:

WITH SystemHealth 
AS ( 
SELECT CAST(target_data as xml) AS TargetData 
FROM sys.dm_xe_session_targets st 
JOIN sys.dm_xe_sessions s 
ON s.address = st.event_session_address 
WHERE name = 'system_health' 
AND st.target_name = 'ring_buffer') 

SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph 
FROM SystemHealth 
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent) 
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'

But it's taking long time to run and not giving any results even we have few deadlocks captured in error log. Some times, I get the result after so much of time. What I am missing here?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.