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.

http://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure — zloctb, Jul 07 '15 at 16:55

score 258 · Accepted Answer · edited Mar 28 '12 at 21:37

258

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

edited Mar 28 '12 at 21:37

Greg B

14,597
18
87
141

answered Nov 17 '09 at 15:47

1

Will this work on MS SQL 2K and higher? This seems the most simple solution. – jonathanpeppers Nov 17 '09 at 15:49
1

It appears in the docs for 2000, 2005, and 2008 so I assume yes. We are using it in 2008. – Nov 17 '09 at 15:54
Is there an option to rollback on a network timeout or network errors in general? Or does this option handle all in one? – jonathanpeppers Nov 17 '09 at 16:04
This will handle run-time sql errors only. Quassnoi has already answered the network connection question. – Nov 17 '09 at 16:14
10

Do I need to turn it off or is it per session? – Marc Sep 03 '12 at 15:52
1

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' – jaraics Oct 05 '12 at 07:44
7

@Marc the scope of `xact_abort` is at the connection level. – Keith Apr 11 '14 at 15:38
@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. – Alex McMillan Feb 27 '17 at 20:23
2

@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. – eksortso Jun 07 '17 at 19:18
2

@eksortso you are incorrect. That is valid for Oracle, which auto commits before DDL command. SQL Server supports DDL transactions. – Bogdan Mart Jan 11 '21 at 13:50

score 245 · Answer 2 · edited May 10 '21 at 06:59

245

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

edited May 10 '21 at 06:59

SQL Police

4,127
1
25
54

answered Nov 17 '09 at 15:46

Raj More

47,048
33
131
198

3

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. – jonathanpeppers Nov 17 '09 at 15:52
18

The try catch gives you the ability to capture (and possibly fix) the error and raise a custom error message if required. – Raj More Nov 17 '09 at 15:55
Since we don't really care to handle the error specifically within SQL, I think we're going with DyingCactus's solution. – jonathanpeppers Nov 17 '09 at 16:07
12

"Capture and log" more frequently than "capture and fix", I'd think. – quillbreaker Jul 19 '12 at 23:54
31

The syntax of RAISERROR is incorrect at least in SQL Server 2008R2 and later. See http://msdn.microsoft.com/en-us/library/ms178592.aspx for correct syntax. – Eric J. Jul 16 '13 at 03:48
1

Why do you need to add the condition `IF @@TRANCOUNT > 0`? – BornToCode Jul 06 '15 at 20:46
3

@BornToCode 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`. – Gabriel GM Aug 18 '15 at 13:27
1

An example of the correct syntax is at https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017#a-returning-error-information-from-a-catch-block – chillNZ Jul 31 '18 at 23:49
1

You can use a simple THROW; instead of RAISERROR and ERROR_* declarations. – rodzmkii Oct 18 '19 at 11:12
2

This original answer is from 2009. The way of raising error messages is outdated meanwhile. I edited the answer to the latest standard. – SQL Police May 10 '21 at 07:01
@jonathanpeppers Another reason for `try-catch`: ["Compile errors, such as syntax errors, are not affected by SET XACT_ABORT."](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver15) – Leponzo Sep 09 '21 at 18:36
1

LOL I wrote this question over a decade ago. I haven’t written much SQL at my job at Microsoft of late. – jonathanpeppers Oct 20 '21 at 00:37

score 59 · Answer 3 · edited Oct 23 '19 at 05:57

59

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

edited Oct 23 '19 at 05:57

Justin Loveless

524
1
10
25

answered Apr 27 '17 at 05:04

samwise

1,907
1
21
24

1

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. – jtlindsey May 04 '17 at 13:26
1

You can use a simple THROW; instead of RAISERROR and ERROR_* declarations. – rodzmkii Oct 18 '19 at 11:12
While we could use 'set xact_abort on' this allows more control in the event and is far more readable. – Joe Johnston Oct 03 '20 at 15:43

score 28 · Answer 4 · edited Jul 23 '13 at 10:34

28

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.

edited Jul 23 '13 at 10:34

default locale

13,035
13
56
62

answered Jul 23 '13 at 10:09

Vitaly

310
3
6

3

what do we need to handle syntax errors? or compile errors? if anyone of them happens whole transaction should be rolled back – Furkan Gözükara Aug 17 '16 at 11:12
Catching compilation/syntax errors are what SSDT projects are for. :-) – Joe the Coder May 03 '19 at 14:21

score 14 · Answer 5 · answered Nov 17 '09 at 15:45

14

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.

answered Nov 17 '09 at 15:45

Quassnoi

413,100
91
616
614

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? – jonathanpeppers Nov 17 '09 at 15:47
2

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. – Quassnoi Nov 17 '09 at 16:04
1

So DyingCactus's solution looks like it fixes my issue, thanks for the help. – jonathanpeppers Nov 17 '09 at 16:06
1

If you need to abort on **any** error, then yes, this is the best option. – Quassnoi Nov 17 '09 at 16:11

score 0 · Answer 6 · answered Aug 14 '23 at 21:17

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

SQL Server - transactions roll back on error?

6 Answers6

Linked

Related