Sessions blocked in chain loop

Question

Sessions blocked in chain loop

askcoffman

Mr or Mrs. 500

Points: 580
More actions
January 17, 2019 at 2:11 pm

#376828

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

askcoffman - Thursday, January 17, 2019 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?

No - blocking is not the same as a deadlock.
Whatâ€™s the Difference Between Locking and Blocking and Deadlocking?

Sue

askcoffman Mr or Mrs. 500 Points: 580 More actions · Answer 2

askcoffman - Thursday, January 17, 2019 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?

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Sue_H - Thursday, January 17, 2019 3:20 PM
askcoffman - Thursday, January 17, 2019 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?
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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

askcoffman - Thursday, January 17, 2019 4:02 PM
askcoffman - Thursday, January 17, 2019 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?

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 5

askcoffman - Thursday, January 17, 2019 4:02 PM
askcoffman - Thursday, January 17, 2019 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?

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

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Sue_H - Friday, January 18, 2019 11:08 AM
askcoffman - Thursday, January 17, 2019 4:02 PM
askcoffman - Thursday, January 17, 2019 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?
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

askcoffman Mr or Mrs. 500 Points: 580 More actions · Answer 7

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.