none
Strange "This SqlTransaction has completed; it is no longer usable." RRS feed

  • Question

  • Hi there to all.

     

    I've experienced this exception randomly in our production server and I don’t figure out why it is happening.

     

    I have a set of windows services (right now 5) that do a lot of data base inserts. The pattern that I’m using to insert is:

     

    using (SqlConnection conn = new SqlConnection("Connection string here!!!!"))

    {                            

    //Open the connection

                    conn.Open();

     

                    //Start a new transaction...

                    SqlTransaction trans = conn.BeginTransaction();

     

    try

                    {

                                    //Refresh the connection and transactions of dataadapters insert and update commands...                            

                                    //call dataadapter.update for each statement...

     

                                   //Commit if ok

                                   trans.Commit();                                                                  

                    }

                    catch

                    {

                                    //I'm getting random error here....

                                    trans.Rollback();

     

                                    throw;                                                                  

                    }

    }

     

    I’m using 11 SqlDataAdapters to insert and update data to 11 different database tables.

     

    One interesting thing, Is that when the exception occurs, it occurs in all services. This can be a sql server issue (some kind of memory recover process that rollback the transaction) or a polling issue?

     

    Thanks for the help

    Best regards

     

    Tiago Silva

    Wednesday, August 1, 2007 11:10 AM

All replies

  • It seems almost as though you are using your try/catch as if they are mutually exclusive events.  It turns out that commit can throw.  If Commit throws, and you try to rollback, you will see exactly what you are running into.  I would suggest setting a flag and then in a finally statement, either commiting or rolling back depending on that flag.

    It seems almost as though you are using your try/catch as if they are mutually exclusive events.  It turns out that commit can throw.  If Commit throws, and you try to rollback, you will see exactly what you are running into.  I would suggest setting a flag and then in a finally statement, either commiting or rolling back depending on that flag.  So it might look something like:

     

    bool transaction_ok = true;

    try

    {

        // insert, update, delete, etc

    }

    catch

    {

         // handle exception

         transaction_ok = false;

    }

    finally

    {

         try

         {

              if (transaction_ok)

                  trans.Commit();

              else

                  trans.Rollback();

         }

         catch

         {

              // handle commit/rollback specific errors

         }

    }

     

    Using this should help you to identify the source of your problem faster.

     

    Hope this helps.

     

    John

     

     

    P.S. please mark this question as answered if you feel that your problem is addressed.

     

    Wednesday, August 1, 2007 6:17 PM
  • Thanks John for the reply.

     

    I will do your sugestion and post the result of the problem.

     

    Regards

     

    Tiago

    Wednesday, August 1, 2007 6:29 PM