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

Re: Using sp_getapplock outside a transaction

Path	csiph.com!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	Anton Shepelev <anton.txt@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Using sp_getapplock outside a transaction
Date	Sun, 26 Nov 2017 19:43:10 +0300
Organization	A noiseless patient Spider
Lines	39
Message-ID	<20171126194310.7f5514c0300678d88cf17eaa@gmail.com> (permalink)
References	<20171125145629.a8f9f75371e03662bf0496dd@gmail.com> <e7eeb2b3-4502-4512-a2c8-a20e908fa080@googlegroups.com> <20171125182411.7cc99cc41acef664ea224b1c@gmail.com> <XnsA838DBBEAC219Yazorman@127.0.0.1>
Mime-Version	1.0
Content-Type	text/plain; charset=US-ASCII
Content-Transfer-Encoding	7bit
Injection-Info	reader02.eternal-september.org; posting-host="b0e5ee9188bab6709777c3e5c3054662"; logging-data="5155"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX188oSvB5m4pqUK7XHqtULi93KihBm+aYc8="
X-Newsreader	Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Cancel-Lock	sha1:W1utgO3lM/nhMI6R7bokkQ4yetk=
Xref	csiph.com comp.databases.ms-sqlserver:1987

Show key headers only | View raw

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]

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