Sql try/catch rollback/commit - предотвращение ошибочного фиксации после отката

Я пытаюсь написать MS sql script, который имеет транзакцию и блок try/catch. Если он получает исключение, транзакция откатывается. Если нет, транзакция совершается. Я видел несколько разных сайтов, говорящих так:

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
end catch

commit transaction

Но разве мы не будем ударять по строке "commit transaction" даже в случае обнаружения исключения? Не приведет ли это к ошибке SQL, потому что транзакция уже откатна? Я думаю, это должно быть сделано следующим образом:

declare @success bit = 1

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
    set @success = 0
end catch

if(@success = 1)
begin
    commit transaction
end

Как правило, общедоступное решение не включает переменную @success? Нет ли ошибки sql, которая происходит в результате совершения транзакции, которая уже откатна? Я неверно говорю, что строка "совершить транзакцию" первого примера кода будет по-прежнему ударяться в случае обнаружения исключения?

Ответ 1

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

PRINT 'BEFORE TRY'
BEGIN TRY
    BEGIN TRAN
     PRINT 'First Statement in the TRY block'
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1',  10000)
     UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) WHERE AccountId = 1
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2',  20000)
     PRINT 'Last Statement in the TRY block'
    COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT 'In CATCH Block'
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN

    THROW; -- raise error to the client
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

Ответ 2

В вашем первом примере вы правы. Партия ударит транзакцию фиксации, независимо от того, срабатывает ли блок try.

В вашем втором примере я согласен с другими комментаторами. Использование флага успеха необязательно.

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

Если вы хотите увидеть, как он обрабатывает исключение, измените значение на второй вставке с 255 на 256.

CREATE TABLE #TEMP ( ID TINYINT NOT NULL );
INSERT  INTO #TEMP( ID ) VALUES  ( 1 )

BEGIN TRY
    BEGIN TRANSACTION

    INSERT  INTO #TEMP( ID ) VALUES  ( 2 )
    INSERT  INTO #TEMP( ID ) VALUES  ( 255 )

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    DECLARE 
        @ErrorMessage NVARCHAR(4000),
        @ErrorSeverity INT,
        @ErrorState INT;
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (
        @ErrorMessage,
        @ErrorSeverity,
        @ErrorState    
        );
    ROLLBACK TRANSACTION
END CATCH

SET NOCOUNT ON

SELECT ID
FROM #TEMP

DROP TABLE #TEMP

Ответ 3

Ниже может быть полезно.

Источник: https://msdn.microsoft.com/en-us/library/ms175976.aspx

BEGIN TRANSACTION;

BEGIN TRY
    -- your code --
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Ответ 4

Счетчик транзакций

[email protected]@TRANCOUNT = 0
begin try
[email protected]@TRANCOUNT = 0
BEGIN TRANSACTION tran1
 [email protected]@TRANCOUNT = 1

        --your code
        -- if failed  @@TRANCOUNT = 1
        -- if success @@TRANCOUNT = 0

COMMIT TRANSACTION tran1

end try

begin catch
    print 'FAILED'
end catch