MySQL Bugs: #28709: Using TransactionScope causes InvalidOperationException

Bug #28709	Using TransactionScope causes InvalidOperationException
Submitted:	27 May 2007 23:29	Modified:	6 Sep 2007 10:39
Reporter:	Dean Ward	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.7, 5.1.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	TransactionScope

Description:
Using TransactionScope and the MySql* classes causes an InvalidOperationException - Connection must be valid and open to commit transaction with the following stack trace...

at MySql.Data.MySqlClient.MySqlTransaction.Commit()
at MySql.Data.MySqlClient.MySqlPromotableTransaction.System.Transactions.IPromotableSinglePhaseNotification.SinglePhaseCommit(SinglePhaseEnlistment singlePhaseEnlistment)
at System.Transactions.DurableEnlistmentCommitting.EnterState(InternalEnlistment enlistment)
at System.Transactions.DurableEnlistmentActive.ChangeStateCommitting(InternalEnlistment enlistment)
at System.Transactions.TransactionStateSPC.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStateVolatilePhase1.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStatePhase0.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStateActive.BeginCommit(InternalTransaction tx, Boolean asyncCommit, AsyncCallback asyncCallback, Object asyncState)
at System.Transactions.CommittableTransaction.Commit()
at System.Transactions.TransactionScope.InternalDispose()
at System.Transactions.TransactionScope.Dispose()
at Console.Program.Main(String[] args) in D:\Source\WFTest\Console\Program.cs:line 32 

Example code is below, using the SqlClient or OracleClient equivalents works just fine!

How to repeat:
using (TransactionScope txScope = new TransactionScope())
{
   using (MySqlConnection dbConnection = new MySqlConnection(connectionString))
   {
      dbConnection.Open();

      using (MySqlCommand dbCommand = new MySqlCommand(commandText))
      {
         dbCommand.Connection = dbConnection;

         using (MySqlDataReader dataReader = dbCommand.ExecuteReader())
         {
            while (dataReader.Read())
               System.Console.WriteLine(dataReader.GetString(0));
         }
      }
   }
   txScope.Complete();
}

Suggested fix:
The MS SQL and Oracle implementations use a transacted connection pool that keeps track of the physical connections and transactions associated with them. When a transaction is committed the underlying pool is notified and it deals with cleaning up the actual connection and transaction. Maybe this is the best approach for MySQL to take as well?

Additionally an enlist flag in the connection string would make it simpler to exclude a connection from automatic enlistment - the current project I'm working on requires that we manually enlist a connection to a particular transaction and that can't happen unless we exclude the connection from auto-enlistment first!

Hi Dean and thanks for your report. What happens if you modify your code so that connection and command objects are created before TransactionScope like this:
      MySqlConnection c = new MySqlConnection(GetConnectionString(true));
      MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES ('a', 'name', 'name2')", c);
      try
      {
          using (TransactionScope ts = new TransactionScope())
          {
              c.Open();
              cmd.ExecuteNonQuery();

              if (commit)
                  ts.Complete();
          }
          cmd.CommandText = "SELECT COUNT(*) FROM test";
          object count = cmd.ExecuteScalar();
          Assert.AreEqual(commit ? 1 : 0, count);
      }
      catch (Exception ex)
      {
          Assert.Fail(ex.Message);
      }
      finally
      {
          if (c != null)
              c.Close();
      }
(standard part of our test suite)

In any case you are right with your remarks but I think implementation of transactions is server issue not connector/NET. So far, this looks like S4 (feature request) to me.

Hi Tonci,

Thanks for investigating, much appreciated!

Unfortunately, in reality, the code I originally submitted is a simplification of what is really happening. In the original code (see http://www.codeplex.com/WFTools - this is where this was discovered) we don't know anything about the TransactionScope by the time we come to open the connection and execute a command. 

In essence the original code is passed a transaction by the Windows Workflow engine and we then enlist our connection in that transaction - i.e. we have little to no control over how or when Windows Workflow commits or rolls back the transaction so we just open and close our connection as usual! 

The root issue here is that Connector/NET is closing the connection without any regard for the fact there's a transactional dependency upon it. What it should do is keep that connection valid and open until all transactions dependent upon it have been committed or rolled back...

I don't think this is feature request material - this is fundamental functionlity for the way that TransactionScope is supposed to work - e.g.

public class MyBLL
{
   private MyDAL myDAL = new MyDAL();

   public void DoSomeTransactionStuff()
   {
      using (TransactionScope txScope = new TransactionScope())
      {
         myDAL.DoSomeUpdates();
         myDAL.DoSomeInserts();
         txScope.Complete();
      }
   }
}

public class MyDAL
{
   public void DoSomeUpdates()
   {
     // ... open connection, do some updates, close connection
   }

   public void DoSomeInserts()
   {
      // ... open connection, do some inserts, close connection
   }
}

In this case the class MyDAL doesn't know, or care, that it's within a TransactionScope, it just does its work as usual - it's the MyBLL class that makes sure the work is performed inside a transaction!

What do you think?

Cheers,

Dean

Hi Dean.

I see your point from the start, as the report was well written, but I don't think we can help you here, especially in c/NET, as that is the way MySQL connection/Transaction work (thus the idea of server feature request regarding Oracle implementation). Transactions + connection pooling seems generally a bad idea to me. Consider this example:
 - Connect to to MySQL server -> you are given unique ConnectionID -> connection goes to pool
 - You start transaction and something goes wrong (i.e. network switch lost power) -> you either reconnect or take another connection from the pool *but* that connection has different ConnectionID thus you need to restart transaction all over again as no other connection can proceed instead of closed one...

As for the root issue (The root issue here is that Connector/NET is closing the connection without any regard for the fact there's a transactional dependency upon it.) I will consult.

Dean, to have this work cleanly we will need to support XA transactions in c/NET. c/NET team is now looking at how it can be done.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32216

Fixed in 5.1.3.  Too big of a change to put in 5.0.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32645

A note has been added to the 5.1.3 changelog: 

Using TransactionScope would cause an InvalidOperationException.