Example of SET XACT_ABORT ON in SQL Server by serverku

We have seen one error The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION and seen workaround too. There is related to transaction mismatch and now I am writing further same with different error and an issue.

In that post, if I remove one column of used tables in those SPs. What will happen? It will raise an error and keep the transaction open. Let us check.

-- Creating table which will be used in SPs.
CREATE TABLE tbl_Tran
  (
     TranId    INT NOT NULL PRIMARY KEY
     ,TranName VARCHAR(10)
  )
 
GO
 
-- Altering first stored procedure here
CREATE PROCEDURE Firttranproc
AS
  BEGIN
	SET NOCOUNT ON
    
   -- Here we have specified Tran1 as transaction name
  
   BEGIN TRY
          BEGIN TRANSACTION
 
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
    
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran1'
 
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Altering second stored procedure here
CREATE PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON
 
      BEGIN TRY
          BEGIN TRANSACTION
 
    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'
 
          -- Calling first stored procedure here
          EXEC Firttranproc
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran2'
 
     
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO

 -- Droping one column to raise an complite type error 
ALTER TABLE tbl_Tran
DROP COLUMN TranName
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO
Msg 207, Level 16, State 1, Procedure Secondtranproc, Line 13
Invalid column name 'TranName'.
Msg 266, Level 16, State 2, Procedure Secondtranproc, Line 13
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

This will raise a compiled type error and keep transaction open which you can get details using the following script,

USE master
GO

SELECT 
       est.session_id as [Session ID],
       est.transaction_id as [Transaction ID],
       tas.name as [Transaction Name],
       tds.database_id as [Database ID]
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 -- user
       AND tas.transaction_state = 2 -- active
       AND tas.transaction_begin_time IS NOT NULL
GO    
/*
Output :

Session ID  Transaction ID   Transaction Name  Database ID
----------- ---------------- ----------------- -----------
54          176426           user_transaction  19

*/

You can see the open transaction details above. For a fox for such issue we should use SET XACT_ABORT ON in the beginning of the stored procedures. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. So let's change stored procedures,

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
	SET NOCOUNT ON
        SET XACT_ABORT ON
   -- Here we have specified Tran1 as transaction name
  
   BEGIN TRY
          BEGIN TRANSACTION
 
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
    
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran1'
 
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
          SET NOCOUNT ON
	  SET XACT_ABORT ON
 
      BEGIN TRY
          BEGIN TRANSACTION
 
    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'
 
          -- Calling first stored procedure here
          EXEC Firttranproc
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran2'
 
     
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO

It will raise a run-time error, but does not keep the transaction open. Please execute stored procedures and check for open transaction using query provided above. This is just what I want to share with you. I would like to put your comments.