BEGIN TRY/CATCH和MSDTC错误

Question

1/ 以下代码片段显示了我期望的错误：插入语句与外键约束 FK_... 冲突。

SET XACT_ABORT ON;

BEGIN TRANSACTION

    INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([Col1], [Col2])  
    VALUES (1200, 0)                

COMMIT TRANSACTION

2/ 但是当我将其放入BEGIN TRY/CATCH中时，错误消息就变得模糊了：Msg 1206，级别18，状态118，第18行 Microsoft Distributed Transaction Coordinator（MS DTC）已取消分布式事务。

SET XACT_ABORT ON;

BEGIN TRY  
    BEGIN TRANSACTION   

        -- Error is on this line
        INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([IdWebsite], [IdProductType])  
        VALUES (1200, 0)   

    COMMIT TRANSACTION
END TRY  
BEGIN CATCH
    PRINT 'Error' -- Code not reached

    SELECT ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()

    IF XACT_STATE() != 0   
        ROLLBACK TRANSACTION
END CATCH

你知道为什么会发生这种情况吗？

后续编辑:

1. 如果我删除不必要的显示事务，则可以正常工作。但是当我放置BEGIN/COMMIT TRAN时，仍然不清楚为什么会出现此错误。

2. 如果我在链接服务器上具有多个表中的多个插入，则会收到相同的错误。

欢迎任何评论 / 备注。

- user3104183

---

这个查询/存储过程是被其他有事务的查询/存储过程/代码调用吗？ - Pranav Singh

@PranavSingh：即使在这种简单的情况下，该错误也会再现。在生产环境中，我有一个更复杂的情况，存储过程调用其他存储过程调用其他存储过程，每个SP都具有SET XACT_ABORT ON的事务。 - user3104183

对于简单的上下文错误是不同的，即FOREIGN KEY constraint FK_错误可能是由于linkedsrv1.db1.[dbo].tbl1的列[IdWebsite]或[IdProductType]具有外键和值1200或0，而各自的列可能不在它们的主表中...但对于复杂的错误可能是由于某个内部sp调用失败了...最佳实践是不依赖于XACT_STATE()，而是在处理多级事务时检查@@TRANCOUNT。 - Pranav Singh

Answer 1

来自MSDN：

症状

考虑以下情况。您使用SQL Server 2005中的SQL Native Client OLE DB提供程序（SQLNCLI）创建链接服务器。您创建了一个分布式事务。分布式事务包含使用链接服务器从表检索数据的查询。当您提交分布式事务时，可能会收到以下错误消息：

Msg 1206, Level 18, State 167, Line 3 
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled 
the distributed transaction.

此外，当您在出现此行为后运行查询时，可能会收到以下错误消息：

Msg 8525, Level 16, State 1, Line 1 
Distributed transaction completed. Either enlist this session in a new 
transaction or the NULL transaction.

如果满足以下条件，就会出现此问题：

You use the SQLNCLI provider to create a linked server between two 
instances of SQL Server 2005.

The XACT_ABORT option is set to ON.

In the distributed transaction, you try to release a rowset before 
all rows in the rowset are processed.

注意：如果您在应用程序中调用ReleaseRows方法以释放行集之前提交分布式事务，则此问题也可能会发生。

原因：

此问题的原因是SQLNCLI提供程序错误地向链接服务器发送了一个attention信号，以回滚分布式事务。

解决方法：

为了防止SQLNCLI提供程序向服务器发送attention信号，请使用SQLNCLI提供程序完全消耗OLE DB使用者创建的任何行集。

更新：

您需要在服务器参数中将'remote proc trans'配置为“1”。

例如：

exec sp_configure 'remote proc trans','1' reconfigure with override 这将允许您执行任何分布式查询。

更多更新：

如果您在前端也使用.Net框架，那么我认为您可以使用TransactionScope类。从查询中删除事务并将事务放在代码级别。

- user240141

---

我的建议是包含一个链接到你所引用的MSDN文档。 - Taryn

+1，感谢提供信息，看起来所有的条件都满足了，除了这个“在处理完行集中的所有行之前，您尝试释放行集。”这个我不明白意思。另外，我也不理解解决方法的想法，具体应该做什么？ - user3104183

@user3104183...请看下面的注释。它与DT中的ReleaseRows方法有关。 - user240141

@bluefeet 这是微软开发者网络的文档： https://support.microsoft.com/zh-cn/help/937517/fix-error-message-when-you-commit-a-distributed-transaction-that-contains-a-query-that-retrieves-data-from-a-linked-server-in-sql-server-2005-transaction-manager-has-cancelled-the-distributed-transaction - CrApHeR

Answer 2

我曾经也经历过这种痛苦！

如果您只对单个表执行任何CRUD操作，那么不需要使用TRANSACTION。

在这种情况下，问题是XACT_STATE()返回-1，因为活动事务中存在错误。但是，ROLLBACK TRANSACTION失败，因为没有发生任何事务。您只执行了一个事务INSERT，它失败了，因此没有其他事务可回滚。

至少在这种情况下，依赖于@@TRANCOUNT总比XACT_STATE()更好。

要使其正常工作，请将其更改如下（尽管我不支持单个表的TRAN）：

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

- Karthik D V

---

有趣的想法...然而，似乎代码没有在BEGIN CATCH区域执行 - 例如，如果我在BEGIN CATCH的第一行添加一个PRINT，我看不到任何输出... - user3104183

我看到了多个ROLLBACK TRANSACTION语句，那么COMMIT TRANSACTION在哪里呢？我闻到了一些奇怪的味道！！！ - Karthik D V

这个链接可能对您有所帮助 http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5b682372-0bfc-4831-8d7f-14bf93b8f8d2/the-microsoft-distributed-transaction-coordinator-ms-dtc-has-cancelled-the-distributed?forum=sqldataaccess 您是否启用了报告过程执行？exec sp_configure 'remote proc trans'，'1' reconfigure with override？ - Karthik D V

看起来我找到答案了！http://support.microsoft.com/kb/937517。如果你关闭`XACT_ABORT`，它可能适用于你。试一下并请告诉我是否有效。 - Karthik D V

修改了“remote proc trans”之后仍未成功。我不能将XACT_ABORT关闭，因为这段代码被调用在更复杂的结构中，我想确保回滚操作正确执行。 - user3104183

Answer 3

BEGIN TRANSACTION 开始了一个分布式事务，涉及到运行语句的服务器和链接服务器，因为你可能会对两个服务器都进行更新。当 INSERT 失败时，需要取消分布式事务，这就是你得到错误信息的原因。所以你需要在两个级别上处理错误（插入和事务）。在这种情况下，你需要两个 TRY/CATCH 块，如下所示：

SET XACT_ABORT ON;

BEGIN TRY  
    BEGIN TRANSACTION   

    BEGIN TRY
        -- Error is on this line
        INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([IdWebsite], [IdProductType]) 
        VALUES (1200, 0)
    END TRY
    BEGIN CATCH
        SELECT 'Insert Error', ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()
        RAISERROR (15600,-1,-1, 'INSERT ERROR');
    END CATCH 

    COMMIT TRANSACTION
END TRY  
BEGIN CATCH
    SELECT 'Transaction Error', ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()

    IF XACT_STATE() != 0   
        ROLLBACK TRANSACTION
END CATCH

- Wagner DosAnjos

---

这里的交易逻辑是什么？ - user3104183

添加了缺失的 RAISERROR 以回滚事务。 - Wagner DosAnjos