CODEWITHSUNDEEP
sql-serverdatabasedeadlock
Liton Uddin
Liton Uddin

Reputation: 85

Avoid becoming victim of database deadlock

Everyday day I run a sql server script to update my table. Once a week I become a victim of a database deadlock. Several people have access to the database and they all know they are not supposed to access the database while it is updating. But someone is accessing the database during the update and we have deadlock. Is there a way I can avoid becoming a victim of deadlock? I’ve heard of deadlock priority. Will it help if I set the deadlock priority to 10? Are there any performance issue or consequences for using deadlock priority?

Upvotes: 1

Views: 685

Answers (2)

Raj
Raj

Reputation: 10843

Several people have access to the database and they all know they are not supposed to access the database while it is updating

Why dont you just put the DB in Single User, do your update and the revert to multi user in one single transaction?

Raj

Upvotes: 0

Eric J.
Eric J.

Reputation: 150108

Assuming the other processes have a deadlock priority less than 10, this should cause you to win the deadlock resolution.

If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim.

http://msdn.microsoft.com/en-us/library/ms186736.aspx

Of course you could always run a script removing update rights from other users for the duration of your update...

Upvotes: 1

Related Questions