Post reply

Blocked Continuously by Different SPIDs

  • December 31, 2013 at 2:50 pm

    #281616

    Hi SSC,

    Question, periodically I've seen scenarios where a SPID (say SPID A) continuously gets blocked by various other SPIDs and never seems to get access to the resources it needs... waiting its turn forever it seems. Why is that? How is SQL determining who's next in line? Does it ever consider SPIDs by their age?

    If someone is willing to share a link or some context as to why this happens I'd appreciate it! You'd think that once the culprit blocker finished SPID A would get it's chance at the resources.

    Thanks and Happy New Year!

  • January 3, 2014 at 11:05 pm

    #1677488

    I think SPID A got low deadlock priority in this case. We can avoid by setting the deadlock priority high.

    If priority is high it never becomes the victim.

    Thanks,
    I’m nobody but still I’m somebody to someone………….

  • January 4, 2014 at 12:24 pm

    #1677545

    I would have to say that you've got a much larger problem at hand. I'd spend some time trying to figure out why those other SPIDs are causing so much blocking.

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

  • January 4, 2014 at 7:08 pm

    #1677567

    @Deadlock priority, I don't think this is a factor here... deadlocks aren't occurring at all but thank you for a response!

    @Blocking, I should've been more specific. The blocking does not occur normally. It only appeared when I tried to modify a column on the table that is heavily hit with updates (the nature of the table). The column mod was referred to as SPID A and was getting blocked by a different SPID every time I checked (because the previous head of the chain already finished).

    The chain was only ever SPID A being blocked by a single SPID. The next time I would check (as fast as I could it F5) SPID A would be blocked by a new SPID of the same update nature because the previous had already finished that fast. The moment I cancelled SPID A the 2 node chain of blocking would clear. There isn't a problem in my eyes per say, I more so just wanted to understand why this would happen. I was expecting SPID A would get it's chance at the resource (and that itself I expected to cause a good pinch of blocking given how active the table is).

    Lesson learned? Don't try touching a highly active table during heavy load. I ended up making the modification during a low activity time frame and all is well.

    Thanks everyone for the responses, I appreciate it.

Viewing 4 posts - 1 through 3 (of 3 total)

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