Sp_getapplock without transaction

Question

I am implementing a stored procedure in which there will be no transactions inside. In fact, it will be, but only in specific places, to reduce time to a minimum. The nature of the stored procedure is that I want only one run at a time.

I tried using sp_getapplock, but quickly found that it must be inside a transaction.

Is there any other alternative where I can place a lock on the whole procedure, but without transferring it to the transaction?

Answer 1

pass the session through @LockOwner to get a session lock; this does not require the transaction to be alive when a lock is made.

for instance

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