Should I rollback my explicit transaction or will XACT_ABORT do it for me?

Here is another question I got in this week. The question is all about rolling back transactions, whether we need to write a code with ROLLBACK TRAN for rolling back or can SQL Server automatically rollback the transaction if XACT_ABORT is on.

Many believe that once the transaction is handled using TRY/CATCH block, rolling back is guaranteed when an error or unexpected issue is thrown. Most of the cases, this is true, when something goes wrong, execution jumps to Catch block and execute the rollback tran statement. But what if execution is unable to jump to Catch block? Can it happen? Yes, it is possible. Assume that logic is implemented with a stored procedure and it is executed by an application. While it is being executed, if something happens at the application-end, application disconnects from the database. This stops continuing the code and executing the rollback tran. Not only that, this results the transaction remain in the database, keeping all the locked placed without releasing.

This type of issues can be solved using XACT_ABORT statement. All we have to do is, add SET XACT_ABORT ON inside the stored procedure because XACT_ABORT makes sure all transactions are rolled back and the batch is aborted when an error occured. For more info, read : https://msdn.microsoft.com/en-us/library/ms188792.aspx

Does this mean that we do not need to have TRY/CATCH block and handling errors? No, we still need them because it is the way of capturing errors and handling them.

Here is an example. This creates a table and stored procedure for inserting records.

Code 1

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. DROP TABLE dbo.Account  
5.   
6. CREATE TABLE dbo.Account  
7. (     
8.  AccountId INT NOT NULL PRIMARY KEY,   
9.  Name    NVARCHAR (50) NOT NULL,  
10.  Balance Money NOT NULL     
11. )  
12. GO  
13.   
14. CREATE PROCEDURE dbo.InsertAccount  
15. AS  
16. BEGIN  
17.   
18.  BEGIN TRY  
19.   
20.   BEGIN TRAN  
21.   
22.   INSERT INTO dbo.Account(AccountId, Name , Balance)   
23.   VALUES(1, 'Account1', 10000)  
24.   -- This throws an error  
25.   INSERT INTO dbo.Account(AccountId, Name , Balance)   
26.   VALUES(1, 'Duplicate', 10000)  
27.   INSERT INTO dbo.Account(AccountId, Name , Balance)   
28.   VALUES(2, 'Account2', 20000)  
29.    
30.   COMMIT TRAN  
31.  END TRY  
32.  BEGIN CATCH  
33.   SELECT ERROR_MESSAGE()  
34.   ROLLBACK TRAN  
35.  END CATCH  
36. END  
37. GO  

USE tempdb;
GO

DROP TABLE dbo.Account

CREATE TABLE dbo.Account
(   
 AccountId INT NOT NULL PRIMARY KEY, 
 Name    NVARCHAR (50) NOT NULL,
 Balance Money NOT NULL   
)
GO

CREATE PROCEDURE dbo.InsertAccount
AS
BEGIN

 BEGIN TRY

  BEGIN TRAN

  INSERT INTO dbo.Account(AccountId, Name , Balance) 
  VALUES(1, 'Account1', 10000)
  -- This throws an error
  INSERT INTO dbo.Account(AccountId, Name , Balance) 
  VALUES(1, 'Duplicate', 10000)
  INSERT INTO dbo.Account(AccountId, Name , Balance) 
  VALUES(2, 'Account2', 20000)
 
  COMMIT TRAN
 END TRY
 BEGIN CATCH
  SELECT ERROR_MESSAGE()
  ROLLBACK TRAN
 END CATCH
END
GO

Now let's open a new connection and execute the stored procedure.

Code 2

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. EXEC dbo.InsertAccount;  
5.   
6. SELECT * FROM dbo.Account;  

USE tempdb;
GO

EXEC dbo.InsertAccount;

SELECT * FROM dbo.Account;

Here is the result.

As expected it throws an error. if you check for active transactions using below code, you should NOT see the transaction we started as it has been handled.

Code 3

view plainprint?

1. SELECT   
2.        est.transaction_id as TransactionID,  
3.        tas.name as [Transaction Name],  
4.        DB_NAME(tds.database_id) as DatabaseName  
5. FROM sys.dm_tran_active_transactions tas   
6.  INNER JOIN sys.dm_tran_database_transactions tds   
7.   ON tas.transaction_id = tds.transaction_id  
8.  INNER JOIN sys.dm_tran_session_transactions est   
9.   ON est.transaction_id=tas.transaction_id  
10. WHERE est.is_user_transaction = 1   
11. AND tas.transaction_state = 2   
12. AND tds.database_transaction_begin_time IS NOT NULL  

SELECT 
       est.transaction_id as TransactionID,
       tas.name as [Transaction Name],
       DB_NAME(tds.database_id) as DatabaseName
FROM sys.dm_tran_active_transactions tas 
 INNER JOIN sys.dm_tran_database_transactions tds 
  ON tas.transaction_id = tds.transaction_id
 INNER JOIN sys.dm_tran_session_transactions est 
  ON est.transaction_id=tas.transaction_id
WHERE est.is_user_transaction = 1 
AND tas.transaction_state = 2 
AND tds.database_transaction_begin_time IS NOT NULL

Now let's make a small modification to the stored procedure and execute the code in a different way. Insert WAITFOR statement in between first and second INSERT statements like below and alter the procedure.

Code 4

view plainprint?

1. INSERT INTO dbo.Account(AccountId, Name , Balance)   
2. VALUES(1, 'Account1', 10000)  
3.   
4. WAITFOR DELAY '00:00:20';  
5.   
6. INSERT INTO dbo.Account(AccountId, Name , Balance)   
7. VALUES(1, 'Duplicate', 10000)  
8. INSERT INTO dbo.Account(AccountId, Name , Balance)   
9. VALUES(2, 'Account2', 20000)  

  INSERT INTO dbo.Account(AccountId, Name , Balance) 
  VALUES(1, 'Account1', 10000)

  WAITFOR DELAY '00:00:20';

  INSERT INTO dbo.Account(AccountId, Name , Balance) 
  VALUES(1, 'Duplicate', 10000)
  INSERT INTO dbo.Account(AccountId, Name , Balance) 
  VALUES(2, 'Account2', 20000)

Now open a new connection and execute Code 2 again. Open another connection immediately and execute Code 3 too. You should see following record with Code 3.

Go back to Code 2 immediately and stop execution. You need make sure you do this within 20 seconds. Once you have stopped the code, go back to Code 3 and execute it again. You should see the transaction which is still exist.

Same can happen with an application too. We stopped the application but still the transaction is exist with the database, locking some resources. Let's make another modification to the stored procedure and test the same. Add following code to the procedure and recreate it.

Code 5

view plainprint?

1. ALTER PROCEDURE dbo.InsertAccount  
2. AS  
3. BEGIN  
4.   
5.  SET XACT_ABORT ON  
6.  BEGIN TRY  
7.   
8.   BEGIN TRAN  

ALTER PROCEDURE dbo.InsertAccount
AS
BEGIN

 SET XACT_ABORT ON
 BEGIN TRY

  BEGIN TRAN

Now do the same thing, open a new connection, execute the Code 2, and with a different connection, execute the Code 3, go back to Code 2, stop it and execute Code 3 again. You should NOT see the transaction now because it has been taken care by XACT_ABORT.