“deadlock victim” in transaction, how to change the priority?

4
votes

i have logged an exception thrown by an ASP.NET-Application.

Message: Transaction (Process ID 56) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I'm sure that the reason for it was, that i was running some selects directly in SSMS on tables that were queried in the application in the same time.

So now my question is:

  • Can i change the priority SQL-Server chooses "deadlock victims"? I would like to choose "myself" (SSMS) as deadlock victim and not ASP.NET, because it's a time-critical application and it's easier for me to rerun the manual query than for the users to accept an error.

Thank you in advance.

1
sql-servertsqldeadlockssmsdatabase-deadlocks

1 Answers

5
votes

SET DEADLOCK_PRIORITY allows you to fine tune the precedence

You can specify this in your SSMS session (per query window) or globally:

Tools..Options..etc