Share via

SQL Server: SET xact_abort Vs Try Catch

  • Article

OVERVIEW

A few days back we witnessed an issue that the SQL Server was running slow. We investigated and soon found out that there were uncommitted transactions on the server. We re-confirmed those transactions from the developers and resolved the issue. Someone tossed the idea to apply SET  XACT_ABORT ON in all SPs so that this situation may not occur again. This article was written to explain the pros and cons of SET XACT_ABORT ON option.

SET XACT_ABORT ON

As many of you already know this option takes the whole batch as one transaction. If any of the statement in the batch fails, the transaction of the whole batch is rolled back. Commits in case of success. Simple. But not that simple. You cannot control the flow of transaction with if XACT_ABORT is ON. TRY .. CATCH will not work. Even if you have the error log is implemented in a CATCH block, it will not be saved as it will also get rolled back. So, DB end error log is gone. In some cases, you may need to continue with the rest of your code in case of an error, for example, return a dataset. You cannot do this as well. The plus point you will never have an open transaction on your server. All transactions will either commit or rollback.

Another way to handle an open transaction issue is to apply TRY .. CATCH in all SPs. There are few limitations of TRY .. CATCH. TRY .. CATCH does not catch the schema related errors. It will return an error without hitting CATCH block where you have rolled back opened transaction in case of a schema error (table does not exist, etc ). Check the code below;

BEGIN TRY 
 -- Table does not exist; object name resolution 
 -- error not caught. 
 begin Tran
 SELECT * FROM NonexistingTable;
 commit
 END TRY 
 BEGIN CATCH 
 rollback
 
SELECT
 ERROR_NUMBER() AS  ErrorNumber 
 ,ERROR_MESSAGE() AS  ErrorMessage; 
 END CATCH 
   
 GO  
 Select @@TRANCOUNT

Execute the above statements. You will end up with an opened transaction.

You can also try this in a stored procedure. You will still end up in an open transaction.

Now add SET XACT_ABORT ON;

SET XACT_ABORT ON
 BEGIN TRY 
 -- Table does not exist; object name resolution 
 -- error not caught. 
 begin Tran
 SELECT * FROM NonexistingTable;
 commit
 END TRY 
 BEGIN CATCH 
 rollback
 SELECT
 ERROR_NUMBER() AS  ErrorNumber 
 ,ERROR_MESSAGE() AS  ErrorMessage; 
 END CATCH 
   
 GO  
 Select @@TRANCOUNT

Your transaction will be rolled back and you will find no open transaction.

CONCLUSION

XACT_ABORT ON solves the open transaction issue but at the cost of logging implemented in the CATCH block (if you have any).

But "NonexistingTable" type of issues should not be in the production environment. Like why will it deploy an SP using a table that does not exist? Strange thing. You should have QA in place and your deployment scripts should be verified before running in the production environment.