Path: csiph.com!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: Wed, 29 Nov 2017 15:22:26 +0300 Organization: A noiseless patient Spider Lines: 30 Message-ID: <20171129152226.e1d2475ae9a71ee46b35b3cf@g{oogle}mail.com> References: <20171125145629.a8f9f75371e03662bf0496dd@gmail.com> <20171125182411.7cc99cc41acef664ea224b1c@gmail.com> <20171126194310.7f5514c0300678d88cf17eaa@gmail.com> <20171127113428.2629ecf62d24b8131d42d13f@g{oogle}mail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Injection-Info: reader02.eternal-september.org; posting-host="85cc17ee4445131a3e1c31a0d6153f8b"; logging-data="4868"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+PGrFijeDjm/fPYptAHq/WvMnmawsbpww=" X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32) Cancel-Lock: sha1:UnyBGeYtAC4oF4a2Snw7KXmEZy0= Xref: csiph.com comp.databases.ms-sqlserver:1994 Erland Sommarskog to Anton Shepelev: >>An inner BEGIN TRAN only increments the counter, >>an inner COMMIT TRAN decrements it, but (!) ROLL- >>BACK TRAN does actually undo the the outer trans- >>action and sets the counter to zero. This is all >>very unintuitive to me, and requires special han- >>dling 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. What about error handling? In the case of pure nesting I could simply ensure that every BEGIN TRAN has a corresponding COMMIT or ROLLBACK, probably with the help of a TRY..CATCH block. In the current model, however, it is a tad less trivial, because I must test @@TRANCOUNT to see whether a nested call did not roll back my transaction in the course of its own error handling logic. -- () ascii ribbon campaign - against html e-mail /\ http://preview.tinyurl.com/qcy6mjc [archived]