在分布式事务中检查旁路旁路CATCH块 [英] Check Contraint Bypassing CATCH block in Distributed Transaction

问题描述

我有一个执行分布式事务的MSSSQL存储过程，如下所示：

I have a MSSSQL stored procedure performing a distributed transaction that looks like this:

SET XACT_ABORT ON;
SET NOCOUNT ON;

BEGIN TRY
  BEGIN DISTRIBUTED TRANSACTION


  insert into LNKSRV.INST.dbo.zz (id, val) values (1, 'a');
  insert into LNKSRV.INST.dbo.zz (id, val) values (2, 'b');


  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  if (XACT_STATE() <> 0) 
  BEGIN
    ROLLBACK TRANSACTION;
  END
  print ERROR_MESSAGE();
  print ERROR_LINE();
  print ERROR_SEVERITY();

END CATCH

这工作正常。

如果我添加这个第三个insert语句：

If I add this 3rd insert statement:

  insert into LNKSRV.INST.dbo.zz (id, val) values ('error', 'b');

...它正确失败 - 事务在远程服务器上回滚，控制传递到CATCH块和我获取有关错误的信息（不能将'error'转换为int）。

...it fails correctly -- the transaction is rolled back on the remote server and control passes to the CATCH block and I get information about the error (can't convert 'error' to int).

但是，如果我添加这个insert语句：

But if I add this insert statement:

  insert into LNKSRV.INST.dbo.zz (id, val) values (-1, 'b');

..我有一个检查限制在远程表需要值> 0在id列，那么事情就不如我所料。交易 DOES 回滚，但控制不要将转移到catch块。相反，执行只是消失，这被打印到输出窗口：

..and I have a check contraint on the remote table requiring values > 0 in the id column, then things do not work as I expect. The transaction DOES roll back, but control DOES NOT transfer to the catch block. Instead, execution just dies and this is printed to the output window:

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

为什么？我需要在捕获博客中记录这些错误。

Why? I need to log these errors in the catch blog.

推荐答案

由于分布式事务协调器正在处理此事务，交易的分布式部分，DTC以注意的形式发送消息，阻止您的代码执行，TRY / CATCH无法处理。

Since the distributed transaction coordinator is handling this, when the transaction fails on the distributed part of the transaction, the DTC sends a message in the form of an attention, which stops your code from executing, and which the TRY/CATCH cannot process.

当您尝试在表中插入不正确的数据类型（甚至在远程实例上）时，SQL Server可以检测到您的最终，但链接服务器上处理约束，这将导致将注意力发送到DTC和您的TRY / CATCH被忽略。

SQL Server can detect on your end when you are trying to insert an incorrect data type into a table (even on a remote instance) but the constraint is processed on the linked server, which causes the attention to be sent to DTC and your TRY/CATCH to be ignored.

有关更多信息，请参阅SQL Server 2008图书中的在Transact-SQL中使用TRY ... CATCH部分中的第一个注意部分在线，位于：

For more information see the first "Note" section in the "Using TRY...CATCH in Transact-SQL" section of SQL Server 2008 Books Online, located at:

http://msdn.microsoft.com/en-us/library/ms179296.aspx

这篇关于在分布式事务中检查旁路旁路CATCH块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！