SQL Server : distributed locks how to check

Question

I'm trying to figure out how to query SQL Server if there is a lock with give name, aka is there a lock.

Is this correct, as it seems overly complicated?

Running

begin tran

print 'APPLOCK_TEST=' + convert(varchar(max), APPLOCK_TEST('public', 'testlock', 'Exclusive', 'Session'))
print 'APPLOCK_MODE=' + APPLOCK_MODE('public', 'testlock', 'Session')

declare @lock int
exec @lock = sp_getapplock @Resource = 'testlock', @Lockmode = 'Exclusive', @LockOwner = 'Session'
print(@lock)

print 'APPLOCK_TEST=' + convert(varchar(max), APPLOCK_TEST('public', 'testlock', 'Exclusive', 'Session'))
print 'APPLOCK_MODE=' + APPLOCK_MODE('public', 'testlock', 'Session')

returns

APPLOCK_TEST=1
APPLOCK_MODE=NoLock
0
APPLOCK_TEST=1
APPLOCK_MODE=Exclusive

Running this in another tab:

begin tran

  print 'APPLOCK_TEST=' + convert(varchar(max), APPLOCK_TEST('public', 'testlock', 'Exclusive', 'Session'))
  print 'APPLOCK_MODE=' + APPLOCK_MODE('public', 'testlock', 'Session')

returns

APPLOCK_TEST=0
APPLOCK_MODE=NoLock

So is the way to check if a lock is held?

Looks like a weird way to do such a check.

//APPLOCK_TEST remains 1 when the session is the session who has the lock
//otherwise this is 0
//APPLOCK_MODE is "NoLock" when a different session has the lock
//otherwise if its the same then APPLOCK_MODE = "Exclusive" 
if(APPLOCK_TEST == 0 or APPLOCK_MODE != 'NoLock')
{
   //there currently is a lock
}
else
{
   //there is no lock.
}

I have been looking at

https://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/ https://github.com/madelson/DistributedLock/blob/5bd69af3f151710825f43ea1379a619a1b63ca1a/DistributedLock.SqlServer/SqlApplicationLock.cs#L164 https://www.endyourif.com/preventing-race-conditions-with-sp_getapplock/

and more..

Please assist with what SQL Server would be for checking if there currently is a lock, regardless of the connection used.

You haven't committed the first query's transaction so what did you expect? — Charlieface, Oct 27 '21 at 08:38
@Charlieface please show me the code, and ill try it as when i do its exactly the same result.... which also what i thought it would be, but thought i would check... — Seabizkit, Oct 27 '21 at 09:34
`COMMIT TRAN;` at the end of the first script, before running the second one — Charlieface, Oct 27 '21 at 09:41
I think your question is how to determine if the current session already has an identical lock on the specified resource. The transaction with a session lock is superfluous. — Dan Guzman, Oct 27 '21 at 09:42

score 1 · Answer 1 · answered Oct 27 '21 at 10:52

A session lock owner is not transaction scoped so the transaction is superfluous.

Using APPLOCK_TEST and APPLOCK_MODE, the T-SQL below will determine if the Exclusive lock is held by any session, including the current session:

PRINT
    CASE WHEN 
        APPLOCK_TEST('public', 'testlock', 'Exclusive', 'Session') = 0 --held by other session and cannot be granted
        OR APPLOCK_MODE('public', 'testlock', 'Session') = 'Exclusive' --this session already holds Exclusive lock
        THEN 'Lock currently exists'
        ELSE 'No lock currently exists'
    END;

Alternatively, you could evaluate only APPLOCK_MODE (to avoid incrementing the lock reference count for the current session) and then acquire the lock with a zero timeout (to identify it's held by other sessions and not block). I suggest this approach if your intent is to immediately acquire the exclusive lock and never block.

DECLARE @lock int = 0;
IF APPLOCK_MODE('public', 'testlock', 'Session') <> 'Exclusive' --this session does not already hold Exclusive lock
BEGIN
    EXEC @lock = sp_getapplock @Resource = 'testlock', @Lockmode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 0;
    PRINT
        CASE @lock
            WHEN    0 THEN 'lock was successfully granted synchronously.'
            WHEN    1 THEN 'The lock was granted successfully after waiting for other incompatible locks to be released.'
            WHEN   -1 THEN 'The lock request timed out.'
            WHEN   -2 THEN 'The lock request was canceled.'
            WHEN   -3 THEN 'The lock request was chosen as a deadlock victim.'
            WHEN -999 THEN 'Indicates a parameter validation or other call error.'
        END;
END
ELSE
BEGIN
    PRINT 'Current session already has Exclusive lock';
END;

;

SQL Server : distributed locks how to check

1 Answers1