Using sp_getapplock outside a transaction

[Anton Shepelev]

Hello, all

According to the documentation for sp_getapplock
available at

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql

lock_owner is nvarchar(32). The value can be
Transaction (the default) or Session. When the
lock_owner value is Transaction, by default or
specified explicitly, sp_getapplock must be exe-
cuted from within a transaction.

When I try, however, to get a lock owned by the ses-
sion outside a transaction:

EXEC sp_getapplock 'test', 'Exclusive', @LockOwner = 'Session'
EXEC sp_releaseapplock 'test'

it fails with:

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

Does not this behavor it contradict the documenta-
tion?

--
Anton Shepelev

[rja.ca...@gmail.com]

I don't know why it isn't working, but it may be permissions -
in which case the error message is not a very good message.

<http://technico.qnownow.com/how-to-prevent-stored-procedure-running-concurrently-in-sql-server/>

says,
"to be able to call sp_getapplock a user calling the stored procedure must meet one of these conditions:
is dbo
is in the db_owner role
is the DB Principal ID (e.g. guest)
is in the DB Principal ID role (e.g. public)"

This is for an example where a stored procedure runs exclusively,
by the procedure taking a session lock on the procedure object
itself. You can try that example.

I've only looked briefly in this area - there may be a server setting,
or a difference between SQL Server versions or editions, that prevents
the session lock from being made. Again, this does not match well
what the error message tells you.

I don't remember how legitimate it is to call a procedure,
as you did, with parameter name @LockOwner used but not others
i.e. the first two. I put in all parameter names I'm using,
or none. It wouldn't do harm to try that.

I'm not sure who is still reading this group; there is more
activity in this web site <https://dba.stackexchange.com>
for "database administrators" - but I haven't opened it for
a while.

[Anton Shepelev]

rja.carnegie to Anton Shepelev:

> > According to the documentation for sp_getapplock
> > available at
> >
> > https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql
> >
> > lock_owner is nvarchar(32). The value can be
> > Transaction (the default) or Session. When
> > the lock_owner value is Transaction, by
> > default or specified explicitly, sp_getapplock

> > must be executed from within a transaction.

> >
> > When I try, however, to get a lock owned by the

> > session outside a transaction:

> >
> > EXEC sp_getapplock 'test', 'Exclusive', @LockOwner = 'Session'
> > EXEC sp_releaseapplock 'test'
> >
> > it fails with:
> >
> > The statement or function must be executed in
> > the context of a user transaction.
> >

> > Does not this behavor it contradict the documen-
> > tation?

>
> I don't know why it isn't working, but it may be

> permissions -- in which case the error message is

> not a very good message.

Does not seem to be permissions, for I am testing it
with full rights as 'sa'. Furhtermore, the return
value indicates the lock has been acquired. When I
execute:

DECLARE @getRes INT
DECLARE @resName VARCHAR(16) = 'test'
BEGIN TRAN
EXEC @getRes = sp_getapplock @resName, 'Exclusive', @LockOwner = 'Session'
IF @getRes >= 0
BEGIN
PRINT 'Lock acquired successfully. Releasing...'
EXEC sp_releaseapplock @resName
END
ELSE PRINT 'Failed to acquire lock'
ROLLBACK TRAN

I get the following surprising output:

Lock acquired successfully. Releasing...
Msg 1223, Level 16, State 1, Procedure xp_userlock, Line 1
Cannot release the application lock (Database Principal:
'public', Resource: 'test') because it is not currently held.

--
Anton Shepelev

[rja.ca...@gmail.com]

While replying, I noticed that sp_releaseapplock takes @LockOwner
as well. So perhaps that needs to be set to 'session' there.

If you don't release the lock explicitly, I gather it terminates
when your transaction or your session does. You could use the
Management Stidio button to "break and connect to a new server"
to test that, I think.

In one session you will be allowed to lock the same "resource"
several times, because it is the same session locking it.
All the locks must be unlocked after use, either by default
or explicitly by name.

I don't have a server here to test on, but I see now
I misunderstood something; the lock will be not on an
existing object in the database, but on the "resource name"
as an abstract, case-sensitive string.

Accordingly, setting that to nvavrchar(255) as specified,
or changing it to 'dbo.test', shouldn't matter - but, again,
you could try.

[Anton Shepelev]

rja.carnegie:

> While replying, I noticed that sp_releaseapplock
> takes @LockOwner as well. So perhaps that needs
> to be set to 'session' there.

Ah, that's it.

> In one session you will be allowed to lock the
> same "resource" several times, because it is the
> same session locking it.

Then per-transaction locks are the thing me. Thank
you.

--
Anton Shepelev

[Anton Shepelev]

Hello, all

According to the documentation for sp_getapplock
available at

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql

lock_owner is nvarchar(32). The value can be
Transaction (the default) or Session. When the
lock_owner value is Transaction, by default or

specified explicitly, sp_getapplock must be exe-

cuted from within a transaction.

When I try, however, to get a lock owned by the ses-

sion outside a transaction:

EXEC sp_getapplock 'test', 'Exclusive', @LockOwner = 'Session'
EXEC sp_releaseapplock 'test'

it fails with:

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

Does not this behavor it contradict the documenta-
tion?

--
() ascii ribbon campaign -- against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[rja.ca...@gmail.com]

Well, I might be wrong about this anyway... but what I think
I mean - unless you find different - is that your script can
put two or three or a hundred locks on the same resource,
even exclusive use, because the lock is to reserve the resource
to you, and you can do that with multiple copies of the lock -
or rather, counts of the lock, because lighter and heavier
locks on the resource add up to the heaviest combination of
locking - and then it stays locked as heavily as that until
all of the locks are released.

But if you run the script in two windows, one window can
lock the resource, exclusively, and then the other window
can't have it.

[Erland Sommarskog]

Anton Shepelev (anto...@gmail.com) writes:
> Does not seem to be permissions, for I am testing it
> with full rights as 'sa'. Furhtermore, the return
> value indicates the lock has been acquired. When I
> execute:
>
> DECLARE @getRes INT
> DECLARE @resName VARCHAR(16) = 'test'
> BEGIN TRAN
> EXEC @getRes = sp_getapplock @resName, 'Exclusive', @LockOwner =
> 'Session'
> IF @getRes >= 0
> BEGIN
> PRINT 'Lock acquired successfully. Releasing...'
> EXEC sp_releaseapplock @resName
> END
> ELSE PRINT 'Failed to acquire lock'
> ROLLBACK TRAN
>
> I get the following surprising output:
>
> Lock acquired successfully. Releasing...
> Msg 1223, Level 16, State 1, Procedure xp_userlock, Line 1
> Cannot release the application lock (Database Principal:
> 'public', Resource: 'test') because it is not currently held.
>

As RJA says, you need to specify LockOwner to sp_releaseapplock as well.

And you don't need the transaction. This runs successfully on my machine:

DECLARE @getRes INT
DECLARE @resName VARCHAR(16) = 'test'

EXEC @getRes = sp_getapplock @resName, 'Exclusive', @LockOwner = 'Session'
IF @getRes >= 0
BEGIN
PRINT 'Lock acquired successfully. Releasing...'

EXEC sp_releaseapplock @resName, 'Session'

[Anton Shepelev]

Thank you for the replies, Erland and rja. I have
found what I think is an ambiguity in the documenta-
tion:

Locks placed on a resource are associated with
either the current transaction or the current ses-
sion.

where the notion of "current transaction" is rather
unintuitive, for it refers not to the scope of the
immediate enfolding BEGIN TRAN block, but to the
outermost one. The following code:

DECLARE @lockRes INT

BEGIN TRAN -- outer transaction
BEGIN TRAN -- inner transaction
PRINT 'Acquiring lock in the inner transaction...'
EXEC @lockRes = sp_getapplock 'test', 'Exclusive'
IF @lockRes < 0 PRINT 'Failure.' ELSE PRINT 'Success.'
COMMIT TRAN
PRINT 'Releasing the lock in the outer transaction...'
EXEC @lockRes = sp_releaseapplock 'test'
IF @lockRes < 0 PRINT 'Failure.' ELSE PRINT 'Success.'
COMMIT TRAN

outputs:

Acquiring lock in the inner transaction...
Success.
Releasing the lock in the outer transaction...
Success.

which means that the life of a transaction lock ter-
minates with that of the *outermost* transaction.

[Erland Sommarskog]

Anton Shepelev (anto...@gmail.com) writes:
> Thank you for the replies, Erland and rja. I have
> found what I think is an ambiguity in the documenta-
> tion:
>
> Locks placed on a resource are associated with
> either the current transaction or the current ses-
> sion.
>
> where the notion of "current transaction" is rather
> unintuitive, for it refers not to the scope of the
> immediate enfolding BEGIN TRAN block, but to the
> outermost one. The following code:
>

