sql - SQL中嵌套事务抛出错误的回滚

Question

这是我想要实现的目标的一个片段。我有一个无法回滚的嵌套事务“tran2”

SELECT 'Before', * FROM [table] WHERE field ..

BEGIN TRAN tran1
UPDATE [table] set field ... WHERE field ..

 BEGIN TRAN tran2
    SELECT 'During', * FROM [table] WHERE field ..
    select @@trancount as 'transactioncount'

  rollback tran tran2

rollback TRAN tran1
SELECT 'After', * FROM [table] WHERE field ..

正在向我抛出这个错误

Msg 6401, Level 16, State 1, Line 13
Cannot roll back tran2. No transaction or savepoint of that name was found.

然而 @@trancount那时给了我 2 笔交易

Answer 1

而不是 begin trans tran2 , 使用 save transaction tran2 .

这会在外部事务中创建一个保存点，您可以回滚到该保存点。