Cannot call stored procedures when a distributed transaction has been rolled back - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


8
0
Sign in
to vote
ID 466749 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 6/12/2009 2:38:40 PM
Access Restriction Public

Description

If you are in a distributed transaction and you encounter an error and roll back 
the transaction, you find that you cannot very much at all after you have rolled
back. For instance, you cannot call stored procedures.

This is really bad. A typical error handler goes:

BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC error_handler_sp @@procid
END CATCH

If you know that this procedure will be called as part of a distributed transction,
you can write it differently, but this may be a general procedure, that was not
intended to be part of a distributed transaction originally. The situation here
is that you cannot even call error_handler_sp. This procedure could then
theoretically identify that you are in an aborted distributed transaction and
invoke a loopback procedure to write the the errorlog table. (Which it also
would if @@trancount > 0.) 

See also https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=466739 where I call for functions 
to detect distributed transactions.

I can understand that I can't perform new INSERT operations if I'm in a
distributed transaction. (Although it certainly is desireable.) But I should 
still be able to perform control of flow like running a stored procedure.

Sign in to post a comment.
Posted by Microsoft on 4/18/2011 at 9:50 AM
Dear Erland,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to reopen it and we will take another look.

Jean-Sébastien, SQL Server
Posted by Erland Sommarskog on 2/4/2010 at 2:01 PM
Oh no, that old man does not walk. BY principle I don't accept bugs being closed by design when the behaviour is not realistically acceptable. "Design" here would mean that someone has decided that distributed transactions should be difficult. Yes, this is likelyt to be difficult to address, but by design? No way!

Whence, I've reopened the bug.
Posted by Erland Sommarskog on 2/2/2010 at 12:43 PM
If this is by design you need to improve your design process.

I can understand that you are caught between a rock and a hard place, but just don't call it design.

To start with, it is not apparent that EXEC starts a transaction, but I guess that you need some sort of lock to invoke the procedure and that is what kills you.

I realise that this is a difficult problem to solve, but Microsoft also needs to understand that this is a serious blow to the composability and which greatly reduces the functionality of linked servers.
Posted by Microsoft on 2/2/2010 at 11:57 AM
Hej Erland,

This is by design. The remote store procedure (i.e. error_sp as in the sample query) is run under a DTC transaction. When the DTC transaction is rolled back, no new transaction can be started (unless the thread defects from DTC transaction, which cannot be done here). Therefore you cannot issue a select from a table/dmv or DML which both require a transaction.

This is different from local transactions. When a local transaction is rolled back you can always create a new one.

Thanks for you feedback!
- Tobias, SQL Server Engine
Posted by Microsoft on 6/24/2009 at 11:55 AM
Erland,

Thanks for this error report.

We'll have to research where the restriction came from, and whether it can be lifted without causing too many other problems.

Once we understand the reason for the restriction, we can then plan a course forward.



Kevin Farlee

SQL Server Storage Engine