sp_getapplock without transaction

Question

I'm implementing stored procedure that won't have transactions inside. Actually, it will, but only on specific spots to reduce time to minimum. Nature of stored procedure is so I only want one runinng at a time.

I tried to use sp_getapplock but quickly found that it needs to be inside transaction.

Is there any other alternative where I can place lock on whole procedure but without wrapping it into transaction?

Answer 1

pass in Session via @LockOwner to get a Session-scope lock; this does not need a transaction to be alive when lock is taken.

For example

EXEC @res = sp_getapplock @Resource = 'Lock ID', @LockOwner = 'Session', @LockMode = 'Exclusive';
..
code
..

EXEC @res = sp_releaseapplock @Resource = 'Lock ID';