Menu
I am unsure as to where I can use the
SET DEADLOCK_PRIORITY...
command in my stored procedure. Does it need to be before I begin a transaction? Or can it be anywhere in the transaction?
Thanks all!
744
SET DEADLOCK_PRIORITY is set at execute or run time and not at parse time. This syntax is not supported in dedicated SQL pools in Azure Synapse Analytics. It is supported in serverless SQL pools in Azure Synapse Analytics.
If the priority of all the transactions involved in the deadlock is same, the transaction that is least expensive is chosen as deadlock victim. In the unlikely event that both the deadlock priority and the cost of the transactions involved in the deadlock are equal, then deadlock victim will be selected randomly.
Update lock (U) is used to avoid deadlocks. Unlike the Exclusive lock, the Update lock places a Shared lock on a resource that already has another shared lock on it.
I'm not sure I understand the question: you can change the priority wherever you need to. If you're unsure then you can just put it at the start of the procedure, unless perhaps it's a very long procedure and there's only one specific query that is prone to deadlocks.
Although a better solution would probably be to avoid the deadlock if possible.
You may also want to note that any priority change inside a stored procedure is reset to the priority of the calling session when the procedure exits:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
