Home Dashboard Directory Help
Search

Uncatchable error when distributed transaction is aborted by Erland Sommarskog


Status: 

Closed
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Bug
ID: 414969
Opened: 2/15/2009 1:23:14 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

If a batch includes a distributed transaction that perform updates on a remote table, and this update fails, and there is a CATCH handler something goes very wrong, and SQL Server reports a level 18 error that cannot be caught in any outer CATCH handler. The error is reported to occur on the line for BEGIN CATCH.

When I have tested this, the error also occurred with OPENQUERY, but I have not seen it when calling remote stored procedures.

I should also say that I ran this test between two instances on the same computer. Since the repro is able to produce different results at repeated executions, there is likely to be a timing issue involved.

Probably there is something more that goes wrong in the TDS output, because the output
can be different from execution to execution.
Details
Sign in to post a comment.
Posted by Microsoft on 3/6/2009 at 11:00 AM
Hi Erland,
We looked at the interaction of DTC and DML for this particular error & reached conclusion that we cannot fix this behavior. For one, the DML statement has to be atomic in nature so we cannot yield during the DTC operation since that will result in breaking the transaction semantics. So given this specification, when an error happens on the remote server DTC sends abort requests to all participants. This is done asynchronously so it is possible that there is a race condition under which we might be able to execute code in the CATCH block if the DTC abort notification has not been processed yet and in other cases we will not be able to run code in the CATCH block. Note that the abort request comes in the form of an attention signal which is essentially a request abort so it cannot be caught on the server.
One possible workaround is to use a stored procedure to perform the INSERT. This way you could wrap the INSERT in a TRY...CATCH on the remote side if needed and handle errors there. In any case, you need to be aware of the DTC error handling when dealing with DML operations on linked servers.
The DTC error behavior I mentioned is documented in Books Online in the following topic:

http://msdn.microsoft.com/en-us/library/ms179296(SQL.90).aspx

Thanks
Umchandar, SQL Programmability Team
Posted by Erland Sommarskog on 2/28/2009 at 4:25 PM
And the fix is to say that it is by design? A design would at least called a for a consistent behaviour, which therer isn't. So try another resolution like "Won't fix", but design it isn't.
Posted by Microsoft on 2/19/2009 at 6:15 PM
Hi Erland,
Thanks for reporting the issue. We have identified the cause of the problem and will evaluate how to fix it. The problem stems from the fact that the DTC transaction could be aborted due to error on the linked server and when the transaction resumes in the local instance we don't handle the DTC abort condition correctly. We handle this situation correctly for remote RPC calls and we need to do the same for INSERT case also. Anyway, we will let you know how we intend to correct the problem.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.