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

Groups > comp.databases.ms-sqlserver > #1991

Re: Using sp_getapplock outside a transaction

Show all headers | View raw

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'

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web