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
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.
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
While replying, I noticed that sp_releaseapplock
takes @LockOwner as well. So perhaps that needs
to be set to 'session' there.
In one session you will be allowed to lock the
same "resource" several times, because it is the
same session locking it.
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
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.
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:
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.
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.
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.
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)
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!
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.
I think Anton got the out-of-transaction lock to
work, but I've rather lost track of the conversa-
tion.
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.
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.
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
http://www.sommarskog.se/error_handling/Part1.html
Thank you, I have started to read it and enjoy ar-
ticle very much.
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.
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 294 |
| Nodes: | 16 (2 / 14) |
| Uptime: | 245:52:19 |
| Calls: | 6,626 |
| Calls today: | 2 |
| Files: | 12,175 |
| Messages: | 5,320,569 |