create procedure sys.sp_replmonitorsynchronize
(
@mode int -- 0 - get lock_refreshcheckexclusive, 1 - get lock_refreshcheckshared, 2 - release lock_refreshcheck, 3 - get lock_compute, 4 - release lock_compute
)
with execute as 'dbo'
as
begin
set nocount on
declare @retcode int
,@lkmode nvarchar(32)
if (@mode in (0,1))
begin
-- get the lock for refresh check and table update
-- loop till you get it
while (1=1)
begin
select @lkmode = case when (@mode = 0) then 'Exclusive' else 'Shared' end
exec @retcode = sp_getapplock @Resource = 'replmonitorrefreshcheck'
,@LockMode = @lkmode
,@LockOwner = 'Session'
,@LockTimeout = '1000'
,@DbPrincipal = 'db_owner'
if (@retcode in (0,1))
return 0
if (@retcode < -1)
return 1
end
select @retcode = 1
end
else if (@mode = 2)
begin
-- release lock refresh check and table update
exec @retcode = sp_releaseapplock @Resource = 'replmonitorrefreshcheck'
,@LockOwner = 'Session'
,@DbPrincipal = 'db_owner'
if (@retcode != 0)
return 1
end
else if (@mode = 3)
begin
-- get the lock for compute instantly - no wait
exec @retcode = sp_getapplock @Resource = 'replmonitorcompute'
,@LockMode = 'Exclusive'
,@LockOwner = 'Session'
,@LockTimeout = 0
,@DbPrincipal = 'db_owner'
if (@retcode != 0)
return 1
end
else if (@mode = 4)
begin
-- release lock for compute
exec @retcode = sp_releaseapplock @Resource = 'replmonitorcompute'
,@LockOwner = 'Session'
,@DbPrincipal = 'db_owner'
if (@retcode != 0)
return 1
end
else
select @retcode = 1
-- all done
return @retcode
end