none
System.Transaction and SQL 2005 BEGIN TRANS don't play nice together. RRS feed

  • Question

  • I'm writing some new C# code that's using the System.Transaction namespace to wrap multiple business managers that in turn call SQL 2005 stored procedures.  Some of these stored procedures, written by other folks, contain BEGIN TRANS, ROLLBACK TRANS, COMMIT TRANS statements.  If there are no errors in the SPs then everything works great.  If one of the SPs call their own ROLLBACK then the System.Transaction / MSDTC Transaction goes haywire.  Meaning, it'll sometimes leave transactions open, sometimes it'll just error out with an error stating "New request is not allowed to start because it should come with valid transaction descriptor".

     

    Has anyone else seen this?  Is there some way to get both the System.Transactions MSDTC transactions to work with the local stored procedure BEGIN TRANS?  Obviously I'd like to not rearchitect my C# code and I probably can't update the SPs unless I break someone elses code.

     

    Thanks for taking a look.

     

    Thursday, June 21, 2007 7:13 PM

Answers

  • Hi Steve,

     

    There is a known issue with transactions within stored procedures that generates the error you describe, (...should come with a valid transaction descriptor).  

    http://support.microsoft.com/kb/916002

    http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93731

     

    In the long run, it is expected that if your stored procedure is running in the middle of a transaction, and you request another transaction on the same table, it will queue up until the first transaction finishes.  This can cause havoc with your other transactions timing out if you do not treat those transactions as critical sections.  Inevitably, you will have to either catch timeout exceptions and retry (effectively polling the other transaction) until your transaction succeeds or you will have to set your timeout to 0 (infinity) which will lock up the thread while it waits. 

     

    In the short term, I would suggest surrounding your transaction establishing code with a critical section, or otherwise serializing those threads in your workflow.  In the long run, you can either use critical sections, or set your timeouts to infinite -- though critical sections provide you with a much finer grained capacity to control, evaluate, and tune performance because you can keep your timeouts for command execution.

     

    I hope this helps,

     

    John (MSFT)

    Friday, June 22, 2007 12:22 AM

All replies

  • Hi Steve,

     

    There is a known issue with transactions within stored procedures that generates the error you describe, (...should come with a valid transaction descriptor).  

    http://support.microsoft.com/kb/916002

    http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93731

     

    In the long run, it is expected that if your stored procedure is running in the middle of a transaction, and you request another transaction on the same table, it will queue up until the first transaction finishes.  This can cause havoc with your other transactions timing out if you do not treat those transactions as critical sections.  Inevitably, you will have to either catch timeout exceptions and retry (effectively polling the other transaction) until your transaction succeeds or you will have to set your timeout to 0 (infinity) which will lock up the thread while it waits. 

     

    In the short term, I would suggest surrounding your transaction establishing code with a critical section, or otherwise serializing those threads in your workflow.  In the long run, you can either use critical sections, or set your timeouts to infinite -- though critical sections provide you with a much finer grained capacity to control, evaluate, and tune performance because you can keep your timeouts for command execution.

     

    I hope this helps,

     

    John (MSFT)

    Friday, June 22, 2007 12:22 AM
  • Thanks for the response.  I'll give this a go and see what happens.
    Friday, June 22, 2007 5:30 PM