Post reply

Deadlock priority not working

  • February 10, 2012 at 4:38 am

    #251352

    We have an issue of a dataload / partition switcher stored procedure deadlocking with another data read/ update stored procedure, and the former being chosen as the deadlock victim.

    But we want the latter to be chosen as the victim.

    So we set the former to DEADLOCK_PRIORITY = 5 and the latter to -1, but the former was still chosen as the victim.

    Why is this?

    (see Profiler deadlock graph clearly showing the deadlock priority values).

    I don't think I've got the order the wrong way round (have I?).

    What else, apart from the number, determines how SQL Server chooses its deadlock victim?

    The locks are schema locks on two tables.

    It is on SQL Server 2008 sp2

    http://90.212.51.111 domain

  • February 10, 2012 at 6:07 am

    #1445499

    neil-560592 (2/10/2012)

    What else, apart from the number, determines how SQL Server chooses its deadlock victim?

    Basically SQL Server looks for the process that would be easier to roll back. This often means the one that has modified less data.

    Instead of raising the deadlock priority, I would look for ways to avoid the deadlock at all. If acceptable, you could put an exclusive lock on the whole table while swithcing the partition.

    -- Gianluca Sartori

Viewing 2 posts - 1 through 1 (of 1 total)

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