SET DEADLOCK_PRIORITY

[CarolineEH]

This may seem like a silly question, but it follows on from a question I had yesterday around deadlocks.
I'm doing a bit of research to find out the best way to avoid disastrous deadlocks on a batch update job that will be run daily while multiple users are accessing the database concurrently.
In the research I've been doing, this set deadlock_priority option has been winning as the option to implement in my user procedures.
However, the question arises: am I right in assuming that if I set a deadlock_priority as low on a procedure, and two or more users use this procedure to update a table, will SQL Server act like it does with the default setting, and just roll back the transaction that takes less processing to roll back, or would SQL Server return an error to all users?
Thanks in advance,
Caroline.

 

[hmckillop]

Are you saying that if different users access the same procedure that it currently deadlocks?

However, the question arises: am I right in assuming that if I set a deadlock_priority as low on a procedure, and two or more users use this procedure to update a table will SQL Server act like it does with the default setting, and just roll back the transaction that takes less processing to roll back,

I dont think I have seen your earlier post, but I think you need to address the issues of the deadlock in preference to using the Deadlock priority. As you mention SQL will always take control of deadlocks and can override the DEADLOCK PRIORITY as its the preferred choice (and not the definitive one).

I have spent months and months on performance tuning large database applications and in most cases with careful stored procedure changes and management of which resources will be accessed and in what order. Shortening lock times and transaction lengths etc, no doubt these have been posted before.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[CarolineEH]

Thanks for your reply hmckillop.
The only thing is, the procedures I am running are part of a batch update job, which are run out of office hours at the moment and do not experience deadlocks.
Now I'm in the situation where I am going to have to run the batch update while users are logged on and I want to implement a preventative measure to ensure my batch job is never the victim of a possible deadlock.
The procedures the users are running are kicked off from a front end application and there is no way of determining if a user is going to attempt updating a table that my batch update job wants to update.
I don't see how I can improve my procedures, as the procedures kicked off by users on the front end application are completely separate.
Maybe I'm missing something, as I'm only in the SQL world the last 7 months.
I really appreciate your help with this...

 

[hmckillop]

You could serialise the process by puttings on transaction isolation level as serialisable and then also locking all the resources the batch jobs requires. This should then prevent anyone from preventing the batch job from accessing the resources until it has finished.
To set the isolation level use
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Then if required access all the resources you require by taking out tablockx (or whatever level of locking you require). This should prevent other procedures from updating the data and thus deadlocking your batch job.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[CarolineEH]

Brilliant... will definitely try that.
Thanks for your help...

 

[JamesLean]

The procedures the users are running are kicked off from a front end application and there is no way of determining if a user is going to attempt updating a table that my batch update job wants to update

Just to check, are you aware of the difference between blocking and deadlocking? Blocking is where one process acquires a lock on an object that a second process then needs to use. The second process must wait until the lock is released, but this will not cause the second process to fail - it will just be delayed while it waits for it's lock. Blocking is quite normal in a transactional database.

A deadlock is a specific situation where the first process has a lock on one object and the second process has a lock on a second object. The first process then needs to use the second object, and so has to wait for the second process to drop its lock, but at the same time the second process needs to use the first object so is waiting for the first process to drop its lock. Each process is waiting for the other to drop its lock so a stalemate occurs. SQL Server detects this deadlock and chooses one of the processes as a deadlock victim, causing it to fail.

Deadlocks can usually be avoided by following certain best practices. Look up "minimizing deadlocks" in BOL for more info.

--James