CODEWITHSUNDEEP
sql-server-2008stored-proceduresdeadlock
ManOnAMission
ManOnAMission

Reputation: 1043

Where can I SET DEADLOCK_PRIORITY in my stored procedure?

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!

Upvotes: 12

Views: 21552

Answers (1)

Pondlife
Pondlife

Reputation: 16260

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

Upvotes: 16

Related Questions