Setting DEADLOCK_PRIORITY with sp_$ndo$loginproc

Business Central/NAV Technical Forum
1

Hi all!

Well, in a perfect world we would never encounter deadlocks with NAV, but unfortunately things are not that perfect … so we will always have deadlocks, sometimes more, sometimes less. It is quite annoying, when a DL occurs and some unattended process, e.g. a NAS or NAV Job Scheduler, is killed, because these processes hardly could resume [:@]

I just found a way to - at least - define the DEADLOCK_PRIORITY for such processes with NAV, using the stored procedure “sp_$ndo$loginproc” (see “Application Designer’s Guide” for details).

The procedure sets the DEADLOCK_PRIORITY of a NAS higher than a normal Client; thus in case of a DL always the client will be chosen as “victim” and killed, not the NAS: [I]

//
/ STRYK System Improvement /
/ Performance Optimization & Troubleshooting /
/ (c) 2007, STRYK System Improvement, Jörg Stryk /
/ http://www.stryk.info/ /
//

if exists (select [name] from sysobjects where [name] = ‘sp_$ndo$loginproc’ and [type] = ‘P’)
drop procedure [sp_$ndo$loginproc]
go

create procedure [sp_$ndo$loginproc]
@appname varchar(64) = NULL,
@appversion varchar(16) = NULL
as
begin

if charindex(‘2000’, @@version) > 0 begin – SQL Server 2000
if @appname like ‘%[Aa]pplication%’ begin
set deadlock_priority normal
set lock_timeout -1
end
if @appname like ‘%[Cc]lient%’
set deadlock_priority low
end

if charindex(‘2005’, @@version) > 0 begin – SQL Server 2005
if @appname like ‘%[Aa]pplication%’ begin
set deadlock_priority high
set lock_timeout -1
end
if @appname like ‘%[Cc]lient%’
set deadlock_priority low
end

end
go

grant execute on [sp_$ndo$loginproc] to [public]
go

Of course, this procedure could be designed in any way to handle specific users/logins and could do MUCH MORE - “the limit is our imagination” [;)]

Hope it could help you as well! I appreciate to get your comments!

Best regards,

Jörg

2

Hello,

I deployed this proc in our test environment and we re-started our NAS service. I then queried dm_exec_sessions and it still shows the deadlock_priority as 0 for our NASQuery login. I the ran a trace while re-starting the service and noticed that the call to the proc was using the Classic Client for the @appname. So, I modified the proc to set the deadlock priority for a specific login. I ran a trace while re-starting the service and saw the statement being executed that set the deadlock_priority to high. I then queried dm_exec_sessions again and it still shows the deadlock_priority as 0 for our NASQuery login. I was wondering how you have verified that this setting is retained for every transaction submitted by the affected login. Thanks.

3

Hi Charles,

I sorry to have to admit this, but this isn’t working. It was a try, and the “success” some brief tests have shown was pure coincidence … [:$] (obviously I forgot to post this in this thread).

Reason is: it cannot work. If the procedure SETs the DEADLOCK_PRIOIRITY this flag is only valid in the context of the procedure execution. Once the sp has finished, all is set back to normal …

So far I have not found a way to permanetly adjust this setting for a process … sorry …

4

OK. Have you ever used or seen anybody use the C/SIDE LOCKTIMEOUT function to override the default lock timeout setting in a SQL Server environment? I saw it mentioned here. Thanks.

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

5

Yes, I used this, for example, with some unattended NAV processes executed by Job SCheduler or NAS, putting in some code like …

LOCKTIMEOUT(GUIALLOWED);

Hence, the Lock-Timeout is disabled when a process is run by NAS. This means, if the NAS gets blocked the processing won’t be cancelled after the Lock-Timeout-Period, the NAS will wait as long as it takes …

6

I am new to the forum so apologies if I sound brash, but I would suggest striving to understand the root cause of the deadlock.

7

Hi Jorg,

Can we set priority for a particular user using this procedure.

Regards

Sha

8

Hi,

as mentioned before - this does not work with NAV … sorry …

Regards,
Jörg

9

Hi Peter,

welcome to the Dynamics User Group [<:o)]

Sure, I absolutely agree. Problem is, that there are so many potentially conflicting processes, thus, it is impossible to solve many of those blocking/deadlocking issues.
Hence, the idea is, IF there are deadlocks to have at least some control about them, e.g. when it’s NAS versus Client etc. …

See also
http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx
http://dynamicsuser.net/blogs/stryk/archive/2010/05/19/decisions-spring-2010-nav-sql-performance-blocks-and-deadlocks.aspx

Cheers,
Jörg