T-SQL等效的.NET TransactionScopeOption.Suppress是什么？

Question

在我的.NET代码中，在数据库事务（使用TransactionScope）内，我可以包含一个带有TransactionScopeOption.Suppress的嵌套块，这可以确保即使外部块回滚，嵌套块内的命令也会被提交。 以下是代码示例：

using (TransactionScope txnScope = new TransactionScope(TransactionScopeOption.Required))
{
    db.ExecuteNonQuery(CommandType.Text, "Insert Into Business(Value) Values('Some Value')");

    using (TransactionScope txnLogging = new TransactionScope(TransactionScopeOption.Suppress))
    {
        db.ExecuteNonQuery(CommandType.Text, "Insert Into Logging(LogMsg) Values('Log Message')");
        txnLogging.Complete();
    }

    // Something goes wrong here. Logging is still committed

    txnScope.Complete();
}

我试图找出是否可以在T-SQL中完成这个操作。有些人推荐OPENROWSET，但使用起来不太优雅。此外，我认为在T-SQL代码中放置连接信息是一个坏主意。

我过去使用过SQL Service Broker，但它也支持事务消息，这意味着消息直到数据库事务提交后才会发布到队列中。

我的要求：我们的应用程序存储过程由某些第三方应用程序触发，在存储过程之外启动隐式事务。我希望能够捕获和记录任何错误（在同一数据库中的数据库表中），并在我的存储过程中重新抛出异常，以让第三方应用程序回滚事务，并知道操作失败了（因此在失败的情况下执行所需的操作）。

- M. Rashid

---

所以您的.NET代码被第三方.NET代码调用了？ - usr

你看过变量表了吗？ - Shaneis

@usr - 我不了解第三方应用程序代码。我在问题中发布的.NET代码只是为了表明我想在T-SQL代码中做同样的事情。 - M. Rashid

@Shaneis - 你是指表变量吗？这些如何帮助呢？我想将错误日志记录在一个永久的数据库表中，该表恰好位于存储过程正在运行的同一数据库中。 - M. Rashid

Answer 1

你可以设置一个回环链接服务器，将remote proc transaction Promotion选项设置为false，然后在TSQL中访问它，或者使用SQL服务器中的CLR过程创建一个新连接，超出事务并完成您的工作。

这两种方法都建议在如何在SQL Server 2008中创建自主事务中使用。

这两种方法都涉及创建新连接。有一个开放连接项目请求本地提供此功能。

- Martin Smith

---

CLR过程选项正在考虑中。然而，回环链接服务器选项要简单得多。非常感谢！链接服务器选项有什么明显的性能缺陷吗？这只会在异常情况下使用（主要用于存储过程的catch块）。 - M. Rashid

我所能想到的主要是两个连接，而不是一个，但似乎这不会成为常规事情。 - Martin Smith

Answer 2

变量中的值在回滚之后仍然存在。 因此，在以下示例中，所有将要删除的行都可以插入到一个持久化表中，并通过OUTPUT和表变量的组合进行后续查询。

-- First, create our table
CREATE TABLE [dbo].[DateTest] ([Date_Test_Id] INT IDENTITY(1, 1), [Test_Date] datetime2(3));

-- Populate it with 15,000,000 rows
-- from 1st Jan 1900 to 1st Jan 2017.
INSERT INTO [dbo].[DateTest] ([Test_Date])
SELECT 
TOP (15000000)
    DATEADD(DAY, 0, ABS(CHECKSUM(NEWID())) % 42734)
    FROM [sys].[messages] AS [m1]
    CROSS JOIN [sys].[messages] AS [m2];

BEGIN TRAN;

BEGIN TRY

    DECLARE @logger TABLE ([Date_Test_Id] INT, [Test_Date] DATETIME);

    -- Delete every 1000 row
    DELETE FROM [dbo].[DateTest]
    OUTPUT deleted.Date_Test_Id, deleted.Test_Date INTO @logger
    WHERE [Date_Test_Id] % 1000 = 0;

    -- Make it fail
    SELECT 1/0

    -- So this will never happen
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    ROLLBACK TRAN
    SELECT * INTO dbo.logger FROM @logger;

END CATCH;

SELECT * FROM dbo.logger;

DROP TABLE dbo.logger;

- Shaneis

---

问题陈述中提到，“第三方应用”正在回滚事务。回滚不在存储过程本身中（虽然如果发生在此处是可选的，这将是更好的解决方案）。 - Martin Smith