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.