Few confusing things about transactions
in [Programming SQL]
|
From: Erland Sommarskog on 30 May 2010 16:06 trubar a (asponmynet(a)gmail.com) writes: > I didn�t think it was possible for rollback command issued inside B > to rollback the entire transaction prior to procedure B exiting. > In other words, if we assume transaction which called B is inside > scope S1, while transaction started within B is inside scope S2, then > I thought that rollback command issued within B would first rollback > only the transaction started within scope S2, You haven't been listening to me, have you? :-) There is no transaction started within scope S1. There can only be one active transaction at a time. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: trubar a on 1 Jun 2010 14:53 On May 30, 1:06 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > trubar a (asponmy...(a)gmail.com) writes: > > I didnt think it was possible for rollback command issued inside B > > to rollback the entire transaction prior to procedure B exiting. > > In other words, if we assume transaction which called B is inside > > scope S1, while transaction started within B is inside scope S2, then > > I thought that rollback command issued within B would first rollback > > only the transaction started within scope S2, > > You haven't been listening to me, have you? :-) > Uhm, I cant tell whether youre joking or not, so Im gonna assume youre serious about claims you've made below ;) > There is no transaction started within scope S1. There can only be one active transaction at a time. > Youve lost me here. If Im not mistaken, then if we start a nested transaction T2 inside B ( where B is called from within a running transaction T1 ), then I assume T2 becomes a part of an already running transaction T1? Thus I wasnt implying that T2 is independent of T1. > There is no transaction started within scope S1. I thought sql code located inside SP is in different scope than code outside SP. So wouldnt in a way same be true for a nested transaction started inside B ( now Im not claiming T2 is not part of T1, Im just saying that part of an active transaction T1 occupies different scope)
From: Erland Sommarskog on 3 Jun 2010 17:20 trubar a (asponmynet(a)gmail.com) writes: >> There is no transaction started within scope S1. There can only be one >> active transaction at a time. > > You�ve lost me here. If I�m not mistaken, then if we start a nested > transaction T2 inside B ( where B is called from within a running > transaction T1 ), then I assume T2 �becomes a part� of an already > running transaction T1? Thus I wasn�t implying that T2 is independent > of T1. That is one way of describing the same thing, but it is a little misleading. There are no nested transactions really, only one single transaction and increments of @@trancount. >> There is no transaction started within scope S1. > > I thought sql code located inside SP is in different scope than code > outside SP. Yes, two different stored procedures are two different scopes, and variables declared in one procedure cannot be accessed from the other. Likewise temp tables created in a scope is deleted when the scope exists, and any SET options changed are reverted with scope exit. That is a declarative scope. > So wouldn�t in a way same be true for a nested transaction > started inside B ( now I�m not claiming T2 is not part of T1, I�m just > saying that part of an active transaction T1 occupies different > scope) But transactions are not bound to scopes, but are purely run time. Except for the fact that you get error 266 when you exit a scope with a different in @@trancount than when you entered it. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: trubar a on 4 Jun 2010 16:27 uh, at last the end of this thread...thank you for having the patience and for helping me out with the subject,and I'd also like to apologize for taking so much of your time Now that this thread is finished, it would be super dooper if you'd help me with my other two threads on full-text queries ( just kidding :D, though it wouldn't hurt if you did ;) ) cheers
From: Erland Sommarskog on 4 Jun 2010 18:17
trubar a (asponmynet(a)gmail.com) writes: > Now that this thread is finished, it would be super dooper if you'd > help me with my other two threads on full-text queries ( just > kidding :D, though it wouldn't hurt if you did ;) ) I've read a book on full-text, but I've never used it, so I will have to decline. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |