SQL: Why didn't my SQL Server transaction roll back on a primary key violation?

There’s a question that I hear all the time in SQL Server forums:

Why didn’t the transaction roll back when a primary key violation occurred?

Take a look at the code in the main image above. Would you expect it to execute the second and third INSERTs if the first INSERT failed with a primary key violation? So many people would. If you’re one of them, read on.

By default, that’s not how SQL Server works.

It might not be a primary key violation. It might be any number of other errors in the question, but the answer is still the same.

What’s going on?

The primary key violation is an example of what’s called a statement-terminating error. T-SQL data modification statements are atomic. If the INSERT was inserting three rows, none get inserted.

But only the statement ends, not the transaction.

If, instead, the error was a batch-terminating error, there’s a difference. SQL Server aborts the transaction and rolls it back.

How can I change that?

If you want to change that behaviour, put

SET XACT_ABORT ON;

at the start of your code.

It tells SQL Server to promote statement-terminating errors to batch-terminating errors. Once you do that, the transaction code above works as expected. The primary key violation aborts and rolls back the transaction.

But I’ve seen it work without that!

I also occasionally hear people say that they’ve seen a different outcome. They didn’t use XACT_ABORT and it still rolled back. What they’re usually missing is that their data access library has set it on for them.

You can tell if it’s on by executing this code:

SELECT CASE WHEN (16384 & @@OPTIONS) = 16384 
            THEN CAST(1 AS bit) 
            ELSE CAST(0 AS bit)
       END AS IsXctAbortOn;

Note: there are a few edge cases on this. Errors that relate to invalid object names, etc. are different. But for standard code, this is how it all works.

2020-05-07