Path: csiph.com!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Anton Shepelev 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> References: <20171125145629.a8f9f75371e03662bf0496dd@gmail.com> <20171125182411.7cc99cc41acef664ea224b1c@gmail.com> 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 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]