Does SET XACT_ABORT ON do anything in a stored procedure if you're NOT in a transaction?

4
votes

Does SET XACT_ABORT ON do anything in a stored procedure if you're NOT in a transaction?

I'm asking as my stored procedure appears to be somehow rolling back an insert statement even thought the part of the stored procedure which is failing is NOT in a transaction. The Identity of the table is being pushed up but the inserted rows are not there after the stored procedure returns, despite the fact the insert is unconditional and not inside a transaction.

Or is there any other reason why SQL Server would roll back an insert outside of a transaction?

1
sqlsql-servertsqlstored-procedurestransactions
All inserts are inside a transaction. If you don't run it inside an explicit transaction SQL Server will start a new one for that statement. – Martin Smith
But if the insert completed successfully, what mechanism might make it roll back? – NickG

1 Answers

4
votes

SET XACT_ABORT ON changes the behavior of statement-terminating errors so that they instead become batch-aborting errors. The batch within which your procedure is executing would be more likely to stop completely when an error was encountered, rather than continuing on to the next T-SQL statement.

This has implications for error-handling. Statement-terminating errors which would normally allow you to continue and execute handling code via a IF @@ERROR <> 0 block within your procedure will not be executed. What's worse, there is no way you can intercept batch-abortion in T-SQL code, so unless there is a check on @@ERROR immediately in a following batch on the same connection, you might not know there was a problem.

Perhaps your INSERT is failing quietly in some way that still allows the IDENTITYseed to be incremented. This is not uncommon...identity values reside in the SQL Server memory cache, the volatility of which provides no guarantee that those values be continuous and without gaps.

Alternatively, the calling context of the stored procedure matters. If called from within an outer-scope transaction, either initiated by SQL Server or at the application level, then a rollback at that outer scope will rollback the inner scope work, regardless of explicit transaction handling code at the inner scope. Again, the IDENTITY seed would be incremented.

TRY/CATCH blocks (available since SQL Server 2005) obviate the need for SET XACT_ABORT ON.