Post reply

SET DEADLOCK PRIORITY LOW question

  • June 8, 2020 at 2:02 pm

    #3761313

    Hey all,

    Session 1 = entity framework, as of not can't change anything about it

    Session 2 = stored proc, can control it

    Session 1 and 2 are deadlocking. I want 1 to be the deadlock victim. If I set 2 to have a deadlock priority LOW, will this cause 1 to be the deadlock victim?

    Thanks

  • June 8, 2020 at 4:01 pm

    #3761359

    Who'd have thought you'd get deadlocks with Entity Framework?!

    It's documented here.  You want to set deadlock priority for HIGH for number 2 if you don't want it to be the victim.  I think you can go even more granular than that as well these days, with a scale of 1 to 10.

    John

  • June 8, 2020 at 4:23 pm

    #3761370

    Thanks. When I look at the call from EF, it does show it has a taskpriority="0".

    I thought the HIGHER number would be the victim, I have it reversed?

    So setting the procedure to "HIGH" (assuming that means > 5), it should NOT be killed and EF with a 0 will be killed?

    Thanks again!

  • June 8, 2020 at 4:37 pm

    #3761374

    Yes, higher numbers are better. FWIW, I'd avoid low/normal/high and choose some numbers. I would set a general rule of stuff that is easily killed, put at -5, assume most at 0 (default), and high at 5. Then if you have really important work, set to 6 or 8.

    In general, we want to leave spaces in case we run into something we need to slot in there, but we also want to keep things really, really simple.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-deadlock-priority-transact-sql?view=sql-server-ver15

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

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