Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1979 > unrolled thread

Using sp_getapplock outside a transaction

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 14:56 +0300
    Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 05:37 -0800
      Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 18:24 +0300
        Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 08:36 -0800
          Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-25 21:13 +0300
            Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-25 16:21 -0800
        Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-25 21:36 +0100
          Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@gmail.com> - 2017-11-26 19:43 +0300
            Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-26 19:55 +0100
              Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-27 11:34 +0300
                Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-27 11:56 +0100
                  Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-27 14:23 -0800
                    Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-28 08:50 +0100
                    Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 15:11 +0300
                  Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 15:22 +0300
                    Re: Using sp_getapplock outside a transaction Erland Sommarskog <esquel@sommarskog.se> - 2017-11-29 13:44 +0100
                      Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 18:44 +0300
                        Re: Using sp_getapplock outside a transaction Anton Shepelev <anton.txt@g{oogle}mail.com> - 2017-11-29 19:42 +0300
                        Re: Using sp_getapplock outside a transaction rja.carnegie@gmail.com - 2017-11-30 13:19 -0800

#1979 — Using sp_getapplock outside a transaction

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

[toc] | [next] | [standalone]

#1980

On Saturday, 25 November 2017 11:56:34 UTC, Anton Shepelev  wrote:
> 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

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.

[toc] | [prev] | [next] | [standalone]

#1981

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

[toc] | [prev] | [next] | [standalone]

#1982

On Saturday, 25 November 2017 15:24:15 UTC, Anton Shepelev  wrote:
> 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.

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.

[toc] | [prev] | [next] | [standalone]

#1983

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

[toc] | [prev] | [next] | [standalone]

#1985

On Saturday, 25 November 2017 18:13:02 UTC, Anton Shepelev  wrote:
> 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

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.

[toc] | [prev] | [next] | [standalone]

#1986

Anton Shepelev (anton.txt@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'
  END
  ELSE PRINT 'Failed to acquire lock'

[toc] | [prev] | [next] | [standalone]

#1987

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.

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

[toc] | [prev] | [next] | [standalone]

#1988

Anton Shepelev (anton.txt@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.

[toc] | [prev] | [next] | [standalone]

#1989

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]

[toc] | [prev] | [next] | [standalone]

#1990

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.

[toc] | [prev] | [next] | [standalone]

#1991

On Monday, 27 November 2017 10:56:34 UTC, Erland Sommarskog  wrote:
> 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.

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'

[toc] | [prev] | [next] | [standalone]

#1992

 (rja.carnegie@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.

[toc] | [prev] | [next] | [standalone]

#1993

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.

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

[toc] | [prev] | [next] | [standalone]

#1994

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.

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

[toc] | [prev] | [next] | [standalone]

#1995

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.)

[toc] | [prev] | [next] | [standalone]

#1996

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.

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

[toc] | [prev] | [next] | [standalone]

#1997

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."

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

[toc] | [prev] | [next] | [standalone]

#2001

On Wednesday, 29 November 2017 15:44:18 UTC, Anton Shepelev  wrote:
> 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.

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.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web