December 1, 2011 at 10:54 am
I'm working on adding some functionality to an existing database, and I'm running into problems with deadlocks. The main Application that uses this DB is blowing up occasionally when it collides with any of my processing, and ... that's bad. I cannot work on the App itself, only with my own code. So I need my new code to always selected as the deadlock victim and not the main App. I believe "SET DEADLOCK_PRIORITY LOW" should accomplish that, I just haven't been able to find enough useful information on how/when/where to set that option.
My code is being run as a scheduled job. The Job has 2 steps: the main processing and and failure-over routine. Each step is a stored proc call, and both procs have some DML and calls to various other procs and functions.
So my question is: Where do I set the Deadlock priority? Should it be a separate (1st) step in the Job? Will that cover all of the subsequent steps? Or does it need to be specified within the main stored proc for each of the Job steps? And if specified there, does it apply to all the lower-level calls made?
Thanks!
December 1, 2011 at 10:56 am
That'd be a last resort option. Usually you can remove that issue with better indexes, proper isolation levels and faster code.
Then if you want to be safer your option could be added.
December 1, 2011 at 10:59 am
Most of the time, deadlocks can be avoided by writing code to take locks in the same sequence. If you can't ensure that, then deadlock priority low should do it.
It needs to be set in the session, per MSDN. That means set it inside the stored procedure or script that you're running.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply