2

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

Attaching the deadlock xml file.Please help!

http://pastebin.com/nY9EXbS8

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.