检查约束绕过分布式事务中的 CATCH 块
我有一个执行分布式事务的 MSSSQL 存储过程，如下所示：
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 into LNKSRV.INST.dbo.zz (id, val) values ('error', 'b');
...它正确失败 - 事务在远程服务器上回滚，控制权传递到 CATCH 块，我得到有关错误的信息（无法将“错误”转换为 int） 。
但是，如果我添加此插入语句：
insert into LNKSRV.INST.dbo.zz (id, val) values (-1, 'b');
..并且我在远程表上有一个检查约束，需要值> > id 列中的 0，那么事情就不会按我的预期进行。 事务确实回滚，但控制没有转移到catch块。 相反，执行只是终止，并且将其打印到输出窗口：
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction
为什么？ 我需要在 catch 博客中记录这些错误。
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
This works fine.
If I add this 3rd insert statement:
insert into LNKSRV.INST.dbo.zz (id, val) values ('error', 'b');
...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).
But if I add this insert statement:
insert into LNKSRV.INST.dbo.zz (id, val) values (-1, 'b');
..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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于分布式事务协调器正在处理此问题，因此当事务在事务的分布式部分失败时，DTC 会以注意的形式发送一条消息，该消息会阻止代码执行，并且 TRY/CATCH 无法处理该消息。
当您尝试将不正确的数据类型插入表中（甚至在远程实例上）时，SQL Server 可以在您端检测到，但约束是在链接服务器上处理的，这会导致将注意力发送到 DTC 和您的 TRY/ CATCH 被忽略。
有关详细信息，请参阅 SQL Server 2008 联机丛书“在 Transact-SQL 中使用 TRY...CATCH”部分中的第一个“注意”部分，该部分位于：
http://msdn.microsoft.com/en-us/library/ms179296.aspx
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 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.
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