What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?

What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?

回答1

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. It covers you in cases like a command timeout occurring on the client application rather than within SQL Server itself (which isn't covered by the default XACT_ABORT OFF setting.)

Since a query timeout will leave the transaction open, SET XACT_ABORT ON is recommended in all stored procedures with explicit transactions (unless you have a specific reason to do otherwise) as the consequences of an application performing work on a connection with an open transaction are disastrous.

There's a really great overview on Dan Guzman's Blog,

Error and Transaction Handling in SQL Server

Part One – Jumpstart Error Handling

SET XACT_ABORT ON

Your stored procedures should always include this statement in the beginning:

SET XACT_ABORT, NOCOUNT ON

This turns on two session options that are off by default for legacy reasons, but experience has proven that best practice is to always have them on. The default behaviour in SQL Server when there is no surrounding TRY-CATCH is that some errors abort execution and roll back any open transaction, whereas with other errors execution continues on the next statement. When you activate XACT_ABORT ON, almost all errors have the same effect: any open transaction is rolled back and execution is aborted. There are a few exceptions of which the most prominent is the RAISERROR statement.

The option XACT_ABORT is essential for a more reliable error and transaction handling. Particularly, with the default behaviour there are several situations where execution can be aborted without any open transaction being rolled back, even if you have TRY-CATCH. We saw one such example in the previous section where we learnt that TRY-CATCH does not catch compilations errors in the same scope. An open transaction which is not rolled back in case of an error can cause major problems if the application jogs along without committing or rolling back.

For good error handling in SQL Server, you need both TRY-CATCH and SET XACT_ABORT ON. Of these two, SET XACT_ABORT ON is the most important. For production-grade code it's not really sufficient to rely on XACT_ABORT, but for quick and simple stuff it can do.

The option NOCOUNT has nothing to do with error handling, but I included in order to show best practice. The effect of NOCOUNT is that it suppresses messages like (1 row(s) affected) that you can see in the Message tab in SQL Server Management Studio. While these row counts can be useful when you work interactively in SSMS, they can degrade performance in an application because of the increased network traffic. The row counts can also confuse poorly written clients that think they are real result sets.

Above, I've used a syntax that is a little uncommon. Most people would probably write two separate statements:

SET NOCOUNT ON
SET XACT_ABORT ON

There is no difference between this and the above. I prefer the version with one SET and a comma since it reduces the amount of noise in the code. As these statements should appear in all your stored procedures, they should take up as little space as possible.

Use Caution with Explicit Transactions in Stored Procedures

Using SET XACT_ABORT

SET XACT_ABORT specifies what action SQL Server should take following run-time errors. The default session setting is SET XACT_ABORT OFF, which indicates that only the Transact-SQL statement that raised the error is rolled back and the transaction continues. Depending on the severity of the error, the entire transaction may be rolled back and batch aborted, even with SET XACT_ABORT is OFF.

A side effect of SET XACT_ABORT OFF is that a cancel/timeout error can leave an open transaction so it’s the client’s responsibility to cleanup following cancel/timeout. To safeguard against leaving an open transaction, applications that execute transactions with SET XACT_ABORT OFF need to roll back transactions and perhaps close the connection following SQL exceptions.

Note that with connection pooling, simply closing the connection without a rollback will only return the connection to the pool and the transaction will remain open until later reused or removed from the pool. This can result in locks begin held unnecessary and cause other timeouts and rolling blocks.

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. Compile errors (e.g. syntax errors) are not affected by SET XACT_ABORT.

In my experience, SET XACT_ABORT ON provides the desired behavior in most cases. I’ve never run into a situation where I wouldn’t want to rollback a transaction following a cancel or timeout.   I nearly always specify SET XACT_ABORT ON in stored procedures that contain explicit transactions to ensure that transactions are rolled back even if the application code doesn’t clean up properly. The only time I don’t use XACT_ABORT is in rare cases where I need to trap and handle specific errors in Transact-SQL and continue.

I strongly recommend that SET XACT_ABORT ON be included in all stored procedures with explicit transactions unless you have a specific reason to do otherwise. The consequences of an application unwittingly performing work on a connection with an open transaction are disastrous.

SQL Server error handling in general is a huge topic I focused on only on timeout errors and SET XACT_ABORT here. For a thorough discussion of SQL Server error handling, I suggest perusing articles Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background by SQL Server MVP Erland Sommarskog.