Path: csiph.com!news.mixmin.net!news.unit0.net!eternal-september.org!feeder.eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail From: Anton Shepelev Newsgroups: comp.databases.ms-sqlserver Subject: Re: Using sp_getapplock outside a transaction Date: Mon, 27 Nov 2017 11:34:28 +0300 Organization: A noiseless patient Spider Lines: 33 Message-ID: <20171127113428.2629ecf62d24b8131d42d13f@g{oogle}mail.com> References: <20171125145629.a8f9f75371e03662bf0496dd@gmail.com> <20171125182411.7cc99cc41acef664ea224b1c@gmail.com> <20171126194310.7f5514c0300678d88cf17eaa@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="77318a268b11ee4568e4db204dc707fa"; logging-data="8375"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18V4CCtlUggSkiMLlB7S1+sxkA9NB031Wo=" X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32) Cancel-Lock: sha1:wbcUVIYlK++VCpfy89PPZI+HAHQ= Xref: csiph.com comp.databases.ms-sqlserver: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]