CODEWITHSUNDEEP
sql-servert-sqllockingsql-server-2008-r2
katit
katit

Reputation: 17915

sp_getapplock without transaction

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?

Upvotes: 9

Views: 7276

Answers (1)

iruvar
iruvar

Reputation: 23384

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', @LockOwner = 'Session';

Upvotes: 16

Related Questions