SAVE TRANSACTION vs BEGIN TRANSACTION (SQL Server), как хорошо вложить транзакции

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

Дело в том, что этот Stored Procedure (SP) вызывается другими. Поэтому я не знаю, начали ли они транзакцию или нет... Даже если мне нужно, чтобы пользователи начали транзакцию для использования моего SP, у меня остались вопросы о правильном использовании Save Points...

Мой SP проверяет, выполняется ли транзакция, а если нет, запустите один с BEGIN TRANSACTION. Если транзакция уже выполняется, она вместо этого создаст точку сохранения с помощью SAVE TRANSACTION MySavePointName и сохранит тот факт, что это то, что я сделал.

Тогда, если мне нужно отменить мои изменения, если раньше я сделал BEGIN TRANSACTION, тогда я буду ROLLBACK TRANSACTION. Если бы я сделал точку сохранения, тогда я буду ROLLBACK TRANSACTION MySavePointName. Этот сценарий отлично работает.

Вот где я немного запутался - если я хочу сохранить работу, которую я сделал, если бы я начал транзакцию, я выполнил бы COMMIT TRANSACTION. Но если бы я создал точку сохранения? Я попробовал COMMIT TRANSACTION MySavePointName, но затем вызывающий пытается выполнить транзакцию и получает сообщение об ошибке:

Запрос COMMIT TRANSACTION не имеет соответствующей BEGIN TRANSACTION.

Итак, мне интересно - точка сохранения может быть отброшена (что работает: ROLLBACK TRANSACTION MySavePointName НЕ откат транзакции вызывающего абонента). Но, возможно, никогда не нужно "совершать" это? Он просто остается там, если вам нужно вернуться к нему, но уходит, как только первоначальная транзакция совершена (или откат)?

Если есть "лучший" способ "вложить" транзакцию, пожалуйста, прольйте также немного света. Я не понял, как вставить с BEGIN TRANSACTION, но только откат или фиксацию моей внутренней транзакции. Кажется, что ROLLBACK всегда будет возвращаться к верхней транзакции, а COMMIT просто уменьшает @@trancount.

Ответ 1

Я считаю, что все это выдумал, поэтому я отвечу на свой вопрос...

Я даже написал свои выводы, если вы хотите получить более подробную информацию в http://geekswithblogs.net/bbiales/archive/2012/03/15/how-to-nest-transactions-nicely---quotbegin-transactionquot-vs-quotsave.aspx

Итак, мой SP начинается с чего-то вроде этого, чтобы начать новую транзакцию, если ее нет, но используйте Save Point, если она уже выполняется:

DECLARE @startingTranCount int
SET @startingTranCount = @@TRANCOUNT

IF @startingTranCount > 0
    SAVE TRANSACTION mySavePointName
ELSE
    BEGIN TRANSACTION
-- …

Затем, когда вы готовы к фиксации изменений, вам нужно только зафиксировать, если мы сами начали транзакцию:

IF @startingTranCount = 0
    COMMIT TRANSACTION

И, наконец, отменить только ваши изменения:

-- Roll back changes...
IF @startingTranCount > 0
    ROLLBACK TRANSACTION MySavePointName
ELSE
    ROLLBACK TRANSACTION

Ответ 2

Расширение ответ Брайана Б..

Это гарантирует уникальное имя точки сохранения и использует новые функции TRY/CATCH/THROW для SQL Server 2012.

DECLARE @mark CHAR(32) = replace(newid(), '-', '');
DECLARE @trans INT = @@TRANCOUNT;

IF @trans = 0
    BEGIN TRANSACTION @mark;
ELSE
    SAVE TRANSACTION @mark;

BEGIN TRY
    -- do work here

    IF @trans = 0
        COMMIT TRANSACTION @mark;
END TRY
BEGIN CATCH
    IF xact_state() = 1 OR (@trans = 0 AND xact_state() <> 0) ROLLBACK TRANSACTION @mark;
    THROW;
END CATCH

Ответ 3

Я использовал этот тип диспетчера транзакций в моих хранимых процедурах:

    CREATE PROCEDURE Ardi_Sample_Test  
        @InputCandidateID INT  
    AS  
        DECLARE @TranCounter INT;  
        SET @TranCounter = @@TRANCOUNT;  
        IF @TranCounter > 0  
            SAVE TRANSACTION ProcedureSave;  
        ELSE  
            BEGIN TRANSACTION;  
        BEGIN TRY  

            /*
            <Your Code>
            */

            IF @TranCounter = 0  
                COMMIT TRANSACTION;  
        END TRY  
        BEGIN CATCH  
            IF @TranCounter = 0  
                ROLLBACK TRANSACTION;  
            ELSE  
                IF XACT_STATE() <> -1  
                    ROLLBACK TRANSACTION ProcedureSave;  

            DECLARE @ErrorMessage NVARCHAR(4000);  
            DECLARE @ErrorSeverity INT;  
            DECLARE @ErrorState INT;  
            SELECT @ErrorMessage = ERROR_MESSAGE();  
            SELECT @ErrorSeverity = ERROR_SEVERITY();  
            SELECT @ErrorState = ERROR_STATE();  

            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
        END CATCH  
    GO