SQL error not rolling back entire SqlCommand

Question

I have been placing set xact_abort on into SQL command statements and noticed that it is not rolling back updates, inserts, etc in my C# SqlCommand on error. Taken from this post. The MSDN states that:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

The general format of my SQl query is:

set xact_abort on
INSERT INTO Table1
UPDATE Table2
UPDATE Table3
UPDATE Table4
--Finally
SELECT someValue

I noticed that on error my SQL command was not being rolled back. The particular error is this case was that the data length of one parameter exceeded the columns specified length. I am using SqlCommand and SqlParameter to create SQL queries.

I am not looking to handle the exceptions in SQL, but it is very important that any errors do not commit any changes to the database.

Typically errors include: column does not exist, wrong data type, data would be truncated due to length, etc.

Should I be using something other than set xact_abort on? Thanks in advance!

why not catch `SqlException` in your C# and rollback the transaction manually then? — Andrew, Feb 16 '17 at 23:23
According to the bottom of [this link](https://msdn.microsoft.com/en-us/library/ms188792.aspx), you still need to wrap the statements in calls to `BeginTransaction` and `EndTransaction`. Seems like it just saves you having to respond to errors and call `Rollback` on your own. — DonBoitnott, Feb 16 '17 at 23:24
If you are running multiple scripts another way to manage it would be [dbup](http://dbup.github.io). — lloyd, Feb 17 '17 at 00:13

score 3 · Accepted Answer · answered Feb 17 '17 at 06:11

The statement you quoted from MSDN is correct: with XACT_ABORT set to ON, any error will abort the batch and roll-back any active Transaction. The confusion here is that in SQL Server, each statement is a Transaction by itself by default. If you want multiple statements to be grouped together into an explicit Transaction, then you need to use BEGIN TRAN; along with COMMIT;. The following example illustrates this behavior:

Run this:

SET XACT_ABORT ON;
CREATE TABLE #Bob (ID INT);

INSERT INTO #Bob (ID) VALUES (1);

BEGIN TRAN;

INSERT INTO #Bob (ID) VALUES (2);
INSERT INTO #Bob (ID) VALUES (3);
INSERT INTO #Bob (ID) VALUES (4 / 0);

COMMIT TRAN;

Then run this separately (since the error in the statements above will abort the entire batch -- due to using XACT_ABORT ON -- and so the SELECT will never execute if you try to run the SELECT at the same time):

SELECT * FROM #Bob;

It will return a single row containing 1 since that statement executed by itself and not within the explicit Transaction. Once you add the BEGIN TRAN; and COMMIT TRAN; statements to your code, it will work as you are expecting it to.

Thank you! Wasn't even aware of the subtlety you mentioned and had not used Transactions in my SQL queries to date. I also now investigated some of the SQL transaction error handling which would be a great help verifying and troubleshooting multiple statement queries. — Matt, Feb 17 '17 at 17:13

score 1 · Answer 2 · answered Feb 17 '17 at 06:24

One pattern to deal with transactions from C# code is the following:

try
{
    var connection = new SqlConnection(connectionString);
    connection.Open();

    var trans = connection.BeginTransaction(); 

    using (var command = connection .CreateCommand())
    {
        command.Transaction = trans;
        command.CommandText = "...";
        command.ExecuteNonQuery();
    }

    // other commands may be defined here
    // command can be included or excluded from transaction (do not set Transaction property)

    // commits the transaction
    trans.Commit(); 
}
// best practice is to catch specific exception types like `SqlException`
catch (Exception ex) //error occurred
{
    trans.Rollback();
    // log error somewhere
}
finally
{
    // execute no-matter what
}

This pattern has the following advantages:

No need to worry about SET XACT_ABORT (OFF by default)
Better exception handling

Note: you may be interested in Unit of Work pattern

Thanks, wasn't even aware of SqlCommand Transactions! Even though it may not be the solution to my original question it may be the better approach seeing as I am using SqlCommands already. — Matt, Feb 17 '17 at 17:07
It is an answer to your _problem_, rather than your _question_. It is easier to understand what failed (exception management, logging) if you manage the transaction from C# code. Of course, if you insist on writing everything in SQL, accepted answer is fine. However, a best practice is to also use `try .. catch` block in SQL as indicated [here](http://stackoverflow.com/a/25147029/2780791). — Alexei - check Codidact, Feb 17 '17 at 17:14

SQL error not rolling back entire SqlCommand

2 Answers2