January 17, 2019 at 2:11 pm
Im using spotlight to monitor our sql servers and am using it now to diagnose a blocking issue. We experienced a spike of about 500 queued sessions that were blocking in a loop. Example
spid 20 is blocking 21
21 blocking 22
22 blocking 20
All from the same application. If spotlight is giving me correct data, shouldn't this trigger a deadlock and kill one of the sessions?
I should add that each of these spids have transactions on the same table. It might not be a "deadlock" but doesnt it still pose a situation that will never finish?
January 17, 2019 at 3:20 pm
askcoffman - Thursday, January 17, 2019 2:11 PMIm using spotlight to monitor our sql servers and am using it now to diagnose a blocking issue. We experienced a spike of about 500 queued sessions that were blocking in a loop. Example
spid 20 is blocking 21
21 blocking 22
22 blocking 20
All from the same application. If spotlight is giving me correct data, shouldn't this trigger a deadlock and kill one of the sessions?
No - blocking is not the same as a deadlock.
What’s the Difference Between Locking and Blocking and Deadlocking?
Sue
January 17, 2019 at 4:02 pm
askcoffman - Thursday, January 17, 2019 2:11 PMIm using spotlight to monitor our sql servers and am using it now to diagnose a blocking issue. We experienced a spike of about 500 queued sessions that were blocking in a loop. Example
spid 20 is blocking 21
21 blocking 22
22 blocking 20
All from the same application. If spotlight is giving me correct data, shouldn't this trigger a deadlock and kill one of the sessions?
I should add that each of these spids have transactions on the same table. It might not be a "deadlock" but doesnt it still pose a situation that will never finish?
January 17, 2019 at 10:08 pm
Sue_H - Thursday, January 17, 2019 3:20 PMaskcoffman - Thursday, January 17, 2019 2:11 PMIm using spotlight to monitor our sql servers and am using it now to diagnose a blocking issue. We experienced a spike of about 500 queued sessions that were blocking in a loop. Example
spid 20 is blocking 21
21 blocking 22
22 blocking 20
All from the same application. If spotlight is giving me correct data, shouldn't this trigger a deadlock and kill one of the sessions?No - blocking is not the same as a deadlock.
What’s the Difference Between Locking and Blocking and Deadlocking?Sue
What you say is true. Blocking isn't the same as a deadlock.
However, if you look at each end of this blocking chain, you have a "loop" of blocking where the first item is responsible for the blocking chain and the last item (going by the order listed above) is blocking the first item in the chain. That's a deadlock even if it does look a bit strange. A blocks B and B blocks A is the normal form of a deadlock but A blocks B blocks C blocks A is also a deadlock.
SQL Server may have had some good bit of difficulty in what to choose as a deadlock victim to break the deadlock in this case.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2019 at 10:16 pm
askcoffman - Thursday, January 17, 2019 4:02 PMaskcoffman - Thursday, January 17, 2019 2:11 PMIm using spotlight to monitor our sql servers and am using it now to diagnose a blocking issue. We experienced a spike of about 500 queued sessions that were blocking in a loop. Example
spid 20 is blocking 21
21 blocking 22
22 blocking 20
All from the same application. If spotlight is giving me correct data, shouldn't this trigger a deadlock and kill one of the sessions?I should add that each of these spids have transactions on the same table. It might not be a "deadlock" but doesnt it still pose a situation that will never finish?
See my post above. I don't know why SQL Server couldn't choose a deadlock victim in this case but it IS a deadlock.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2019 at 11:08 am
askcoffman - Thursday, January 17, 2019 4:02 PMaskcoffman - Thursday, January 17, 2019 2:11 PMIm using spotlight to monitor our sql servers and am using it now to diagnose a blocking issue. We experienced a spike of about 500 queued sessions that were blocking in a loop. Example
spid 20 is blocking 21
21 blocking 22
22 blocking 20
All from the same application. If spotlight is giving me correct data, shouldn't this trigger a deadlock and kill one of the sessions?I should add that each of these spids have transactions on the same table. It might not be a "deadlock" but doesnt it still pose a situation that will never finish?
Did you check the waiting tasks? There is/was an issue that was on connect which I can no longer find since it moved to voices.
EXECSYNC waits won't show blocks in sys.dm_os_waiting_tasks (and deadlocks not detected). They are related to parallel tasks. It's worth checking to see if that is the case as there appears to be a bug or bugs related to that.
Sue
January 18, 2019 at 11:15 am
Sue_H - Friday, January 18, 2019 11:08 AMaskcoffman - Thursday, January 17, 2019 4:02 PMaskcoffman - Thursday, January 17, 2019 2:11 PMIm using spotlight to monitor our sql servers and am using it now to diagnose a blocking issue. We experienced a spike of about 500 queued sessions that were blocking in a loop. Example
spid 20 is blocking 21
21 blocking 22
22 blocking 20
All from the same application. If spotlight is giving me correct data, shouldn't this trigger a deadlock and kill one of the sessions?I should add that each of these spids have transactions on the same table. It might not be a "deadlock" but doesnt it still pose a situation that will never finish?
Did you check the waiting tasks? There is/was an issue that was on connect which I can no longer find since it moved to voices.
EXECSYNC waits won't show blocks in sys.dm_os_waiting_tasks (and deadlocks not detected). They are related to parallel tasks. It's worth checking to see if that is the case as there appears to be a bug or bugs related to that.Sue
And one other thing to check - I just ran across this while trying to find the posts about execsync and deadlocks -
Deadlock not detected when using MARS on SQL 2017
Sue
January 18, 2019 at 11:16 am
Thanks for all the replies. This scenario did cause deadlocks on the service, I just need to learn how to read dateTime correctly when reading the logs.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply