如何避免在嵌套存储过程的嵌套事务中使用重复的保存点名称?

我有一个几乎总是遵循的模式,如果我需要在事务中包装一个操作,我会这样做:

BEGIN TRANSACTION
SAVE TRANSACTION TX

-- Stuff

IF @error <> 0
    ROLLBACK TRANSACTION TX

COMMIT TRANSACTION

在过去,这对我来说已经足够好了,但是在多年使用这种模式(并复制粘贴上面的代码)之后,我突然发现了一个完全震惊的缺陷。

CREATE TABLE Test (test INTEGER NOT NULL)

BEGIN TRAN 
SAVE TRAN TX

    BEGIN TRAN
    SAVE TRAN TX
        INSERT INTO Test(test) VALUES (1)
    COMMIT TRAN TX

    BEGIN TRAN
    SAVE TRAN TX
        INSERT INTO Test(test) VALUES (2)
    COMMIT TRAN TX

    DELETE FROM Test

ROLLBACK TRAN TX
COMMIT TRAN TX

SELECT * FROM Test

DROP TABLE Test

当我执行这个时,它列出了一条记录,值为“1”。换句话说, 即使我回滚了我的外部事务 ,表中添加了一条记录。

发生的事情是 ROLLBACK TRANSACTION TX 最外面的一层正在倒退到最后一层 SAVE TRANSACTION TX 在内部层面。现在我把这些都写出来了,我可以看到它背后的逻辑:服务器通过日志文件回溯,将其视为线性事务流;它不理解事务嵌套(或者在我的实际场景中,通过调用其他存储过程)所隐含的嵌套/层次结构。

可以

每次启动一个事务时,都要给出一个唯一的名称并不难(可以基于SP名称,或者类似的名称),但我确实担心最终会出现冲突—而且您不会知道,因为它不会导致错误,而是默默地销毁您的数据……:-(