通过

SAVE TRANSACTION （Transact-SQL）

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 数据库

在事务内设置保存点。

Transact-SQL 语法约定

Syntax

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
[ ; ]

Arguments

savepoint_name

分配给保存点的名称。 保存点名称必须符合标识符的规则，但长度不能超过 32 个字符。 savepoint_name 始终区分大小写，即使数据库引擎实例不区分大小写也是如此。

@savepoint_variable

包含有效保存点名称的用户定义变量的名称。 必须使用 char、varchar、nchar 或 nvarchar 数据类型声明该变量 。 可以向变量传递超过 32 个字符，但只使用前 32 个字符。

Remarks

可以在事务中设置保存点。 保存点定义在有条件地取消事务的一部分时，事务可以返回的一致性状态。 如果事务回滚到保存点，则必须根据需要继续完成更多 Transact-SQL 语句和 COMMIT TRANSACTION 语句，或者必须完全取消事务，方法是将事务回滚到其开头。 若要取消整个事务，请使用窗体 ROLLBACK TRANSACTION transaction_name。 这将撤消事务的所有语句和过程。

事务中允许重复保存点名称，但 ROLLBACK TRANSACTION 指定保存点名称的语句仅使用该名称将事务回滚到最新的 SAVE TRANSACTION 事务。

SAVE TRANSACTION 分布式事务不支持从本地事务显式 BEGIN DISTRIBUTED TRANSACTION 启动或从本地事务升级。

注释

数据库引擎不支持独立管理的嵌套事务。 内部事务的提交会递 @@TRANCOUNT 减，但没有任何其他影响。 内部事务的回滚始终回滚外部事务，除非 存在保存点 并在语句中 ROLLBACK 指定。

锁定行为

指定ROLLBACK TRANSACTIONsavepoint_name的语句释放除升级和转换的锁外获取的任何锁。 这些锁不会释放，它们不会转换回以前的锁定模式。

Permissions

要求具有 public 角色的成员身份。

Examples

本文中的代码示例使用 AdventureWorks2025 或 AdventureWorksDW2025 示例数据库，可以从 Microsoft SQL Server 示例和社区项目 主页下载该数据库。

下面的示例演示如何使用事务保存点仅在执行存储过程之前启动事务时回滚存储过程所做的修改。

IF EXISTS (SELECT name FROM sys.objects
           WHERE name = N'SaveTranExample')
    DROP PROCEDURE SaveTranExample;
GO

CREATE PROCEDURE SaveTranExample
    @InputCandidateID INT
AS
-- Detect whether the procedure was called
-- from an active transaction and save
-- that for later use.
-- In the procedure, @TranCounter = 0
-- means there was no active transaction
-- and the procedure started one.
-- @TranCounter > 0 means an active
-- transaction was started before the
-- procedure was called.
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;

IF @TranCounter > 0
    -- Procedure called when there is
    -- an active transaction.
    -- Create a savepoint to be able
    -- to roll back only the work done
    -- in the procedure if there is an
    -- error.
    SAVE TRANSACTION ProcedureSave;
ELSE
    -- Procedure must start its own
    -- transaction.
    BEGIN TRANSACTION;
-- Modify database.
BEGIN TRY
    DELETE HumanResources.JobCandidate
        WHERE JobCandidateID = @InputCandidateID;
    -- Get here if no errors; must commit
    -- any transaction started in the
    -- procedure, but not commit a transaction
    -- started before the transaction was called.
    IF @TranCounter = 0
        -- @TranCounter = 0 means no transaction was
        -- started before the procedure was called.
        -- The procedure must commit the transaction
        -- it started.
        COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- An error occurred; must determine
    -- which type of rollback will roll
    -- back only the work done in the
    -- procedure.
    IF @TranCounter = 0
        -- Transaction started in procedure.
        -- Roll back complete transaction.
        ROLLBACK TRANSACTION;
    ELSE
        -- Transaction started before procedure
        -- called, do not roll back modifications
        -- made before the procedure was called.
        IF XACT_STATE() <> -1
            -- If the transaction is still valid, just
            -- roll back to the savepoint set at the
            -- start of the stored procedure.
            ROLLBACK TRANSACTION ProcedureSave;
            -- If the transaction is uncommitable, a
            -- rollback to the savepoint is not allowed
            -- because the savepoint rollback writes to
            -- the log. Just return to the caller, which
            -- should roll back the outer transaction.

    -- After the appropriate rollback, return error
    -- information to the caller.
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();

    RAISERROR (
              @ErrorMessage, -- Message text.
              @ErrorSeverity, -- Severity.
              @ErrorState -- State.
              );
END CATCH
GO

相关内容

  • Last updated on