XACT_ABORT V/S TRY .. CATCH

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. That made me to write this article so that I can explain the pros and cons of SET XACT_ABORT ON option.

SET XACT_ABORT ON

As many of you already know that this options just takes the whole batch a 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 CATCH block, it will not be saved as it will also get rolled backed. 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. Ok, I know. There are few limitations of TRY .. CATCH. TRY .. CATCH do 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 exists, 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.

Bow 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 CATCH block (if you have any).

But “NonexistingTable” type of issues should not be in the production environment. Like why will I deploy a SP using a table that does not exists? Strange thing for me. You should have QA in place and your deployment scripts should be verified before running in the production environment.

 You decide.