Why is part of transaction committed when stored procedure faults?

Question

I have a stored procedure that begins by declaring a few variables then contains begin tran; After this it performs some validations on the supplied arguments (and increments an error count each time a supplied argument fails validation). If there is no error count it then proceeds to carry out 7 inserts. Following this, it has commit tran;

Recently I added an 8th insert to the list. An implicit type conversion meant that some inserted data would be truncated if inserted. This threw an error to the SSMS screen but I found the first 7 inserts had committed while the 8th obviously didn’t complete.

I appreciate that I could include a try ... catch block to handle errors but if an explicit begin tran; doesn’t make the whole block of work autonomous down to the commit, then what is the point? What have I missed?

I get that I perhaps could have wrapped my procedure call in a transaction at that level – but can someone please explain what is going on and why the begin tran appears disrespected when included within the procedure body? If calling the procedure begins an implicit transaction, then shouldn’t a faulting step in the proc roll back all changes being effected by the proc – even without explicitly including begin tran in the proc body?

asked 2021-04-25 by youcantryreachingme

Answer

Transactions don’t automatically roll back on error–that’s not what they are designed to do. They are designed to give you the ability to rollback. However, you still need to do something to make that happen.

As you mention, you can make that happen through TRY...CATCH, which gives you the most control over if and how you can rollback.

It sounds like you are expecting the behavior of SET XACT_ABORT ON, which you can set in your stored procedure, but is not the default behavior. The description of setting XACT_ABORT on vs off from the docs is:

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

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.

When SET XACT_ABORT is ON, calling a faulting procedure returns messages in SSMS like:

The first line is in red in SSMS.

When SET XACT_ABORT is OFF, calling a faulting procedure includes one additional message:

In particular, when you see the following message, it means statements before the faulting statement within the procedure have been committed:

answered 2021-04-26 by Andy Mallon