Search This Blog

Thursday, February 2, 2017

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.

1 comment: