Do you need to call SET XACT_ABORT only when starting a transaction?

Question

I would like to get some clarification to this answer -> Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Below is the code sample from the above link

 1 CREATE PROCEDURE [Name]
 2 AS
 3 SET XACT_ABORT, NOCOUNT ON
 4
 5 DECLARE @starttrancount int
 6
 7 BEGIN TRY
 8    SELECT @starttrancount = @@TRANCOUNT
 9
10    IF @starttrancount = 0
11        BEGIN TRANSACTION
12
13       [...Perform work, call nested procedures...]
14
15    IF @starttrancount = 0 
16        COMMIT TRANSACTION
17 END TRY
19 BEGIN CATCH
20    IF XACT_STATE() <> 0 AND @starttrancount = 0 
21        ROLLBACK TRANSACTION
22    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
23 END CATCH
24 GO

Suppose it is called from the legacy stored procedure that starts a transaction. In this case this proc will not start transaction of its own, but will alter the XACT_ABORT state for the call.

So, I have a few questions here.

Is XACT_ABORT active for the current proc only, or for the whole call stack?
If I want to refactor a proc to use SET XACT_ABORT ON, do I need to pair it up with SET XACT_ABORT OFF? Is this the safest way to do it for legacy code?

Below is the modified sample that turns on XACT_ABORT conditionally and pairs it up with turning it off on a proc exit

CREATE PROCEDURE [Name]
AS
SET NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
   SELECT @starttrancount = @@TRANCOUNT

   IF @starttrancount = 0
   BEGIN
      SET XACT_ABORT ON
      BEGIN TRANSACTION
   END

      [...Perform work, call nested procedures...]

   IF @starttrancount = 0 
   BEGIN
      COMMIT TRANSACTION
      SET XACT_ABORT OFF
   END
END TRY
BEGIN CATCH
   IF XACT_STATE() <> 0 AND @starttrancount = 0 
   BEGIN
       ROLLBACK TRANSACTION
       SET XACT_ABORT OFF
   END
   RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

Martin Smith · Accepted Answer · 2015-09-22T20:00:24.207

As far as I can see when execution of a child batch ends it restores the previous value of this setting.

SET NOCOUNT ON;

BEGIN TRAN

SELECT CASE
         WHEN 16384 & @@OPTIONS = 16384
           THEN 'XACT_ABORT IS ON'
         ELSE ' XACT_ABORT IS OFF'
       END

CREATE TABLE #T
  (
     C INT
  )

SET XACT_ABORT OFF

INSERT INTO #T
VALUES      (1)

EXEC ('SET XACT_ABORT ON; 
      INSERT INTO #T VALUES(2);
        SELECT CASE
                    WHEN 16384 & @@OPTIONS = 16384
                    THEN ''XACT_ABORT IS ON''
                    ELSE '' XACT_ABORT IS OFF''
                END 
      ')


INSERT INTO #T
VALUES      (1 / 0)

/*If XACT_ABORT was on we would never get here but we do!*/
COMMIT

SELECT *
FROM   #T

DROP TABLE #T

SELECT CASE
         WHEN 16384 & @@OPTIONS = 16384
           THEN 'XACT_ABORT IS ON'
         ELSE ' XACT_ABORT IS OFF'
       END

Returns

------------------
 XACT_ABORT IS OFF


------------------
XACT_ABORT IS ON

Msg 8134, Level 16, State 1, Line 31
Divide by zero error encountered.
The statement has been terminated.
C
-----------
1
2


------------------
 XACT_ABORT IS OFF

To check, does this mean the below answer is incorrect (i.e. as to my reading Jimbo's answer is contradictory to this answer; yet both have upvotes... assuming yours is correct as you have significantly higher reputation). — JohnLBevan, Sep 22 '15 at 19:20

score 0 · Answer 2 · answered Apr 18 '11 at 19:33

Is XACT_ABORT active for the current proc only, or for the whole call stack? -- When set to ON, it is active from that point forward for all of that connection

If I want to refactor a proc to use SET XACT_ABORT ON, do I need to pair it up with SET XACT_ABORT OFF?
-- If that is the behavior you want, yes - and it means you don't get new behavior from legacy code.

Is this the safest way to do it for legacy code? -- To minimize the effect on other stored procedures, yes

score 0 · Answer 3 · answered Sep 21 '20 at 16:12

It sounds to me like it's scoped only to the Stored Procedure where it is SET, and reset after:

If a SET statement runs in a stored procedure or trigger, the value of the SET option gets restored after the stored procedure or trigger returns control. Also, if you specify a SET statement in a dynamic SQL string that runs by using either sp_executesql or EXECUTE, the value of the SET option gets restored after control returns from the batch that you specified in the dynamic SQL string.

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql?view=sql-server-ver15#considerations-when-you-use-the-set-statements

It's unclear to me from that page what the behavior in a nested proc is.

Do you need to call SET XACT_ABORT only when starting a transaction?

3 Answers3