MySQL Bugs: #26754: EnlistTransaction throws false MySqlExeption "Already enlisted"

Bug #26754	EnlistTransaction throws false MySqlExeption "Already enlisted"
Submitted:	1 Mar 2007 17:01	Modified:	20 May 2007 6:18
Reporter:	Vlad Untu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.3.0, 5.0.5	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
After enlisting a null transaction (witch throws an enlistment exception) the internal state of the Connection object becomes invalid.

If you try to enlist a valid transaction after the first you always get "Already enlisted" exception.

How to repeat:
the assertions between the lines of code are valid;

assert: Transaction.Current == null

MySqlConnection c = new MySqlConnection(conn_string);
c.Open();

assert: c.State == Open

try {
   c.EnlistTransaction(Transaction.Current)
}
catch { }

using (TransactionScope ts = new TransactionScope()) {

assert: Transaction.Current != null;

   c.EnlistTransaction(Transaction.Current)
}

Suggested fix:
public override void EnlistTransaction(System.Transactions.Transaction transaction)
{

if (currentTransaction != null <<<add && currentTransaction.BaseTransaction != null>>>) {
	if (currentTransaction.BaseTransaction == transaction)
		return;

	throw new MySqlException("Already enlisted");
}
....

It seems that sometimes the server reports InTransaction when no BEGIN statement or other transaction start function call was called (I've also checked to see if the server is in AUTOCOMMIT=0). <<I will investigate this issue to be able to reproduce this situation>>

Now, if the server reports InTransaction the BeginTransaction function throws an NestedTransaction exception and sice the EnlistTransaction calls BeginTransaction the internal state of connection becomes invalid and subsequent enlistments throws "Already enlisted".

This is not an issue for programs that maintain a persistent connection to the database. If you connect to the server within the TransactionScope the connector works fine.

CORRECTION:
THIS IS AN issue for programs that maintain a persistent connection to the
database.

Hi Vlad and thanks for your report.
I must admit I don't quite follow you. Can you provide me with better problem description as well as with steps to reproduce it (and a test case too)?

[Test()]
public void EnlistTransactionNullTest() {   
   MySqlConnection conn = new MySqlConnection("server = 192.168.0.200; user = root;");
   conn.Open();

   try {
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      cmd.Connection.EnlistTransaction(null);
   }
   catch { }

   using (TransactionScope ts = new TransactionScope()) {
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      try {
         cmd.Connection.EnlistTransaction(Transaction.Current);
      }
      catch (MySqlException) {
         Assert.Fail("No exception should have been thrown");
      }
   }
}

[Test()]
public void EnlistTransactionWNestedTrxTest() {
   MySqlConnection conn = new MySqlConnection("server = 192.168.0.200; user = root;");
   conn.Open();

   MySqlTransaction t = conn.BeginTransaction();

   using (TransactionScope ts = new TransactionScope()) {
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      try {
         cmd.Connection.EnlistTransaction(Transaction.Current);
      }
      catch (InvalidOperationException) { /* caught NoNestedTransactions */  }
   }

   t.Rollback();

   using (TransactionScope ts = new TransactionScope()) {
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      try {
         cmd.Connection.EnlistTransaction(Transaction.Current);
      }
      catch (MySqlException) {
         //Assert.Fail("No exception should have been thrown");
      }
   }
}

This test demonstrates how the failure (this bug) occurs. 
There is another bug in the connector or in the server that sets the `driver.ServerStatus` = InTransaction even if the server is not in transaction. So first I get the NoNestedTransaction exception and then `Already Enlisted`. At this point the connection can no longer be used.

Vlad, thanks for all your help regarding transactions.

Verified as described with MySQL 5.0.38BK on WinXP Pro SP2 localhost using NET fw 2.0.50727 and c/NET SVN sources.
connection.cs, 279.
	public new MySqlTransaction BeginTransaction(IsolationLevel iso)
	{
            // First check to see if we are in a current transaction
            if ((driver.ServerStatus & ServerStatusFlags.InTransaction) != 0)
                throw ...

driver.ServerStatus is allways InTransaction.

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/26495

Fixed in 5.0.7

A note has been added to the 5.0.7 changelog.