create procedure sys.sp_MSacquireHeadofQueueLock
@queue_timeout int = 0, -- means wait in definitely
@no_result bit = 0,
@return_immediately bit = 0, -- if 1, do not wait: return and
-- post a progress message.
@DbPrincipal sysname = NULL
declare @entry_date datetime
declare @delay_time int --in second
declare @retcode int
declare @max_waiting int
declare @lock_acquired bit
-- Security Checking
-- sysadmin or db_owner or PAL user of some publication have access
exec @retcode = sys.sp_MSrepl_PAL_rolecheck
if (@retcode <> 0) or (@@error <> 0)
RAISERROR (14126, 11, -1)
raiserror(21344, 16, -1, '@queue_timeout')
if @queue_timeout> 3600*12 -- more than 12 hours is not allowed:
-- make it 0 in this case.
raiserror(21417, 16, -1)
if @DbPrincipal is NULL
if exists (select * from sys.database_principals where name=N'MSmerge_PAL_role' and type = 'R')
select @DbPrincipal = N'MSmerge_PAL_role'
select @DbPrincipal = N'db_owner'
--quick peek and leave
--take a peek: return without wait regardless of whether the lock
--is successfully acquired.
exec @retcode=sys.sp_getapplock @Resource=@process_name,
@DbPrincipal = @DbPrincipal
if (@retcode <> 0 AND @retcode <> -1)
IF (@retcode = 0) -- AppLock acquired
select 1, 0 -- 1 means lock acquired, 0 is the time
-- consumed to acquired this lock.
else if @retcode = -1 -- AppLock is not available
select 0, 0 -- first 0 means lock not available,
-- second column will be ignored.
return (0) -- OK
-- from now on, @return_immediately=0
-- if @queue_timeout=0, means waiting indefinitely
select @max_waiting = NULL
--otherwise convert to minisecond and pass it down.
select @max_waiting = @queue_timeout * 1000
-- First try to acquire EXCLUSIVE lock which signfies at front of queue.
-- i.e. I'm next.
exec @retcode=sys.sp_getapplock @process_name, @LockMode=N'Exclusive',
@LockOwner=N'Session', @LockTimeout=@max_waiting, @DbPrincipal=@DbPrincipal
-- We shouldn't return from above until we have it
-- RC should be either 0 (got immediately) or 1 (waited and got it eventually) or -1 (timed out); Exit on anything else.
-- We need value -1 because @LockTimeout value is no longer NULL,
-- meaning waiting indefinitely. It is possible
-- that we waited for a given length of time and timed out
IF (@retcode <> 0 and @retcode <> 1) and @retcode<>-1
RAISERROR(21413, 16, -1)
-- Im-first lock is to be released after successfully
-- obtained a slot lock later.
select @delay_time=datediff(ss, @entry_date, getdate())
if @no_result = 0
select @lock_acquired, @delay_time