Sessions blocked in chain loop

  • 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?

  • 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 - 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?

  • 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)

  • 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)

  • 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 - 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

  • 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