SET DEADLOCK_PRIORITY

Oct 3, 2006

Is this command imperative for MS SQL server?

I have two concurrent threads which can be deadlocked. One thread is background and I prefer to set it as deadlock victim (in case of deadlock) using SET DEADLOCK_PRIORITY LOW.

Unfortunately, sometimes main thread is selected to be victim.

Why?

Quote from SQL Server books:

"...LOW - Specifies that the current session is the preferred deadlock victim. The deadlock victim's transaction is automatically rolled back by Microsoft� SQL Server��, and the deadlock error message 1205 is returned to the client application..."

Does "session" mean connection or transaction? i.e. Does SET DEADLOCK_PRIORITY change priority until transaction finished/connection closed or its scope only following statement?

View 1 Replies

SQL 2000 TSQL Example Required To Set DEADLOCK_PRIORITY To 10

Mar 7, 2008

I have some deadlocks in defragging indexes in a sql 2000 db
the defrag runs late @ night but the conflict is with a 24/7 app (email app)
MY SQL job which calls an sp to defrag dbs get to be the dead locked victim (dooh)
So I thought that I would: set SET DEADLOCK_PRIORITY '10' but i get SET DEADLOCK_PRIORITY option '10' is invalid
This works in sql 2005 but not 2000. I can't set the apps connection to low.
Any rate would any one know a TSQL 2000 example of setting the deadlock priority to 10 ?
Please.

View 1 Replies

View Related