Few confusing things about transactions
in [Programming SQL]
|
From: trubar a on 21 May 2010 16:13 I apologize for replying so late. On May 14, 1:08 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > > > * Also, Im not sure I understand your reasoning on why we should roll > > back regardless of whether SP was called from within a transaction or > > not. > > > 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. > Assuming stored procedure A calls stored procedure B and assuming error occurs within B, then wont what you suggest ( thus rolling back inside Bs CATCH block ) make the two SPs tightly coupled, since now A wont have a chance to decide by itself whether or not it wants to abort the transaction? I realize only other option would be for B to use save points, which you said is a bad design, but isnt making SPs tightly coupled also a bad design? > > 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, Yeah, but if B just raises an error, then @@trancount doesnt get set to back zero, while with B rolling back the transaction also sets @@trancount to zero. > A batch-aborting error will be caught in the CATCH handler, but the transaction will be > "doomed", and you can only roll it back. a) Are you sure that batch-aborting error will be caught by CATCH handler? Namely, I did the following query and it appears that as soon as batch aborting error was encountered, transactions were immediately rolled back and then batch got aborted, and thus it appears that CATCH block didnt have a chance to run: select convert(datetime, '20080230') print 'This prints' begin try begin transaction select convert(datetime, '2080230') print 'This does not print' commit end try begin catch select @@TRANCOUNT rollback end catch b) Anyways, what's the point of batch aborting errors abort the batch even if they are thrown within TRY-CATCH blocks? > > > * 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? > Here's your quote: "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. " thanx
From: Erland Sommarskog on 21 May 2010 17:49 trubar a (asponmynet(a)gmail.com) writes: > Assuming stored procedure A calls stored procedure B and assuming > error occurs within B, then won�t what you suggest ( thus rolling back > inside B�s CATCH block ) make the two SPs tightly coupled, since now A > won�t have a chance to decide by itself whether or not it wants to > abort the transaction? I realize only other option would be for B to > use save points, which you said is a bad design, but isn�t making SPs > tightly coupled also a bad design? Not really. That is more a general pattern. "If you call a stored procedure, it may run into an error where it must roll back the transaction". Keep in mind that there is a vast difference between COMMIT and ROLLBACK. COMMIT is the normal flow. ROLLBACK usually only happens on the exception path. > a) Are you sure that batch-aborting error will be caught by CATCH > handler? Yes. (OK, so I've seen a few exceptions, but they are very rare.) > Namely, I did the following query and it appears that as soon > as batch aborting error was encountered, transactions were immediately > rolled back and then batch got aborted, and thus it appears that CATCH > block didn�t have a chance to run: > > select convert(datetime, '20080230') > print 'This prints' > > begin try > begin transaction > select convert(datetime, '2080230') > print 'This does not print' > commit > end try > begin catch > select @@TRANCOUNT > rollback > end catch When I run this, I get this output: ----------------------- Msg 242, Level 16, State 3, Line 2 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. This prints ----------------------- (0 row(s) affected) ----------- 1 (1 row(s) affected) The last result set is the "SELECT @@trancount" in the CATCH handler. > b) Anyways, what's the point of batch aborting errors abort the batch > even if they are thrown within TRY-CATCH blocks? Beats me. Backwards compatibility, I guess. Error handling in SQL Server is really confusing some times. >> > * 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? > > Here's your quote: > > "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. " What I have been saying: in case of an error, we roll back the entire 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 26 May 2010 19:28 hi Assuming stored procedure B is called from within a transaction and assuming B also opens a new transaction, then if B rolls back the transaction, then two things happen: the whole transaction is rolled back an error is raised ( due to rollback command setting @@trancount to zero ) a) But is the whole transaction rolled back due to an error being raised ( thus is an error a batch aborting transaction ) or due to rollback command issued within B? Im asking this since this error is being raised when B exits, which is before the rollback command ( issued inside B ) has a chance to finish rolling back the whole transaction. On May 21, 2:49 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > trubar a (asponmy...(a)gmail.com) writes: > > Namely, I did the following query and it appears that as soon > > as batch aborting error was encountered, transactions were immediately > > rolled back and then batch got aborted, and thus it appears that CATCH > > block didnt have a chance to run: > > > select convert(datetime, '20080230') > > print 'This prints' > > > begin try > > begin transaction > > select convert(datetime, '2080230') > > print 'This does not print' > > commit > > end try > > begin catch > > select @@TRANCOUNT > > rollback > > end catch > > When I run this, I get this output: > > ----------------------- > Msg 242, Level 16, State 3, Line 2 > The conversion of a char data type to a datetime data type resulted in an > out-of-range datetime value. > > This prints > > ----------------------- > > (0 row(s) affected) > > ----------- > 1 > > (1 row(s) affected) > > The last result set is the "SELECT @@trancount" in the CATCH handler. > But then why doesnt the result of "SELECT @@trancount" get displayed? thanx
From: Erland Sommarskog on 27 May 2010 18:11 trubar a (asponmynet(a)gmail.com) writes: > Assuming stored procedure B is called from within a transaction and > assuming B also opens a new transaction, then if B rolls back the > transaction, then two things happen: > � the whole transaction is rolled back > � an error is raised ( due to rollback command setting @@trancount to > zero ) > > a) But is the whole transaction rolled back due to an error being > raised ( thus is an error a batch aborting transaction ) or due to > rollback command issued within B? I�m asking this since this error is > being raised when B exits, which is before the rollback command > ( issued inside B ) has a chance to finish rolling back the whole > transaction. Not sure what you mean here, but if B includes ROLLBACK TRANSACTION RETURN The entire transaction is rolled back before RETURN is executed. At which point error 266 will be raised. -- 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 28 May 2010 16:48
hi I really appreciate all your help and I also apologize for keep dragging this thread for so long. Anyways, time to wrap it up On May 27, 3:11 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > trubar a (asponmy...(a)gmail.com) writes: > > Assuming stored procedure B is called from within a transaction and > > assuming B also opens a new transaction, then if B rolls back the > > transaction, then two things happen: > > the whole transaction is rolled back > > an error is raised ( due to rollback command setting @@trancount to > > zero ) > > > a) But is the whole transaction rolled back due to an error being > > raised ( thus is an error a batch aborting transaction ) or due to > > rollback command issued within B? Im asking this since this error is > > being raised when B exits, which is before the rollback command > > ( issued inside B ) has a chance to finish rolling back the whole > > transaction. > > Not sure what you mean here, 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, then it would have to wait for B to exit, and only after B exits would it be able to also rollback the main transaction ( which was started within scope S1 ) > -- > trubar a (asponmy...(a)gmail.com) writes: > > Namely, I did the following query and it appears that as soon > > as batch aborting error was encountered, transactions were immediately > > rolled back and then batch got aborted, and thus it appears that CATCH > > block didnt have a chance to run: > > select convert(datetime, '20080230') > > print 'This prints' > > begin try > > begin transaction > > select convert(datetime, '2080230') > > print 'This does not print' > > commit > > end try > > begin catch > > select @@TRANCOUNT > > rollback > > end catch > When I run this, I get this output: > ----------------------- > Msg 242, Level 16, State 3, Line 2 > The conversion of a char data type to a datetime data type resulted in an > out-of-range datetime value. > This prints > ----------------------- > (0 row(s) affected) > ----------- > 1 > (1 row(s) affected) > The last result set is the "SELECT @@trancount" in the CATCH handler. But then why doesnt the result of "SELECT @@trancount" get displayed? thanx |