Few confusing things about transactions
in [Programming SQL]
|
From: trubar a on 5 May 2010 17:29 Hi Sorry for putting so many questions into one thread, but in my opinion questions are somewhat related. If its a problem, I can break the questions into several threads. 1) Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than 1 simply decrements @@TRANCOUNT by 1. I assume calling rollback inside a nested transaction rolls back the entire transaction and not just the nested one? 2) One of the optional arguments of BEGIN TRANSACTION statement is also transaction_name parameter: BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] [ ; ] Although each COMMIT TRANSACTION statement has a transaction_name parameter, there is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure that the proper number of commits are coded to decrement @@TRANCOUNT to 0 and thereby commit the outer transaction. a) Does the above quote imply that transaction_name parameters are always used just for readability? b) But doesnt the following quote imply that transaction_name parameters are not used just for readability: If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone. c) Anyways, I thought simply calling ROLLBACK / ROLLBACK TRANSACTION will roll the entire transaction to its beginning, even if ROLLBACK is called inside nested transaction? If so, why would we ever need to call ROLLBACK TRANSACTION transaction_name instead of just calling ROLLBACK? 3) BEGIN TRY DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMIT TRANSACTION; END TRY The above code is an excerpt from the SQL code Ive posted at the bottom of this post. In any case, code shown in this excerpt calls COMMIT TRANSACTION only if @TranCounter variable is equal to 0 ( which means no transaction was started before the procedure was called). But doesnt this checking only make sense if calling COMMIT TRANSACTION would commit the entire transaction, even if @TranCounter was greater than 0 ( @TranCounter being greater than 0 means that @@TRANCOUNT > 1)? But as far as I know, if @@TRANCOUNT is greater than 1, then all that happens is that @@TRANCOUNT gets decremented by 1 ( thus transaction doesnt get committed ). The following quote also suggests this: When used in nested transactions, commits of the inner transactions do not free resources or make their modifications permanent. The data modifications are made permanent and resources freed only when the outer transaction is committed. Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than 1 simply decrements @@TRANCOUNT by 1. When @@TRANCOUNT is finally decremented to 0, the entire outer transaction is committed. Example is taken from http://msdn.microsoft.com/en-us/library/ms188378.aspx : USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.objects WHERE name = N'SaveTranExample') DROP PROCEDURE SaveTranExample; GO CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS -- Detect if the procedure was called -- from an active transaction and save -- that for later use. -- In the procedure, @TranCounter = 0 -- means there was no active transaction -- and the procedure started one. -- @TranCounter > 0 means an active -- transaction was started before the -- procedure was called. DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 -- Procedure called when there is -- an active transaction. -- Create a savepoint to be able -- to roll back only the work done -- in the procedure if there is an -- error. SAVE TRANSACTION ProcedureSave; ELSE -- Procedure must start its own -- transaction. BEGIN TRANSACTION; -- Modify database. BEGIN TRY DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMIT TRANSACTION; END TRY BEGIN CATCH -- An error occurred; must determine -- which type of rollback will roll -- back only the work done in the -- procedure. IF @TranCounter = 0 -- Transaction started in procedure. -- Roll back complete transaction. ROLLBACK TRANSACTION; ELSE -- Transaction started before procedure -- called, do not roll back modifications -- made before the procedure was called. IF XACT_STATE() <> -1 -- If the transaction is still valid, just -- roll back to the savepoint set at the -- start of the stored procedure. ROLLBACK TRANSACTION ProcedureSave; -- If the transaction is uncommitable, a -- rollback to the savepoint is not allowed -- because the savepoint rollback writes to -- the log. Just return to the caller, which -- should roll back the outer transaction. -- After the appropriate rollback, echo error -- information to the caller. DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH GO Thank you
From: Erland Sommarskog on 5 May 2010 18:19 trubar a (asponmynet(a)gmail.com) writes: > 1) �Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than 1 > simply decrements @@TRANCOUNT by 1.� > > I assume calling rollback �inside� a nested transaction rolls back the > entire transaction and not just the nested one? Yes. > a) Does the above quote imply that transaction_name parameters are > always used just for readability? Personally, I see little reason to name transaction - unless you use the very special WITH MARK > b) But doesn�t the following quote imply that transaction_name > parameters are not used just for readability: > > �If a transaction is rolled back to a savepoint, it must proceed to > completion with more Transact-SQL statements if needed and a COMMIT > TRANSACTION statement, or it must be canceled altogether by rolling > the transaction back to its beginning. To cancel an entire > transaction, use the form ROLLBACK TRANSACTION transaction_name. All > the statements or procedures of the transaction are undone.� I don't think that is correct. To rollback to the beginning, ROLLBACK TRANSACTION is sufficient. > c) Anyways, I thought simply calling ROLLBACK / ROLLBACK TRANSACTION > will roll the entire transaction to its beginning, even if ROLLBACK is > called �inside� nested transaction? If so, why would we ever need to > call ROLLBACK TRANSACTION transaction_name instead of just calling > ROLLBACK? The one situation you name a name after ROLLBACK TRANSACTION is when you rollback to a *savepoint*. When you do this, you are still in a transaction. > The above code is an excerpt from the SQL code I�ve posted at the > bottom of this post. In any case, code shown in this excerpt calls > COMMIT TRANSACTION only if @TranCounter variable is equal to 0 > ( which means no transaction was started before the procedure was > called). I'm �n even more hurry now, but I'd say the example is hopelessly overworked. While you can use savepoints, you don't have to. In my over 15 years with SQL Server, I think I've used SAVE TRANSACTION twice in production code. -- 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: Sylvain Lafontaine on 5 May 2010 18:57 Most of your questions have already been answered by Erland, so I just want to add a little explanation for the use of @TranCounter in the code example. When you call a stored procedure, the transaction count when exiting the SP must be the same as it was when calling the SP. This means that if a transaction is already open when calling this SP, then you cannot perform a rollback inside this SP because the rollback will set the transaction count to 0 and therefore, the transaction count will be different when returning from the SP and will generate an error. This is why they are testing for the value @@TRANCOUNT at the beginning of the SP: this is for determining if the SP has been called from inside a running (open) transaction or not. If not, then the code start a transaction and close it (commit or rollback) at the end of the SP; if yes, then either you do nothing (you don't open a new transaction and you don't make any commit or rollback at the end) or you create a saved point; which can be seen as a mini-nested-transaction. This is the easiest way of dealing with the problem of calling a SP from inside an open transaction or not. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "trubar a" <asponmynet(a)gmail.com> wrote in message news:9f49d502-bb9c-4ce9-868d-13ed27a777f6(a)b18g2000yqb.googlegroups.com... Hi Sorry for putting so many questions into one thread, but in my opinion questions are somewhat related. If it�s a problem, I can break the questions into several threads. 1) �Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than 1 simply decrements @@TRANCOUNT by 1.� I assume calling rollback �inside� a nested transaction rolls back the entire transaction and not just the nested one? 2) One of the optional arguments of BEGIN TRANSACTION statement is also transaction_name parameter: BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] [ ; ] �Although each COMMIT TRANSACTION statement has a transaction_name parameter, there is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure that the proper number of commits are coded to decrement @@TRANCOUNT to 0 and thereby commit the outer transaction.� a) Does the above quote imply that transaction_name parameters are always used just for readability? b) But doesn�t the following quote imply that transaction_name parameters are not used just for readability: �If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone.� c) Anyways, I thought simply calling ROLLBACK / ROLLBACK TRANSACTION will roll the entire transaction to its beginning, even if ROLLBACK is called �inside� nested transaction? If so, why would we ever need to call ROLLBACK TRANSACTION transaction_name instead of just calling ROLLBACK? 3) BEGIN TRY DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMIT TRANSACTION; END TRY The above code is an excerpt from the SQL code I�ve posted at the bottom of this post. In any case, code shown in this excerpt calls COMMIT TRANSACTION only if @TranCounter variable is equal to 0 ( which means no transaction was started before the procedure was called). But doesn�t this checking only make sense if calling COMMIT TRANSACTION would commit the entire transaction, even if @TranCounter was greater than 0 ( @TranCounter being greater than 0 means that @@TRANCOUNT > 1)? But as far as I know, if @@TRANCOUNT is greater than 1, then all that happens is that @@TRANCOUNT gets decremented by 1 ( thus transaction doesn�t get committed ). The following quote also suggests this: �When used in nested transactions, commits of the inner transactions do not free resources or make their modifications permanent. The data modifications are made permanent and resources freed only when the outer transaction is committed. Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than 1 simply decrements @@TRANCOUNT by 1. When @@TRANCOUNT is finally decremented to 0, the entire outer transaction is committed.� Example is taken from http://msdn.microsoft.com/en-us/library/ms188378.aspx : USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.objects WHERE name = N'SaveTranExample') DROP PROCEDURE SaveTranExample; GO CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS -- Detect if the procedure was called -- from an active transaction and save -- that for later use. -- In the procedure, @TranCounter = 0 -- means there was no active transaction -- and the procedure started one. -- @TranCounter > 0 means an active -- transaction was started before the -- procedure was called. DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 -- Procedure called when there is -- an active transaction. -- Create a savepoint to be able -- to roll back only the work done -- in the procedure if there is an -- error. SAVE TRANSACTION ProcedureSave; ELSE -- Procedure must start its own -- transaction. BEGIN TRANSACTION; -- Modify database. BEGIN TRY DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMIT TRANSACTION; END TRY BEGIN CATCH -- An error occurred; must determine -- which type of rollback will roll -- back only the work done in the -- procedure. IF @TranCounter = 0 -- Transaction started in procedure. -- Roll back complete transaction. ROLLBACK TRANSACTION; ELSE -- Transaction started before procedure -- called, do not roll back modifications -- made before the procedure was called. IF XACT_STATE() <> -1 -- If the transaction is still valid, just -- roll back to the savepoint set at the -- start of the stored procedure. ROLLBACK TRANSACTION ProcedureSave; -- If the transaction is uncommitable, a -- rollback to the savepoint is not allowed -- because the savepoint rollback writes to -- the log. Just return to the caller, which -- should roll back the outer transaction. -- After the appropriate rollback, echo error -- information to the caller. DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH GO Thank you
From: trubar a on 6 May 2010 10:41 hi On May 5, 3:19 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > trubar a (asponmy...(a)gmail.com) writes: > > > a) Does the above quote imply that transaction_name parameters are > > always used just for readability? > > Personally, I see little reason to name transaction - unless you use > the very special WITH MARK > So except when used with WITH MARK, transaction_name doesnt do anything ( ) but just help in readability > >The above code is an excerpt from the SQL code Ive posted at the > >bottom of this post. In any case, code shown in this excerpt calls > >COMMIT TRANSACTION only if @TranCounter variable is equal to 0 > >( which means no transaction was started before the procedure was > >called). > >I'm ín even more hurry now, but I'd say the example is hopelessly >overworked. While you can use savepoints, you don't have to. In my over >15 years with SQL Server, I think I've used SAVE TRANSACTION twice >in production code. > Uhm, but how else if not via savepoints could you cancel out (via rollback) only certain commands/changes? On May 5, 3:57 pm, "Sylvain Lafontaine" <sylvainlafontaine2...(a)yahoo.ca> wrote: > Most of your questions have already been answered by Erland, so I just want > to add a little explanation for the use of @TranCounter in the code example. > When you call a stored procedure, the transaction count when exiting the SP > must be the same as it was when calling the SP. This means that if a > transaction is already open when calling this SP, then you cannot perform a > rollback inside this SP because the rollback will set the transaction count > to 0 and therefore, the transaction count will be different when returning > from the SP and will generate an error. > a) Assuming transaction is already opened when we call SP and assuming that we set a transaction savepoint inside this SP, then we are allowed to rollback (inside that SP) to that savepoint, but Im assuming were not allowed to rollback to a savepoint defined outside this SP? b) Sorry for being repetitive, but again assuming transaction is already opened, then trying to call COMMIT inside SP will also set the transaction count to 0 and cause an error? And for that reason SP also checked whether @TranCounter equals zero (IF @TranCounter = 0) before calling COMMIT: BEGIN TRY DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMIT TRANSACTION; END TRY c) Anyways, again assuming transaction is already opened when we call SP, why couldnt/shouldnt transaction begin outside SP and end inside SP ( end inside SP either via rollback or commit )? thanx
From: Erland Sommarskog on 6 May 2010 18:08
trubar a (asponmynet(a)gmail.com) writes: > So except when used with WITH MARK, transaction_name doesn�t do > anything ( ) but just help in readability ....or to add confusion. Although, see below. >>I'm �n even more hurry now, but I'd say the example is hopelessly >>overworked. While you can use savepoints, you don't have to. In my over >>15 years with SQL Server, I think I've used SAVE TRANSACTION twice >>in production code. > > Uhm, but how else if not via savepoints could you cancel out (via > rollback) only certain commands/changes? Correct, for that savepoints is the only option. Now: how often do you really want to do it? OK, *in theory* the answer is always. Say that you have a procedure A that performs a piece of work, and defines a user-defined transaction and if the A fails, it rolls back. But what if A is called from an outer procedure B - and when you wrote A you did know whether that will happen. If A performs a rollback, it would pull the rug for B as well. But for B, A may be part of a bigger plot, and B may want to continue with the rest of its work. So from this point of view, it would be better if A checked if there was an active transaction and in such case did a SAVE TRANSACTION instead of BEGIN TRANSACTION, and in case of ROLLBACK, rolled back to the savepoint. If you have the same name for the savepoint as in BEGIN TRANSACION, you only need a single ROLLBACK. (So there is some use for the name anyway.) But there are two problems here: 1) The code is complex and error-prone. 2) Lot's of errors in SQL Server causes the transaction to be "doomed"; that is, if there is no TRY-CATCH, the batch and transaction is aborted directly. If there is a CATCH handler, you can only roll it back entirely. So I would say that in SQL Server you would only do it, if you really need it. As I said, I've used SAVE TRANSACTION twice in production code. In the first case it was resolve a catch-22 situation. In the second case it was just like the scenario above. But I had to roll back that change, beause the code was also called from a linked server - and you cannot have savepoints in distributed transactions. > a) Assuming transaction is already opened when we call SP and assuming > that we set a transaction savepoint inside this SP, then we are > allowed to rollback (inside that SP) to that savepoint, but I�m > assuming we�re not allowed to rollback to a savepoint defined outside > this SP? That should work. The caller could pass the name of the savepoint in a variable. (But I have not tested.) > b) Sorry for being repetitive, but again assuming transaction is > already opened, then trying to call COMMIT inside SP will also set the > transaction count to 0 and cause an error? And for that reason SP also > checked whether @TranCounter equals zero (IF @TranCounter = 0) before > calling COMMIT: @TranCounter is the value of @@trancount when the procedure is entered, so the code is OK. You commit, only if you started the transaction yourself. > c) Anyways, again assuming transaction is already opened when we call > SP, why couldn�t/shouldn�t transaction begin outside SP and end inside > SP ( end inside SP either via rollback or commit )? If @@trancount has a different value when the procedure is exited, this raises error 266, so committed the caller's procedure is not good. Rolling it back, also produce the error, but there are situations when you have no other choice. -- 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 |