There isn't really any ambiguity, because there is only one transaction.
The inner BEGIN TRANSACTION does not start any new transaction, but
only increases @@trancount. In the same manner, the first COMMIT in your
example only decreases @@trancount, but nothing is actually committed.

[Anton Shepelev]

Erland Sommarskog to Anton Shepelev:

>>Thank you for the replies, Erland and rja. I have

>>found what I think is an ambiguity in the documen-

>>ta- tion:
>>
>> Locks placed on a resource are associated with
>> either the current transaction or the current

>> session.

>>
>>where the notion of "current transaction" is
>>rather unintuitive, for it refers not to the scope
>>of the immediate enfolding BEGIN TRAN block, but
>>to the outermost one.
>

>There isn't really any ambiguity, because there is
>only one transaction. The inner BEGIN TRANSACTION

>does not start any new transaction, but only in-

>creases @@trancount. In the same manner, the first
>COMMIT in your example only decreases @@trancount,
>but nothing is actually committed.

Indeed. An inner BEGIN TRAN only increments the
counter, an inner COMMIT TRAN decrements it, but (!)
ROLLBACK TRAN does actually undo the the outer
transaction and sets the counter to zero. This is
all very unintuitive to me, and requires special
handling of ROLLBACKs in both T-SQL code and the
client application.

--
() ascii ribbon campaign - against html e-mail
/\ http://preview.tinyurl.com/qcy6mjc [archived]

[Erland Sommarskog]

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> Indeed. An inner BEGIN TRAN only increments the
> counter, an inner COMMIT TRAN decrements it, but (!)
> ROLLBACK TRAN does actually undo the the outer
> transaction and sets the counter to zero. This is
> all very unintuitive to me, and requires special
> handling of ROLLBACKs in both T-SQL code and the
> client application.
>

It becomes more intuitive if you stop to think in terms of "outer
transaction". A transaction is all there is. So the ROLLBACK will
roll back it all.

The point with BEGIN/COMMIT increase @@trancount is that it makes
it easier to nest procedures that both start transactions.

[rja.ca...@gmail.com]

Either my own understanding is wrong, or you should say,
"Do not think in terms of outer transaction" - because
the phrase "stop to think" has a different meaning.

