March 21, 2016 at 4:53 am
[font="Courier New"][/font]
I get the following output from sys.dm_exec_requests:
SELECT
session_id,
blocking_session_id,
wait_type,
wait_time,
status
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
session_idblocking_session_id wait_type wait_time status
73 130 LCK_M_S 2699 suspended
130 73 LCK_M_S 2700 suspended
These 6 columns below are from sp_who2:
SPID Status Login BlkBy DBName
73 SUSPENDED BOBJAUD 130 BOBJAUD
130 SUSPENDED BOBJAUD 73 BOBJAUD
-- Waiter statement
SELECT LOCKTABLE.PARENTID,LOCKTABLE.CHILDID,0,LOCKTABLE.ISMEMBER FROM dbo.CMS_LOCKS7 LOCKTABLE WHERE LOCKTABLE.CHILDID IN (6108987)
-- Blocker statement
SELECT LOCKTABLE.PARENTID,LOCKTABLE.CHILDID,0,LOCKTABLE.ISMEMBER FROM dbo.CMS_LOCKS7 LOCKTABLE WHERE LOCKTABLE.CHILDID IN (4323)
How should I interpret this?
I always thought that there is a blocker and a waiter
and I may need to kill the blocker.
How can the blocker be the waiter and the waiter be the blocker?
Which session above is at the top of the blocker tree?
Which session should I kill?
March 21, 2016 at 6:41 am
I suspect you have something else going on there. Those statements are SELECT statements. They're not going to be blocking in this fashion. Instead, there must be something taking out locks such that these statements are blocking. Plus, if it really was a deadly embrace, each blocking the other, it would resolve as a deadlock, one would be chosen as the victim, and be rolled back.
Try using sp_whoisactive to get the blocking chain. It provides much better information.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 21, 2016 at 9:35 am
To pile on to what Grant said, yeah, there's probably something else going on there. Those queries,table names, and deadlock issues are eerily familiar, so I'm guessing the "something else" is that implicit transactions are being used, and you're getting a common writer-reader deadlock, with each session owning an exclusive lock from an earlier statement in the transaction, and those exclusive locks are blocking their attempts at acquiring a shared lock for the SELECT.
The wait times are below 5 seconds, which I seem to recall is how often the thread that monitors for deadlocks wakes up, so most likely you were just seeing a deadlock about to happen.
You can also use the following query to check for deadlocks that have occurred and are still in the system_health XEvents session:
SELECT OccurredAt,CAST(event_data.value('(event/data/value)[1]',
'varchar(max)') AS XML) AS DeadlockGraph
FROM ( SELECT Xevent.value('@timestamp','datetime') as OccurredAt,XEvent.query('.') AS event_data
FROM ( -- Cast the target_data to XML
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 target_name = 'ring_buffer'
) AS Data -- Split out the Event Nodes
CROSS APPLY TargetData.nodes('RingBufferTarget/
event[@name="xml_deadlock_report"]')
AS XEventData ( XEvent )
) AS tab ( OccurredAt,event_data )
order by OccurredAt desc
That will give you a bit more insight into what's going on, and you can focus your investigation from there.
Cheers!
March 21, 2016 at 10:08 am
Grant,
Thanks for the reply - conceptually helpful.
March 21, 2016 at 10:10 am
Jacob,
Very helpful - thanks for the additional scripts to assist in the analysis.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply