Few confusing things about transactions
in [Programming SQL]
|
From: Erland Sommarskog on 11 May 2010 17:39 trubar a (asponmynet(a)gmail.com) writes: > Ok, this is confusing. Both batches produce the same type of error, > but print statement in first bacth gets executed, while in second > batch doesn�t. Yes, it is just crazy! > a) By latter case are you referring to second batch? Thus, if both > batches were each in its own transaction, then error in the first > batch wouldn�t cause a rollback, while error in second batch would > cause a rollback? Yup. > b) Perhaps a stupid question, but when you say error in second batch > would cause a rollback, are you implying that rollback would happen > even if we didn�t include a ROLLBACK statement inside the transaction: Yup. The batch is aborted, and that implicitly rolls back the transaction. > a) You lost me here. Assuming SP checks whether it is called within a > running transaction and assuming check confirms that it was called > from within some transaction, then why would it be a good idea to > start another transaction inside this SP? Because adding the code to check whether there already is a transaction to investigate whether you should start a new transaction adds extra complexity. Keep in mind that you if adhere to this, you would need to do it in every procedure - you cannot tell if someone wants to reuse your procedure as a component three years from now. > b) Also, you are stating that we essentially have two choices: either > starting a new transaction inside SP ( even if SP was called from > within the transaction ) or using save points. But at least to my > understanding, I don�t see why save points would less needed ( or not > needed at all ) of if SP called from within a transaction also starts > its own transaction?! In both cases we would need savepoint to > rollback just the statements executed inside this SP, so�uh, I�m dumb Yes. But what I'm saying is that most of the time we don't care, but if there is an error, we let the entire transaction slip. That's want you want in most cases, so the complexity to deal with savepoints every time is not worth 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 12 May 2010 16:43 To summarize - if we dont know whether our SP will be called from within a transaction, then you recommend that ( please correct me if any of the points are off ): * we dont check ( due to complexity ) whether SP is called from within a running transaction, but instead we simply start a new transaction inside SP * savepoints shouldnt be used due to the added complexity and due to the fact that they only make sense if we dont use a TRY-CATCH block ( since if there is a CATCH handler, we can only roll it back entirely and not to a savepoint ) *we should use TRY-CATCH block inside SP, else if error occurs we may encounter strange/inconsistent behavior (like your example with two identical batches, where upon error the first one gets committed while second one is rolled back) * If no errors occur, then we commit only if the following condition evaluates to true, else we do nothing: IF @@trancount = 0 COMMIT * if error does occur, then should we inside a CATCH block ROLLBACK only if SP wasnt called from within transaction or should we ROLLBACK regardless? On May 11, 2:39 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > trubar a (asponmy...(a)gmail.com) writes: > > > b) Also, you are stating that we essentially have two choices: either > > starting a new transaction inside SP ( even if SP was called from > > within the transaction ) or using save points. But at least to my > > understanding, I dont see why save points would less needed ( or not > > needed at all ) of if SP called from within a transaction also starts > > its own transaction?! In both cases we would need savepoint to > > rollback just the statements executed inside this SP, so uh, Im dumb > > Yes. But what I'm saying is that most of the time we don't care, but > if there is an error, we let the entire transaction slip. > What do you mean with the term let the entire transaction slip? Perhaps that we should ROLLBACK the whole transaction? > That's want you want in most cases, so the complexity to deal with savepoints > every time is not worth it. But I don't find that using savepoints adds much to the complexity, or am I missing something?
From: Erland Sommarskog on 12 May 2010 18:10 trubar a (asponmynet(a)gmail.com) writes: > * we don�t check ( due to complexity ) whether SP is called from > within a running transaction, but instead we simply start a new > transaction inside SP Yes. > * savepoints shouldn�t be used due to the added complexity and due to > the fact that they only make sense if we don�t use a TRY-CATCH block > ( since if there is a CATCH handler, we can only roll it back entirely > and not to a savepoint ) No this is wrong. You can rollback to a savepoint in a CATCH handler, as long as the error is not batch-aborting. A batch-aborting error will be caught in the CATCH handler, but the transaction will be "doomed", and you can only roll it back. > *we should use TRY-CATCH block inside SP, else if error occurs we may > encounter strange/inconsistent behavior (like your example with two > identical batches, where upon error the first one gets committed while > second one is rolled back) Yes, TRY-CATCH is highly recommendable. > * If no errors occur, then we commit only if the following condition > evaluates to true, else we do nothing: > > IF @@trancount = 0 > COMMIT The above is not good, as it will fail to commit - or yield an error. Possibly: IF @@trancount > 0 COMMIT TRANSACTION But I'm not sure that I like it. You should know at a certain point whether you need to commit at all. > * if error does occur, then should we inside a CATCH block ROLLBACK > only if SP wasn�t called from within transaction or should we > ROLLBACK regardless? ROLLBACK regardless in most cases. Here you would do: IF @@trancount > 0 ROLLBACK TRANSACTION so we don't need to bother whether the error occurred before or after we called BEGIN TRANSACTION. -- 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 13 May 2010 17:59 I apologize for still dragging this topic On May 12, 3:10 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > trubar a (asponmy...(a)gmail.com) writes: > > > *we should use TRY-CATCH block inside SP, else if error occurs we may > > encounter strange/inconsistent behavior (like your example with two > > identical batches, where upon error the first one gets committed while > > second one is rolled back) > > Yes, TRY-CATCH is highly recommendable. > > > * If no errors occur, then we commit only if the following condition > > evaluates to true, else we do nothing: > > > IF @@trancount = 0 > > COMMIT > > The above is not good, as it will fail to commit - or yield an error. Yeah,Ive made a mistake > Possibly: > > IF @@trancount > 0 > COMMIT TRANSACTION > > But I'm not sure that I like it. You should know at a certain point > whether you need to commit at all. Uhm, could you elaborate on what you mean by " we should know at certain point..."? > > > * if error does occur, then should we inside a CATCH block ROLLBACK > > only if SP wasnt called from within transaction or should we > > ROLLBACK regardless? > > ROLLBACK regardless in most cases. Here you would do: > > IF @@trancount > 0 ROLLBACK TRANSACTION > > so we don't need to bother whether the error occurred before or > after we called BEGIN TRANSACTION. > * But if SP always starts a new transaction, then is there really a point in checking whether @@trancount is greater than zero? * Also, Im not sure I understand your reasoning why we should roll back regardless of whether SP was called from within a transaction or not. Namely, if SP wasnt called from within a transaction, then inside CATCH the transaction will be rolled back and no error will be returned to the user, but if it was called from within a transaction, then IF @@trancount > 0 ROLLBACK TRANSACTION will throw an error within a CATCH block why would we want that? * BTW you did say in one of your earlier posts that we should let the transaction slip what exactly did you mean by that?
From: Erland Sommarskog on 14 May 2010 04:08
trubar a (asponmynet(a)gmail.com) writes: >> � �IF @@trancount > 0 >> � � � COMMIT TRANSACTION >> >> But I'm not sure that I like it. You should know at a certain point >> whether you need to commit at all. > > Uhm, could you elaborate on what you mean by " we should know at > certain point..."? Simply: When you write code you should know what you're doing! The code above says "I don't know whether there is an active transaction, but if there is, I commit it." My point is that you should know whether you did start one or not. (The one exception is if you use implicit transactions - but you shouldn't.) >> ROLLBACK regardless in most cases. Here you would do: >> >> � �IF @@trancount > 0 ROLLBACK TRANSACTION >> >> so we don't need to bother whether the error occurred before or >> after we called BEGIN TRANSACTION. >> > > * But if SP always starts a new transaction, then is there really a > point in checking whether @@trancount is greater than zero? Many of my procedures do a bunch of validating or reading before they actually start a transaction. Also, the error may have occured in an inner procedure, and that procedure rolled back the transaction before it re-reaised the error. > * Also, I�m not sure I understand your reasoning why we should roll > back regardless of whether SP was called from within a transaction or > not. Well, if we were not called from a transaction, there is nothing to roll back. If we were called from a transaction, we should roll it back, because we can no longer guarantee that we are able to fulfil our contract, and we don't want to leave the database in an inconsistent state. > then > > IF @@trancount > 0 > ROLLBACK TRANSACTION > > will throw an error within a CATCH block � why would we want that? No, the error is raised when the procedure exits. In any case, an error has already occurred, and the show is over. Any extra error message is on the noise on the wire, but it's more important that we clean up the transaction that we know is bad. > * BTW � you did say in one of your earlier posts that we should let > the transaction slip � what exactly did you mean by that? Do you have the context where I said 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 |