Where can I set DEADLOCK_PRIORITY in my stored procedure?

I'm not sure where I can use

SET DEADLOCK_PRIORITY... 

in my stored procedure. Should it be before I start a transaction? Or could it be anywhere in the transaction?

Thanks everyone!

+4
source share
1 answer

I'm not sure I understand the question: you can change the priority where you need it. If you are not sure, you can simply put it at the beginning of the procedure, if perhaps it is a very long procedure, and there is only one specific request that is prone to deadlocks.

Although a better solution is likely to avoid a dead end , if possible.

You may also notice that any change in priority within the reset stored procedure corresponds to the priority of the calling session when the procedure ends:

 set deadlock_priority high go select deadlock_priority from sys.dm_exec_sessions where session_id = @@spid go create proc dbo.p as begin select deadlock_priority as 'PriorityBefore' from sys.dm_exec_sessions where session_id = @@spid set deadlock_priority low select deadlock_priority as 'PriorityAfter' from sys.dm_exec_sessions where session_id = @@spid end go exec dbo.p select deadlock_priority from sys.dm_exec_sessions where session_id = @@spid drop proc dbo.p go set deadlock_priority normal go 
+7
source

Source: https://habr.com/ru/post/1481252/


All Articles