嵌套存储过程中的嵌套事务

Question

实际上，我对在存储过程中使用嵌套事务有点困惑。我需要在一个主过程中调用两个/三个过程。

例如：

Begin try
Begin Tran -- 1st level transcation
Procedure 1 --will be executed
Procedure 2 --will be executed
End tran -- 1st level transcation
End try
Begin Catch
Rollback tran
End Catch

PROCEDURE 1:
BEGIN TRAN TRAN1
///scripts
COMMIT TRAN TRAN1

PROCEDURE 2:
BEGIN TRAN TRAN2
///scripts
COMMIT TRAN TRAN2

我这样做的方式是否正确？

Answer 1

SQL Server 数据库引擎将忽略提交内部事务。事务根据在最外层事务结束时执行的操作提交或回滚。如果提交外部事务，则也会提交内部嵌套事务。如果回滚外部事务，则所有内部事务也会回滚，无论内部事务是否单独提交。

请参考链接https://technet.microsoft.com/en-us/library/ms189336%28v=sql.105%29.aspx

Answer 2

开始事务 => @@TRANCOUNT = @@TRANCOUNT + 1
提交事务 => @@TRANCOUNT = @@TRANCOUNT - 1（如果 0 保存更改）
回滚事务 => @@TRANCOUNT = 0（并放弃更改）

在您的外部程序中，只需执行

BEGIN TRY
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    ..
END TRY

Answer 3

没有嵌套事务这样的东西。如果您只想回滚事务的一部分，那么我应该使用保存点。关于保存点，如果在执行COMMIT TRANSACTION SavepointName后ROLLBACK最后一个ROLLBACK语句将回滚每个 thint，包括由保存点定义的部分：

BEGIN TRANSACTION -- A
SAVE TRANSACTION SavePoint1 -- B
-- do something
COMMIT TRANSACTION SavePoint1 -- C
-- ...
-- This ROLLBACK will rollback everything including data committed for SavePoint1 - it will rollback also actions for [B,C] range
ROLLBACK -- D

如果我使用保存点，那么我将使用此处的以下模板（请参阅第 Example 节）。

Answer 4

我用两个假设的过程来完成整个过程。全面测试和使用它。我希望在这段时间之后它能解决，因为问题是三年前的问题。

1.拳头步骤 创建两个表：

CREATE TABLE [dbo].[Products](
    [ProductId] [nvarchar](70) NOT NULL,
    [ItemId] [nvarchar](50) NOT NULL,
    [Color] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_table_4] PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](20) NULL,
    [NewColumn] [nchar](10) NULL,
 CONSTRAINT [P_Users_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

2. 创建 3 个存储过程（产品 ID 不是标识，我不使用它生成测试错误）：

--SP1
Create PROCEDURE [dbo].[usp_adduser]
    @name nvarchar(20),
    @new  nchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    insert into users
    values (@name,@new)
END
GO
--SP2
Create PROCEDURE [dbo].[usp_addproduct]
    @itemid nvarchar(50),
    @color  nvarchar(50)
    --,@Productid int = null --for test commnted
AS
BEGIN
    SET NOCOUNT ON;
    insert into products ([ItemId], [Color])    --dont use producid for test
    values (@itemid,@color)
END
GO
--SP3
Create PROCEDURE [dbo].[usp_addmix]
    @itemid nvarchar(50),
    @color  nvarchar(50),
    @name   nvarchar(20),
    @new    nchar(10)
AS

    SET NOCOUNT ON;
    Set Implicit_transactions ON;
    Begin transaction
            Execute usp_adduser @name,@new
            Execute usp_addproduct @itemid,@color
            Commit transaction
    Catch
        if @@TRANCOUNT > 0 
        rollback

3. 输入虚假数据

EXEC    [dbo].[usp_addmix]
        @itemid = N'1',
        @color = N'red',
        @name = N'ali',
        @new = N'test'

它的工作很好。

当第一个过程正确而后一个过程正确时，反之亦然，回滚操作对两者都有效。