All geek questions in one place

Does SET XACT_ABORT ON set anything in the stored procedure if you are NOT a transaction?

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

I ask how my stored procedure seems to somehow roll back the insert statement, even assuming that the part of the stored procedure that fails is NOT a transaction. Table identification is pushed up, but the inserted rows are not present after the stored procedure returns, despite the fact that the insert is unconditional, and not inside the transaction.

Or is there another reason SQL Server rolls back an insert outside a transaction?

+4
source share
1 answer

SET XACT_ABORT ON changes the behavior of errors that end with the application so that they instead become packet interrupt errors. The package in which your procedure runs is likely to stop completely when an error occurs, rather than continuing with the next T-SQL statement.

It matters for error handling. Errors with completion of the output, which usually allow you to continue and process the code using the IF @@ERROR <> 0 block inside your procedure, will not be executed. To make matters worse, you cannot intercept batch abortion in T-SQL code, so if you don’t check for @@ERROR immediately in the next batch in the same connection, you may not know that there was a problem.

Perhaps your INSERT is failing in some way, which allows you to increase the seed of IDENTITY . This is not uncommon ... identity values ​​are in the memory cache of SQL Server, whose volatility does not guarantee that these values ​​will be continuous without spaces.

Alternatively, the context of the stored procedure call makes sense. If it is called from an external visibility transaction initiated by SQL Server or at the application level, then the rollback in this external area rolls back the internal work of the area, regardless of the explicit transaction processing code in the internal area. Again, the seed of IDENTITY will increase.

Blocks

TRY/CATCH (available with SQL Server 2005) eliminates the need for SET XACT_ABORT ON .

+2
source

Source: https://habr.com/ru/post/1495915/

More articles:


All Articles