如何在SQL Server 2008中实现自主事务？

Question

我正在尝试在SQL Server 2008存储过程中实现自主事务。该过程将只有两个事务，内嵌的事务将提交，但外部事务将回滚。 我看到了许多成功实现相反过程的示例。但我还没有找到我的问题的解决方案。

样本表脚本

create table testTab1(id int identity(1,1) primary key, name varchar(100));
create table testTab2(id int , name varchar(100));
insert into testTab1(name) values('User1'),('User2'),('User3');

过程的示例代码

CREATE PROCEDURE [dbo].[testSP2]
AS
BEGIN
SET IMPLICIT_TRANSACTIONS ON
BEGIN TRAN 
    insert into testTab1(name) values('User4'),('User5'),('User6');
    IF @@ROWCOUNT>0
        BEGIN
            SAVE TRAN InnerTran
                insert into testTab2(id,name) values(1,'User4'),(2,'User5'),(3,'User6');
                --PRINT 'NUMBER OF TRAN='+CAST(@@TRANCOUNT AS VARCHAR(5)) 
            COMMIT TRAN InnerTran
        END                 
ROLLBACK
SET IMPLICIT_TRANSACTIONS OFF
END

MY desired Output is

- Avishek

---

简单来说，你无法使用嵌套事务。嵌套事务是一个谎言，并不能实现你想象中的效果。第二个事务仅会增加@@TRANCOUNT的值，回滚或提交操作只会将计数器归零，并为每个“嵌套”事务执行回滚/提交操作。http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/ - Sean Lange

@SeanLange 不要太认真对待这个插入语句。我将使用其他的dml替换它们。我只是想让InnerTransaction提交，但在这个过程中OuterTransaction将回滚。 - Avishek

正如我之前所说，没有办法做到这一点。就是这样。故事结束了。然而，真正的问题是你实际上想要做什么，因为很可能你只需要以不同的方式思考它。也许你需要使用TRY/CATCH。然后，如果你的插入失败了，你可以使用catch在其他地方插入。 - Sean Lange

Oracle当然有这个功能，它被称为自主事务。我只在SQL Server领域工作了1.5年，我讨厌没有现成的自主事务。当我在Oracle工作了8年以上时，我的代码和团队中的其他人都广泛使用了这个功能。当你发现一些如此简单（在概念上）的东西，你已经使用了很长时间，但你发现其他非常相似的技术没有实现这些你认为理所当然的功能时，真是令人恼火。 - Code Novice

我只是在观望和等待微软推出自主事务功能，这也是我发现自己在这篇SO文章上的原因。我大概每个月看一次这个。 - Code Novice

Answer 1

这是另一篇写得不错的博客，展示了每个“hack”如何模仿Oracle精彩的自主事务功能。我期待着在SQL Server中存在这个功能的世界。

https://techcommunity.microsoft.com/t5/sql-server/how-to-create-an-autonomous-transaction-in-sql-server-2008/ba-p/383471

注意：由于某种原因，当你向SQL Server专业人员提出这个问题时，他们会看着你像个疯子，并开始询问一些问题，比如“为什么需要那个...为什么有人需要那个？”现在我在一个SQL Server店里，我经常遇到这样的情况。只要知道如果你来自拥有强大和深入功能的Oracle数据库，例如内置的强大搜索算法和出色的字符串解析和操作能力，你会发现，在深入研究SQL Server后，它缺少这些功能。不要误会... SQL Server也做很多事情非常好，比如表变量和临时表，以及将T-SQL与普通SQL混合使用的能力。 Oracle将其PL-SQL与其SQL引擎分开，这是Oracle的一个主要缺点。优缺点都有。

- Code Novice

---

虽然我只用过SQL Server，从未使用过Oracle，但自主事务的用例每年或两年就会出现一次。这将是一个非常方便的功能。对我来说，主要用例是无论事务的结果如何都要写入日志表。如果我们完全控制事务，那么我们可以使用变量或表变量（不参与事务）来存储我们想要记录的数据，并在事务回滚（或提交）后记录它。但有时存储过程会参与外部事务，仍然需要记录消息。 - Simon Elms