XACT_ABORT and Transaction

Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

The THROW statement honors SET XACT_ABORT. RAISERROR does not. New applications should use THROW instead of RAISERROR.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.

When ANSI_WARNINGS=OFF, permissions violations cause transactions to abort.

The setting of SET XACT_ABORT is set at execute or run time and not at parse time.

The procedure and it’s not encapsulated in a transaction and is using RAISEERROR to alert calling entity of the error, but this might be an issue since by default XACT_ABORT is OFF and only rolls back the statement that raised the error (in this case the second insert). The recommendation is to use a combination of SET XACT_ABORT ON and transactions to achieve desired results, Please check on the links. Also look into using the THROW functionality instead of RAISEERROR.

Using Try-Catch

create PROCEDURE insert_rain
   @rain_time DATETIME ,
   @location VARCHAR(100)
AS
BEGIN;
   SET XACT_ABORT ON;
   BEGIN TRANSACTION;
   PRINT 'before insert';

   BEGIN TRY
      insert INTO rain
         ( rain_time, location )
         values  ( @rain_time, @location );
      PRINT 'after insert';

      COMMIT;
   end try
   begin catch;
      if @@trancount > 0
         rollback;
      throw;
   end catch;
END;
go

Examples

The following code example causes a foreign key violation error in a transaction that has other Transact-SQL statements. In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed. In the second set of statements, SET XACT_ABORT is set to ON. This causes the statement error to terminate the batch and the transaction is rolled back.SQLCopy

IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO  
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF; -- By Default
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1); -- Inserted into table
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3); -- Inserted into table
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4); -- Rolled back
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6); -- Never reached here.
COMMIT TRANSACTION;
GO
-- SELECT shows only keys 1 and 3 added.
-- Key 2 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
-- Key 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
SELECT *
 FROM t2;
GO

Result Set 1

(1 row affected)
 Msg 547, Level 16, State 0, Line 23
 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__t2__a__5EBFDEF4". The conflict occurred in database "TCS", table "dbo.t1", column 'a'.
 The statement has been terminated.
 (1 row affected)
 Completion time: 2021-05-04T15:54:16.5849725-07:00

Result Set 2

(1 row affected)
 Msg 547, Level 16, State 0, Line 31
 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__t2__a__5AEF4E10". The conflict occurred in database "TCS", table "dbo.t1", column 'a'.
 Completion time: 2021-05-04T15:53:10.2075095-07:00