TSQL中嵌套事务抛出错误的回滚 [英] Rollback of nested transaction throwing error in TSQL

问题描述

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

Here is a snippet of what I am trying to achieve. I have a nested transaction 'tran2', which I can't rollback

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 ..

向我抛出这个错误

消息 6401，级别 16，状态 1，第 13 行
无法回滚 tran2.未找到该名称的事务或保存点.

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

然而，@@trancount 在那一刻给了我 2 个交易

however the @@trancount is giving me 2 transactions at that point

推荐答案

代替 begin trans tran2，使用 保存交易tran2.

Instead of begin trans tran2, use save transaction tran2.

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

This creates a save point within the outer transaction, which you can rollback to.

这篇关于TSQL中嵌套事务抛出错误的回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！