Deadlock victim in the UI

0 votes

For our database I have PowerShell scripts that automate updating tables with data it is pulling from other sources and applications. These mainly run overnight, but sometimes run over into the morning. What happens occasionally in this scenario is when browsing and reading records in the UI we might get an error that the data can't be retrieved because the transaction was deadlocked.

How can I change the deadlock priority of browsing and reading records in the UI so that it takes priority over the script updating fields? Or, if that can't be changed what is the default deadlock priority of a browse/read via the UI? Perhaps I can change the priority on the scripts? There are multiple scripts that run and there is logic built within them to detect deadlocks between them and retry, so if a record is being read and is set to a higher priority the script will retry that record again until successful and not fail.


in Database Connection by (350 points)

1 Answer

0 votes

dbFront uses the default DEADLOCK_PRIORITY in SQL Server which is NORMAL = 0.

Your option would be to run your scripts using a DEADLOCK_PRIORITY less then 0, such as -5 or even -10.

For more information you can see the Microsoft documentation at DEADLOCK_PRIORITY.

by (64.5k points)
Welcome to the dbFront Q&A site, where you can ask questions and receive answers from other members of the community.
 | Minimalist Answer Theme by Digitizor Media
Powered by Question2Answer