none
Transaction

    Question

  • Hi Everyone,

    I am using the following piece of code. What happens when the code encounters an error  after the update statement? Would SQL Server automatically roll it back? If not, would raiserror execute after rollback? Please advise.

    DECLARE @CaptureErrorCode INT
    DECLARE @IDs INT
    BEGIN TRANSACTION
    UPDATE DW.dbo.Test SET DW.dbo.Test.Lost = 1
    FROM DW.dbo.Test WHERE ID IN(@IDs)
    SET @CaptureErrorCode = @@ERROR
    IF (@CaptureErrorCode <> 0) GOTO RollTransactionBack
    
    RollTransactionBack:
    IF (@CaptureErrorCode <> 0)
    BEGIN
    	Rollback Transaction
    	RAISERROR ('Error Message’, 16, 1)
    END
    

    Saturday, March 03, 2012 1:45 PM

Answers

  • Thanks Dan - There is another INSERT statement so I would need an explicit transaction. Can you please let me know where I should put raiserror. In case of an error, would SQL Server automatically rollback transaction before reaching the IF statement?
    Do I need to use XACT_ABORT or riaseerror is good enough?

    The reason I suggested XACT_ABORT ON is to address errors that cannot be trapped in T-SQL.  Specifically, an attention sent from the client (due to a query timeout or cancel) will stop the remainder of the code from executing and possibly leave an open transaction.

    RAISERROR isn't needed here unless you use strunctured error handling (TRY/CATCH).  Without TRY/CATCH, SQL Server will automatically return errors messages to the client.  XACT_ABORT ON will also terminate the batch and rollback the transaction after most errors even without error handling code.  However, it's still a good practice to include error handling code to be safe.

    Here is an example of a batch with unstructured error handling:

    SET XACT_ABORT ON;
    DECLARE @CaptureErrorCode INT;
    DECLARE @IDs INT;
    BEGIN TRANSACTION;
    UPDATE dbo.Test SET dbo.Test.Lost = 1
    FROM dbo.Test WHERE ID = @IDs;
    
    SET @CaptureErrorCode = @@ERROR;
    IF @CaptureErrorCode <> 0 GOTO RollTransactionBack;
    
    --other code here
    
    COMMIT
    
    RollTransactionBack:
    IF @CaptureErrorCode <> 0
    BEGIN
    	ROLLBACK TRANSACTION;
    END
    GO

    With TRY/CATCH, you can omit error checking after each statement and include only happy path code in the TRY block.  In the catch block, rollback the transaction (if needed) and finally raise the error.  Importantly, TRY/CATCH will hide the error from the client so it is important to raise an error in the CATCH block in cases where the calling application needs to know something went wrong.  Strunctured error handling example:

    SET XACT_ABORT ON;
    DECLARE @IDs INT;
    BEGIN TRY
    
    	BEGIN TRANSACTION
    
    	UPDATE dbo.Test SET dbo.Test.Lost = 1
    	FROM dbo.Test WHERE ID = @IDs;
    
    	--other code here
    
    	COMMIT
    END TRY
    BEGIN CATCH
    	DECLARE @errmsg   nvarchar(2048),
    			@severity tinyint,
    			@state    tinyint,
    			@errno    int,
    			@proc     sysname,
    			@lineno   int;
    
              
    	SELECT @errmsg = error_message(), 
    			@severity = error_severity(),
    			@state  = error_state(),
    			@errno = error_number(),
    			@proc   = error_procedure(),
    			@lineno = error_line();
    
    	IF @@TRANCOUNT > 0 ROLLBACK;
    	--raiserror is needed to return error to client
    	RAISERROR('Msg %d, Line %d: %s', @severity, @state, @errno, @lineno, @errmsg);
    
    END CATCH
    GO

    I should add that SQL Server 2012 adds a new THROW statement to simplify structured error handling code:

    SET XACT_ABORT ON;
    DECLARE @IDs INT;
    BEGIN TRY
    
    	BEGIN TRANSACTION
    
    	UPDATE dbo.Test SET dbo.Test.Lost = 1
    	FROM dbo.Test WHERE ID = @IDs;
    
    	--other code here
    
    	COMMIT
    END TRY
    BEGIN CATCH
    	IF @@TRANCOUNT > 0 ROLLBACK;
    	--return error to client
    	THROW;
    
    END CATCH
    GO


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, March 05, 2012 1:38 PM

All replies

  • Yes, this should roll back if an error occurs. However:

     -@@ERROR and GOTO should be avoided in programming. Consider using TRY...CATCH instead:

    Using TRY...CATCH

     - Rollback rolls back anythigns since the first "Begin Transaction". Since you can nest transcation seven layers deep, this could affect a lot more. But that is usually intended.

     - the best place for the commit (for this transaction level) would be right behind the GOTO.

     - technically Error values under 11 are not errors, but warnings wich should not be caught. See this older article:
    http://www.sommarskog.se/error-handling-I.html

     - try to include information like the value of the ErrorCode in your Message.
    Saturday, March 03, 2012 2:53 PM
  • Thanks Christopher,

    I want the code to fail when the roll back happens. Should I place the raiseerror after the IF statement?

    • Edited by PeaceOut Sunday, March 04, 2012 1:20 AM
    Saturday, March 03, 2012 3:11 PM
  • More important than aborting the code when the transaction rolls back, is to rolling back the transaction if an error happens.

    One simple way to make sure that the code aborts and the transaction rolls back is using XACT_ABORT:
    http://msdn.microsoft.com/en-us/library/ms188792.aspx

    It's the blunt tool for aborting an transaction and indeed the entire batch if any error happens. There are some detail about what it does not catches in the article above (only the Errors you raise yourself are important).

    RAISEERROR should be last, at it will cancel the batch* (nothing past it will be executed) and I am not certain what happens with Transactions if the batch cancels.

    *unless caught by a try...catch. In that case it works like a goto to the CATCH block. RAISEERROR and TRY...CATCH work similar to how "throw new Exception()" and try...catch work in C#, C++ and other C-style higher languages.

    Saturday, March 03, 2012 4:12 PM
  • Hi Everyone,

    I am using the following piece of code. What happens when the code encounters an error  after the update statement? Would SQL Server automatically roll it back? If not, would raiserror execute after rollback? Please advise.

    There is no need for an explicit transaction here because individual statements are always rolled back in the event of a run time error.  SQL Server will return an error message if the UPDATE fails and no data will be changed.

    In the case of multiple statements, an explict transaction is needed to guarantee all-or-none behavior. 


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Saturday, March 03, 2012 5:07 PM
  • Thanks Dan - There is another INSERT statement so I would need an explicit transaction. Can you please let me know where I should put raiserror. In case of an error, would SQL Server automatically rollback transaction before reaching the IF statement?
    Do I need to use XACT_ABORT or riaseerror is good enough?

    • Edited by PeaceOut Sunday, March 04, 2012 1:36 AM
    Sunday, March 04, 2012 1:24 AM
  • Anyone please?
    Sunday, March 04, 2012 11:46 PM
  • Check the sample of using transactions and error handling in this SP

    How to insert information into multiple related tables and return ID using SQLDataSource

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 05, 2012 12:30 AM
  • I re-read the thematic. using Raiseerror will jump to the nearest catch block (the next on up in the nestign hirarchy). If there is none, it jumps directly to the end of your code ignoring everything and the error is given as the result of the query to whoever called this script (your application, Sever Managemetn Studio).

    Your application will propably throw an exception in response, while Management studio simply shows you the message (in red).

    So most importanly: Nothing standing behind a RAISEERROR will ever be executed!

    Monday, March 05, 2012 12:04 PM
  • Thanks Dan - There is another INSERT statement so I would need an explicit transaction. Can you please let me know where I should put raiserror. In case of an error, would SQL Server automatically rollback transaction before reaching the IF statement?
    Do I need to use XACT_ABORT or riaseerror is good enough?

    The reason I suggested XACT_ABORT ON is to address errors that cannot be trapped in T-SQL.  Specifically, an attention sent from the client (due to a query timeout or cancel) will stop the remainder of the code from executing and possibly leave an open transaction.

    RAISERROR isn't needed here unless you use strunctured error handling (TRY/CATCH).  Without TRY/CATCH, SQL Server will automatically return errors messages to the client.  XACT_ABORT ON will also terminate the batch and rollback the transaction after most errors even without error handling code.  However, it's still a good practice to include error handling code to be safe.

    Here is an example of a batch with unstructured error handling:

    SET XACT_ABORT ON;
    DECLARE @CaptureErrorCode INT;
    DECLARE @IDs INT;
    BEGIN TRANSACTION;
    UPDATE dbo.Test SET dbo.Test.Lost = 1
    FROM dbo.Test WHERE ID = @IDs;
    
    SET @CaptureErrorCode = @@ERROR;
    IF @CaptureErrorCode <> 0 GOTO RollTransactionBack;
    
    --other code here
    
    COMMIT
    
    RollTransactionBack:
    IF @CaptureErrorCode <> 0
    BEGIN
    	ROLLBACK TRANSACTION;
    END
    GO

    With TRY/CATCH, you can omit error checking after each statement and include only happy path code in the TRY block.  In the catch block, rollback the transaction (if needed) and finally raise the error.  Importantly, TRY/CATCH will hide the error from the client so it is important to raise an error in the CATCH block in cases where the calling application needs to know something went wrong.  Strunctured error handling example:

    SET XACT_ABORT ON;
    DECLARE @IDs INT;
    BEGIN TRY
    
    	BEGIN TRANSACTION
    
    	UPDATE dbo.Test SET dbo.Test.Lost = 1
    	FROM dbo.Test WHERE ID = @IDs;
    
    	--other code here
    
    	COMMIT
    END TRY
    BEGIN CATCH
    	DECLARE @errmsg   nvarchar(2048),
    			@severity tinyint,
    			@state    tinyint,
    			@errno    int,
    			@proc     sysname,
    			@lineno   int;
    
              
    	SELECT @errmsg = error_message(), 
    			@severity = error_severity(),
    			@state  = error_state(),
    			@errno = error_number(),
    			@proc   = error_procedure(),
    			@lineno = error_line();
    
    	IF @@TRANCOUNT > 0 ROLLBACK;
    	--raiserror is needed to return error to client
    	RAISERROR('Msg %d, Line %d: %s', @severity, @state, @errno, @lineno, @errmsg);
    
    END CATCH
    GO

    I should add that SQL Server 2012 adds a new THROW statement to simplify structured error handling code:

    SET XACT_ABORT ON;
    DECLARE @IDs INT;
    BEGIN TRY
    
    	BEGIN TRANSACTION
    
    	UPDATE dbo.Test SET dbo.Test.Lost = 1
    	FROM dbo.Test WHERE ID = @IDs;
    
    	--other code here
    
    	COMMIT
    END TRY
    BEGIN CATCH
    	IF @@TRANCOUNT > 0 ROLLBACK;
    	--return error to client
    	THROW;
    
    END CATCH
    GO


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, March 05, 2012 1:38 PM