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

Re: Using sp_getapplock outside a transaction

From	Anton Shepelev <anton.txt@g{oogle}mail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Using sp_getapplock outside a transaction
Date	2017-11-27 11:34 +0300
Organization	A noiseless patient Spider
Message-ID	<20171127113428.2629ecf62d24b8131d42d13f@g{oogle}mail.com> (permalink)
References	(1 earlier) <e7eeb2b3-4502-4512-a2c8-a20e908fa080@googlegroups.com> <20171125182411.7cc99cc41acef664ea224b1c@gmail.com> <XnsA838DBBEAC219Yazorman@127.0.0.1> <20171126194310.7f5514c0300678d88cf17eaa@gmail.com> <XnsA839CAC1E58E4Yazorman@127.0.0.1>

Show all headers | View raw

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]

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