Каково влияние включения / выключения XACT_ABORT в хранимых процедурах родитель / потомок соответственно?

Question

Я пытаюсь улучшить обработку ошибок текущей системы, чтобы производить более значимые сообщения об ошибках. У меня есть "корневая" хранимая процедура, которая выполняет несколько вызовов других вложенных хранимых процедур.

В корне зр, XACT_ABORT установлен в ON но во вложенных процедурах, XACT_ABORT установлен в OFF, Я хочу фиксировать конкретные ошибки процедур более низкого уровня, а не получать ошибку корневой процедуры.

Я часто вижу ошибку, uncommittable transaction is detected at the end of the batch, the transaction is being rolled back.

Есть ли какой-либо эффект от наличия этих "смешанных" сред с XACT_ABORTs?

Кроме того, если у вас есть какие-либо предложения по расширенной обработке ошибок, это будет высоко ценится. Я думаю, что я хотел бы использовать sp_executesql так что я могу передать параметры, чтобы получить вывод ошибок без необходимости изменять все хранимые процедуры и использовать RAISERROR вызвать родительскую процедуру CATCH блок.

Answer 1

Согласно ответу Андомара здесь и MSDN:

Настройка SET XACT_ABORT устанавливается во время выполнения или выполнения, а не во время анализа

т.е. XACT_ABORT не будет "копироваться" из сеанса создания в каждую процедуру, поэтому любой PROC, который явно не установит этот параметр внутри, будет наследовать этот параметр из окружающего сеанса во время выполнения, что может иметь катастрофические последствия.

FWIW, как правило, мы всегда гарантируем, что XACT_ABORT включен в глобальном масштабе и выполните проверку на предмет отсутствия дефектов, чтобы убедиться, что ни один из наших PROC не изменил этот параметр.

Обратите внимание, что XACT_ABORT однако, это не серебряная пуля - например, ошибки, которые были вызваны вашим PROC с RAISERROR, не прекратят выполнение пакета. Тем не менее, кажется, что это улучшается с помощью ключевого слова THROW в SQL 2012

Как вы и предполагали, и согласно наблюдению Ремуса Русану, структурированная обработка исключений (TRY / CATCH) является гораздо более чистым и надежным механизмом обработки исключений.

Answer 2

Способ сохранить XACT_ABORT и получать ошибки, если они есть, или совершать, если все нормально при вызове SP, который может вызвать другой SP: два теста sp и три в качестве примера

 create PROCEDURE [dbo].[myTestProcCalled]
    (
        @testin int=0
    )
    as
    begin

        declare @InnerTrans int 
        set XACT_ABORT on;



        set @InnerTrans = @@trancount;

        PRINT '02_01_Trancount='+cast (@InnerTrans as varchar(2));

        begin try
           if (@InnerTrans = 0)
           begin
                PRINT '02_02_beginning trans';
                begin transaction
           end 

           declare @t2 int
           set @t2=0
           PRINT '02_03_doing division'
           set @t2=10/@testin 

           PRINT '02_04_doing AfterStuff'
           if (@InnerTrans = 0 and XACT_STATE()=1)
             begin
                 PRINT '02_05_Committing'

                commit transaction
              end
              PRINT '02_05B_selecting calledValue=' +cast(@t2 as varchar(20))
              select  @t2 as insidevalue

        end try

        begin catch
         PRINT '02_06_Catching Errors from called'
              declare @ErrorMessage nvarchar(4000);
            declare @ErrorNumber int;
            declare @ErrorSeverity int;
            declare @ErrorState int;

            select @ErrorMessage = error_message(), @ErrorNumber = error_number(), @ErrorSeverity = error_severity(), @ErrorState = error_state();

            if (@InnerTrans = 0 and XACT_STATE()=-1)
            begin
              PRINT '02_07_Rolbacking'
              rollback transaction
            end
           PRINT '02_08_Rising Error'
            raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState); 
            --use throw if in 2012 or above 
            -- else might add a "return" statement
        end catch

    end
    go

    create PROCEDURE [dbo].[myTestPCalling]
    (
        @test int=0
        ,@testinside int=0
    )
    as
    begin

        declare @InnerTrans int 
        set XACT_ABORT on;



        set @InnerTrans = @@trancount;

        PRINT '01_01_Trancount='+cast (@InnerTrans as varchar(2));

        begin try
           if (@InnerTrans = 0)
           begin
                PRINT '01_02_beginning trans';
                begin transaction
           end 

           declare @t2 int
           set @t2=0
           PRINT '01_03_doing division'
           set @t2=10/@test 
           PRINT '01_04_calling inside sp'
           execute [dbo].[myTestProcCalled]
                  @testin  = @testinside
           --
           PRINT '01_05_doing AfterStuff'
           if (@InnerTrans = 0 and XACT_STATE()=1)
             begin
                 PRINT '01_06_Committing'
                 commit transaction
                 PRINT '01_06B_selecting callerValue=' +cast(@t2 as varchar(20))
              select  @t2 as outsidevalue

              end
        end try

        begin catch
         PRINT '01_07_Catching Errors from Caller'
            declare @ErrorMessage nvarchar(4000);
            declare @ErrorNumber int;
            declare @ErrorSeverity int;
            declare @ErrorState int;

            select @ErrorMessage = error_message(), @ErrorNumber = error_number(), @ErrorSeverity = error_severity(), @ErrorState = error_state();
            if (@InnerTrans = 0 and XACT_STATE()=-1)
            begin
              PRINT '01_08_Rolbacking'
              rollback transaction
            end
            PRINT '01_09_Rising Error'
            raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState); 
            --use throw if in 2012 or above 
            -- else might add a "return" statement
        end catch

    end

----test 1 :result OK----
USE [PRO-CGWEB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[myTestPCalling]
        @test =2
        ,@testinside = 2

SELECT  'Return Value' = @return_value

GO

----test2 :error in caller ----
USE [PRO-CGWEB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[myTestPCalling]
        @test =0
        ,@testinside = 2

SELECT  'Return Value' = @return_value

GO

----test3 :error in calling ----
USE [PRO-CGWEB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[myTestPCalling]
        @test =2
        ,@testinside = 0

SELECT  'Return Value' = @return_value

GO