CODEWITHSUNDEEP
sql-servert-sqllocking
zernager
zernager

Reputation: 54

sp_GetAppLock without transaction in SQL Server 2019

The code in irunvar's answer here no longer works in SQL Server 2019. When I run this code (and only this code)

EXEC sp_getapplock @Resource = 'Lock ID', @LockOwner = 'Session', @LockMode = 'Exclusive';
print 'omg' 
EXEC sp_releaseapplock @Resource = 'Lock ID';

I get

The statement or function must be executed in the context of a user transaction.

Any ideas how I get an applock without a transaction these days?

Upvotes: 0

Views: 263

Answers (1)

HABO
HABO

Reputation: 15852

In order to match the lock and release you must specify the same @LockOwner for both operations:

declare @Status as Int;
EXEC @Status = sp_getapplock @Resource = 'Lock ID', @LockOwner = 'Session', @LockMode = 'Exclusive';
print @Status;
print 'omg';
EXEC @Status = sp_releaseapplock @Resource = 'Lock ID', @LockOwner = 'Session';
print @Status;

Upvotes: 2

Related Questions