MySQL Bugs: #28709: Using TransactionScope causes InvalidOperationException

Bug #28709	Using TransactionScope causes InvalidOperationException
Submitted:	28 May 2007 1:29	Modified:	6 Sep 2007 12:39
Reporter:	Dean Ward
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	5.0.7, 5.1.1	OS:	Microsoft Windows
Assigned to:		Target Version:
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.