In this post I will explain Error handling concept with Try/Catch construct in SQL Code.
What happens when error occurred in a transaction ?
If you are not using Try/Catch construct and error occurred in an explicit transaction then transaction will be in one of two states.
- No open Transactions
- Active but committable transaction
If you are using Try/Catch construct and error occurred in the try block then transaction can be in third state, which is
- Active but uncommittable transaction (Failed State)
Active but uncommittable transaction (Failed State) :
In this transaction state, transaction will be in active state but you can't commit changes done in the transaction and also you can only read data, because to modify data in failed state, you first need to rollback failed transaction then apply modification statement in the new transaction.
Errors with a severity level of 17 and higher, but not 20 or higher, cause a transaction to enter this failed state. You can make all errors under severity 20 enter failed state by setting the XACT_ABORT session option to ON. In failed state, transaction keep locks on the resources it locked at the beginning of the transaction. Any time you can rollback the transaction and begin new transaction. You can use this state to query data, to investigate the reason of error. you should finish investigation as soon as possible and rollback transaction, because you are holding resources locked, preventing others from holding these resources.
Error Handling with transaction states :
Here I will explain error handling using XACT_STATE session option. XACT_STATE is a function that you invoke in the CATCH block to get the current transaction state. It returns 0 for no active transaction, 1 for active and committable, and –1 for active but uncommittable. Run following code for demonstration of Error handling using XACT_STATE session option.
Listing 1:
Use Tempdb;
CREATE TABLE MyTestTable(id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NULL);
GO
BEGIN TRY
BEGIN TRAN
INSERT INTO dbo.MyTestTable(id, name)
VALUES(1, 'Ashish');
/* other activity */
COMMIT TRAN
PRINT 'Code completed successfully.';
END TRY
BEGIN CATCH
PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' found.';
IF (XACT_STATE()) = -1
BEGIN
PRINT 'Transaction is open but uncommittable.';
/* ...investigate data... */
ROLLBACK TRAN; -- can only ROLLBACK
/* ...handle the error... */
END
ELSE IF (XACT_STATE()) = 1
BEGIN
PRINT 'Transaction is open and committable.';
/* ...handle error... */
COMMIT TRAN; -- or ROLLBACK
END
ELSE
BEGIN
PRINT 'No open transaction.';
/* ...handle error... */
END
END CATCH
This code inserts single row with id=1 into the MyTestTable table using an explicit transaction in the TRY block. In the CATCH block XACT_STATE() option is used to know the current state of transaction and execute statements based on current state. First time, when you run this code it executes successfully and you will get following output.
Error: 2627 found.
Transaction is open and committable.
Because the primary key violation is not treated as severe error, it neither completely terminates nor failed the transaction. Rather, the transaction remains open and committable. To see an example where the transaction fails, you can simply set XACT_ABORT to ON, and rerun the code in Listing 1:
SET XACT_ABORT ON;
-- run code in listing 10-3
SET XACT_ABORT OFF;
This time, you get the following output:
Error: 2627 found.
Transaction is open but uncommittable.
Hope you find this post good. If you have any questions or want to share your thoughts on this topic please write in the comment section.
