Deadlocking on a batch update job

[CarolineEH]

Hi,
In the environment I am using, I need to run update scripts while users are logged into the SQL box. There is a possibility that they could be updating tables, causing a deadlock when I attempt to run my script.
As my update scripts are part of a daily job and of a definite priority over any user transactions, is there any code that can be added to my scripts to ensure that when the victim is chosen, the user is always chosen (as opposed to my update scripts).
Up until now, it has not been an issue as the updates have occurred out of hours, but now there will be users logged in, in the middle of a batch update.
Any help would be greatly appreciated..

Thanks,
Caroline.

 

[JamesLean]

You could look at SET DEADLOCK_PRIORITY. However this can only set the priority low for a particular connection - not high, ie you would have to add it to all your user connections so that they were chosen as the victim in a deadlock situation.

Perhaps the better solution would be to revisit your code, both in your app and the batch update, to either reduce/eliminate the chances of a deadlock occuring or add code to your batch update to check for error 1205 (deadlock) and dealing with it (eg resubmitting the current update).

--James

 

[CarolineEH]

Thanks James - I think I'd be more inclined to address the error handling of error 1205 rather than SET DEADLOCK_PRIORITY. As it stands, I believe the current code should have minimal deadlock occurrences, but I want to implement something as a preventative measure going forward, to be on the safe side.
Thanks for your feedback...
Caroline.