Deadlock

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    I had to do a little research. I definitely learned something useful. Thanks

  • Dana Medley

    SSCertifiable

    Points: 6764

    Great Question. That one took me back a ways in the memory banks. I thought I remembered, and I remembered right.:-)



    Everything is awesome!

  • jts2013

    Hall of Fame

    Points: 3226

    Daniel Bowlin (2/8/2012)


    I had to do a little research. I definitely learned something useful. Thanks

    Same here, so good question. Thanks.

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3944

    tks for the question

  • Rob Schripsema

    SSCertifiable

    Points: 7469

    Great question. Wasn't very familiar with this, and I think it will help me solve a nagging problem we've been having....

    Rob Schripsema
    Propack, Inc.

  • Ken Wymore

    SSCoach

    Points: 16391

    Thanks for the question

  • Lon-860191

    SSCrazy

    Points: 2449

    Good question, thanks.

  • tilew-948340

    Hall of Fame

    Points: 3431

    jts_2003 (2/8/2012)


    Daniel Bowlin (2/8/2012)


    I had to do a little research. I definitely learned something useful. Thanks

    Same here, so good question. Thanks.

    +1 Still no sure to understand it perfectly, but ya... thanks!

  • TomThomson

    SSC Guru

    Points: 104772

    Thanks for the nice question.

    I wonder what SQL Server does about victim selection when all processes involved have the same deadlock priority (which will be most of the time, I imagine).

    Tom

  • Revenant

    SSC-Forever

    Points: 42467

    This was a simple one - thanks!

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Good question. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • Simon Liddle

    SSCrazy

    Points: 2573

    L' Eomot Inversé (2/9/2012)


    I wonder what SQL Server does about victim selection when all processes involved have the same deadlock priority (which will be most of the time, I imagine).

    It estimates which of the processes will be less expensive to roll back and selects that one as the victim (it's explained in the first bullet point in the MSDN article linked in the answer). 🙂

  • TomThomson

    SSC Guru

    Points: 104772

    Simon Liddle (2/13/2012)


    L' Eomot Inversé (2/9/2012)


    I wonder what SQL Server does about victim selection when all processes involved have the same deadlock priority (which will be most of the time, I imagine).

    It estimates which of the processes will be less expensive to roll back and selects that one as the victim (it's explained in the first bullet point in the MSDN article linked in the answer). 🙂

    But that's a pretty content-free explanation! 🙁

    Everyone :crazy: knows that the idea is to minimise cost, and that most :unsure: systems make some sort of stab at doing that ("everybody"?? and most ??? that's me being disgustingly optimistic again, twice in one sentence :hehe:).

    The question is what that estimate of cost is based on. One popular way of estimating is to look at which transaction in the loop was the last to start; another is to select the last to tale a lock, which may not be equivalent; yet another the last to take a lock that it still holds, which in several of MS's isolation levels is yet a third story; yet another variation is to hand each process a token when it starts a transaction, and invalidate the token when the transaction is committed or is rolled back for any reason other than as a deadlock victim, and provide a transaction start-again command which takes a token as a parameter, and then victimise the one with the newest token; that last has a variant in which a token is also invalidated if too much time passes during which it doesn't belong to a transaction; then there are bvariants based on how much IO the transaction has already used, or how much mill, or how much main-store occupancy, or some waited combination of these three, or that combined with infomation about start (whether lock related or tokenised or not). All those algorithms were in use or at least in the literatuire when I was working on deadlock detection a couple of decades ago, and I imagine thiongs have moved on now. It would be nice to know what algorithm (or at least what class of algorithm) MS uses, and the article referenced says precisely nothing :angry:, zilch, or squat about that. Too difficult for their customers, a question like that, I guess.

    Tom

  • Paul White

    SSC Guru

    Points: 150442

    L' Eomot Inversé (2/13/2012)


    But that's a pretty content-free explanation!

    My recollection is that it is the process that has generated least log that is selected as the victim.

  • TomThomson

    SSC Guru

    Points: 104772

    SQL Kiwi (2/13/2012)


    L' Eomot Inversé (2/13/2012)


    But that's a pretty content-free explanation!

    My recollection is that it is the process that has generated least log that is selected as the victim.

    Thanks Paul. Actually that looks like a pretty good choice - reassuring.

    Tom

Viewing 15 posts - 16 through 30 (of 30 total)

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