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)') 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?