I think we are agreeing that a transaction starts when
"BEGIN TRAN" first appears - or automatically if a certain
setting is in place (which I've forgotten) - and ends either
immediately at ROLLBACK TRAN, or else when the number of
COMMIT TRAN statements equals the number of implicit
and explicit BEGIN TRAN. Naming the BEGIN TRAN statements
does not get around this: only the outermost transaction name
can be addressed. Using transaction savepoints does get
around it in a way...

Also, we should avoid having long transactions: they block
other business and (with simple recovery) they bloat the
log file. But if it's your own server then do whatever
you want!

I think Anton got the out-of-transaction lock to work,
but I've rather lost track of the conversation.
i.e. one term needed to be added to the "release" statement:

EXEC sp_getapplock 'test', 'Exclusive', @LockOwner = 'Session'
EXEC sp_releaseapplock 'test', @LockOwner = 'Session'

[Erland Sommarskog]

(rja.ca...@gmail.com) writes:
> On Monday, 27 November 2017 10:56:34 UTC, Erland Sommarskog wrote:
>> It becomes more intuitive if you stop to think in terms of "outer
>> transaction". A transaction is all there is. So the ROLLBACK will
>> roll back it all.
>>
>

> Either my own understanding is wrong, or you should say,
> "Do not think in terms of outer transaction" - because
> the phrase "stop to think" has a different meaning.

Please bear with me: English is not my native language.

> I think we are agreeing that a transaction starts when
> "BEGIN TRAN" first appears - or automatically if a certain
> setting is in place (which I've forgotten)

SET IMPLICIT_TRANSACTIONS ON

> Also, we should avoid having long transactions: they block
> other business and (with simple recovery) they bloat the
> log file. But if it's your own server then do whatever
> you want!

Our transactions should be as long as needed to complete the atomic
piece of work we want to do, not shorter.

[Anton Shepelev]

rja.carnegie to Erland Sommarskog:

>>It becomes more intuitive if you stop to think in
>>terms of "outer transaction".
>

>Either my own understanding is wrong, or you should

>say, "Do not think in terms of outer transac-
>tion" -- because the phrase "stop to think" has a
>different meaning.

Messems Erland meant "stop thinking in terms of..."

>I think Anton got the out-of-transaction lock to

>work, but I've rather lost track of the conversa-
>tion.

I have, thanks.

[Anton Shepelev]

Erland Sommarskog to Anton Shepelev:

>>An inner BEGIN TRAN only increments the counter,

>>an inner COMMIT TRAN decrements it, but (!) ROLL-
>>BACK TRAN does actually undo the the outer trans-

>>action and sets the counter to zero. This is all

>>very unintuitive to me, and requires special han-

>>dling of ROLLBACKs in both T-SQL code and the
>>client application.
>
>It becomes more intuitive if you stop to think in
>terms of "outer transaction". A transaction is all
>there is. So the ROLLBACK will roll back it all.
>
>The point with BEGIN/COMMIT increase @@trancount is
>that it makes it easier to nest procedures that
>both start transactions.

What about error handling? In the case of pure
nesting I could simply ensure that every BEGIN TRAN
has a corresponding COMMIT or ROLLBACK, probably
with the help of a TRY..CATCH block. In the current
model, however, it is a tad less trivial, because I
must test @@TRANCOUNT to see whether a nested call
did not roll back my transaction in the course of
its own error handling logic.

[Erland Sommarskog]

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:

> What about error handling? In the case of pure
> nesting I could simply ensure that every BEGIN TRAN
> has a corresponding COMMIT or ROLLBACK, probably
> with the help of a TRY..CATCH block. In the current
> model, however, it is a tad less trivial, because I
> must test @@TRANCOUNT to see whether a nested call
> did not roll back my transaction in the course of
> its own error handling logic.
>

If you don't want to use TRY-CATCH, you need to write some tedious
piece of code.

On my web site there is a series of articles on error and transaction
handling in SQL Server, which starts here:
http://www.sommarskog.se/error_handling/Part1.html

If you want to do without TRY-CATCH, this is something I cover very
little, except for the old articles for SQL 2000. (You find links to
these if you go to the link above.)

[Anton Shepelev]

Erland Sommarskog to Anton Shepelev:

>>What about error handling? In the case of pure
>>nesting I could simply ensure that every
>>BEGIN TRAN has a corresponding COMMIT or ROLLBACK,
>>probably with the help of a TRY..CATCH block. In

>>the current model, however, it is a tad less triv-

>>ial, because I must test @@TRANCOUNT to see

>>whether a nested call did not roll back my trans-

>>action in the course of its own error handling
>>logic.
>
>If you don't want to use TRY-CATCH, you need to
>write some tedious piece of code.

The hay surely smelleth of that weed. I was think-
ing along the lines of:

https://technet.microsoft.com/en-us/library/aa175920(v=sql.80).aspx

Or did you mean something else?

>On my web site there is a series of articles on er-

>ror and transaction handling in SQL Server, which
>starts here:
>http://www.sommarskog.se/error_handling/Part1.html

Thank you, I have started to read it and enjoy arti-
cle very much. Kudos for a clean website.

[Anton Shepelev]

I wrote to Erland Sommarskog

>>http://www.sommarskog.se/error_handling/Part1.html
>
>Thank you, I have started to read it and enjoy ar-
>ticle very much.

Insert a definite article before "article."

[rja.ca...@gmail.com]

Lately, I've worked mainly on regularly built data warehousing,
i.e. just copying data in order to make reports. So error
handling is less about transactions and TRY-CATCH and more
"if it doesn't tun, maybe it will tomorrow".

I used to use an error handling block that goes like:

BEGIN TRANSACTION
EXEC procedure
SET @returnerror = @@ERROR; IF (@returnerror <> 0 ) GOTO failure
....
COMMIT TRANSACTION
GOTO terminate
failure:
IF ( @@TRANCOUNT > 0 ) BEGIN ROLLBACK TRANSACTION END
RAISERROR(N'Error.', 16, 1)
terminate:
/* End of procedure */

There was also something about deallocating a cursor, but then I dug
cursor variables that eliminate themselves (close enough).

The approach depends on exiting a procedure with an error that
can be read where the procedure was called, and then raising
another error at the end of this procedure, to hand it on.
But only "GOTO failure" needs to be included after each place
where an error may need to be handled in the program body.