SQL Server - transactions roll back on error?

Question

We have client app that is running some SQL on a SQL Server 2005 such as the following:

BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;

It is sent by one long string command.

If one of the inserts fail, or any part of the command fails, does SQL Server roll back the transaction? If it does not rollback, do I have to send a second command to roll it back?

I can give specifics about the api and language I'm using, but I would think SQL Server should respond the same for any language.

Grammatical

#1150532 – Izzard

Answer 1

You can put set xact_abort on before your transaction to make sure sql rolls back automatically in case of error.

Niels

Will this work on MS SQL 2K and higher? This seems the most simple solution. – Grammatical

It appears in the docs for 2000, 2005, and 2008 so I assume yes. We are using it in 2008. – Niels

Is there an option to rollback on a network timeout or network errors in general? Or does this option handle all in one? – Grammatical

This will handle run-time sql errors only. Quassnoi has already answered the network connection question. – Niels

Do I need to turn it off or is it per session? – Hummocky

For this particular case it works, but XACT_ABORT is not a cure for all disease. The following will fail SET XACT_ABORT ON EXEC sp_executesql N'some eroneus statement' SELECT 'Shouldn''t see this' – Stubbed

@Hummocky the scope of xact_abort is at the connection level. – Maynor

@btberry That appears the opposite of what I'm seeing: set xact_abort on, begin trans, drop procedure, error. The procedure is not restored as the transaction is not rolled back. – Amphitryon

@AlexMcMillan The DROP PROCEDURE statement modifies the database structure, unlike INSERT, which just works with the data. So it cannot be wrapped in a transaction. I'm oversimplifying, but basically that's how it is. – Aceydeucy

@Aceydeucy you are incorrect. That is valid for Oracle, which auto commits before DDL command. SQL Server supports DDL transactions. – Packsaddle

Answer 2

You are correct in that the entire transaction will be rolled back. You should issue the command to roll it back.

You can wrap this in a TRY CATCH block as follows

BEGIN TRY
    BEGIN TRANSACTION

        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);

    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- <EDIT>: From SQL2008 on, you must raise error messages as follows:
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    -- </EDIT>
END CATCH

Lavery

I like DyingCactus's solution better, his is 1 line of code to change. If yours if for some reason better (or more reliable) let me know. – Grammatical

The try catch gives you the ability to capture (and possibly fix) the error and raise a custom error message if required. – Lavery

Since we don't really care to handle the error specifically within SQL, I think we're going with DyingCactus's solution. – Grammatical

"Capture and log" more frequently than "capture and fix", I'd think. – Brierwood

The syntax of RAISERROR is incorrect at least in SQL Server 2008R2 and later. See msdn.microsoft.com/en-us/library/ms178592.aspx for correct syntax. – Stopoff

Why do you need to add the condition IF @@TRANCOUNT > 0? – Partida

@Partida To make sure the transaction exist.. Lets say you have rolled back your transaction under given condition (in the try), but the code fails after. There are no more transaction, but you're still going into the catch. – Zoller

An example of the correct syntax is at learn.microsoft.com/en-us/sql/t-sql/language-elements/… – Criminate

You can use a simple THROW; instead of RAISERROR and ERROR_* declarations. – Seel

This original answer is from 2009. The way of raising error messages is outdated meanwhile. I edited the answer to the latest standard. – Kisumu

@Grammatical Another reason for try-catch: "Compile errors, such as syntax errors, are not affected by SET XACT_ABORT." – Telemark

LOL I wrote this question over a decade ago. I haven’t written much SQL at my job at Microsoft of late. – Grammatical

Answer 3

Here the code with getting the error message working with MSSQL Server 2016:

BEGIN TRY
    BEGIN TRANSACTION 
        -- Do your stuff that might fail here
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN

        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
        DECLARE @ErrorState INT = ERROR_STATE()

    -- Use RAISERROR inside the CATCH block to return error  
    -- information about the original error that caused  
    -- execution to jump to the CATCH block.  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Ratepayer

I had to use DECLARE @Var TYPE; SET @Var = ERROR; for raise errors in sql server 2005. Otherwise the above code for raising errors works for older DB's too. Trying to assign a default value to a local variable is what was causing issue. – Plumbo

You can use a simple THROW; instead of RAISERROR and ERROR_* declarations. – Seel

While we could use 'set xact_abort on' this allows more control in the event and is far more readable. – Quintie

Answer 4

From MDSN article, Controlling Transactions (Database Engine).

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. For more information, see SET XACT_ABORT (Transact-SQL).

In your case it will rollback the complete transaction when any of inserts fail.

Circe

what do we need to handle syntax errors? or compile errors? if anyone of them happens whole transaction should be rolled back – Harrietharriett

Catching compilation/syntax errors are what SSDT projects are for. :-) – Gerrilee

Answer 5

If one of the inserts fail, or any part of the command fails, does SQL server roll back the transaction?

No, it does not.

If it does not rollback, do I have to send a second command to roll it back?

Sure, you should issue ROLLBACK instead of COMMIT.

If you want to decide whether to commit or rollback the transaction, you should remove the COMMIT sentence out of the statement, check the results of the inserts and then issue either COMMIT or ROLLBACK depending on the results of the check.

Amharic

So if I get an error, say "Primary key conflict" I need to send a second call to rollback? I guess that makes sense. What happens if there is a network-related error such as the connection is severed during a very long running SQL statement? – Grammatical

When a connection times out, the underlying network protocol (e. g. Named Pipes or TCP) breaks the connection. When a connection is broken, SQL Server stops all currently running commands and rollbacks the transaction. – Amharic

So DyingCactus's solution looks like it fixes my issue, thanks for the help. – Grammatical

If you need to abort on any error, then yes, this is the best option. – Amharic

Answer 6

Throwing this out as an alternative method, you can also capture the error number after each statement and then use an if statement to determine if to commit or rollback. The accepted answer is the best one-liner, but if you want to know more about what issue you're getting rather than just rolling it back you can use the example below and add some additional information for seeing the issue. Of course, you could also do a Try-Catch block with RAISERROR.

Here's an example of what I have as just something quick:

DECLARE @errorNumber int;

BEGIN TRANSACTION;
    INSERT INTO [table2] ([field1], [field2])
    SELECT [fieldA], [fieldB]
    FROM [table1];
        
    SET @errorNumber = @@ERROR;

    UPDATE [table3]
    SET [field1] =
        (SELECT COUNT(ID)
        FROM [table2]
        WHERE [table2].[fieldA] = [table3].[field2])
    WHERE [field1] IS NULL;
        
    SET @errorNumber = @@ERROR;

IF @errorNumber = 0
    COMMIT TRANSACTION;
ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT CONCAT('Transaction rolled back with error number: ',@errorNumber);
    END

Meanie