MySQL Bugs: #45098: No support for nested TransactionScope scopes

Bug #45098	No support for nested TransactionScope scopes
Submitted:	26 May 2009 14:48	Modified:	19 Feb 2010 16:22
Reporter:	Pavel Bazanov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	5.2.5, 6.0.3	OS:	Any
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	Connector .NET, nested, TransactionScope

Description:
It seems like there is far not enough support for nested TransactionScopes.

See "How to repeat" secion for detailed tests and exceptions information.

Any comments?

How to repeat:
[Test]
public void TransactionScopeSupport()
{
	using (var externalTS = new TransactionScope())
	{
		ExecuteNonQuery("INSERT INTO customers SET Name = 'TEST2'");

		using (var innerTS = new TransactionScope())
		{
			ExecuteNonQuery("INSERT INTO customers SET Name = 'TEST3'");

			Assert.AreEqual(1, ExecuteScalarInt("SELECT COUNT(*) FROM customers WHERE Name = 'TEST3'"));
			Assert.AreEqual(1, ExecuteScalarInt("SELECT COUNT(*) FROM customers WHERE Name = 'TEST2'"));
		} // not completed

		// Exception: The operation is not valid for the state of the transaction 
		Assert.AreEqual(0, ExecuteScalarInt("SELECT COUNT(*) FROM customers WHERE Name = 'TEST3'"));
		Assert.AreEqual(1, ExecuteScalarInt("SELECT COUNT(*) FROM customers WHERE Name = 'TEST2'"));

		externalTS.Complete();
	}
}

[Test]
public void TransactionScopeSupport2()
{
	using (var externalTS = new TransactionScope())
	{
			ExecuteNonQuery("INSERT INTO customers SET Name = 'TEST2'");

		using (var innerTS = new TransactionScope())
		{
			ExecuteNonQuery("INSERT INTO customers SET Name = 'TEST3'");

			Assert.AreEqual(1, ExecuteScalarInt("SELECT COUNT(*) FROM customers WHERE Name = 'TEST3'"));
			Assert.AreEqual(1, ExecuteScalarInt("SELECT COUNT(*) FROM customers WHERE Name = 'TEST2'"));
		} // not completed

		externalTS.Complete();
	} // Exception on dispose: The transaction has aborted
}

private void ExecuteNonQuery(string query)
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = query;
	command.ExecuteNonQuery();
	command.Connection.Close();
}

private int ExecuteScalarInt(string query)
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = query;
	object result = command.ExecuteScalar();
	command.Connection.Close();
	return Convert.ToInt32(result);
}

private MySqlCommand OpenConnectionAndCreateCommand()
{
	string connectionString = DB.ConnectionString(false);
	MySqlConnection conn = new MySqlConnection(connectionString);
	conn.Open();
	MySqlCommand command = new MySqlCommand();
	command.Connection = conn;
	return command;
}

-----------------------------------------------

TransactionScopeSupport() fails with the following exception information:

System.Transactions.TransactionException: The operation is not valid for the state of the transaction.
at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
at MySql.Data.MySqlClient.MySqlConnection.EnlistTransaction(Transaction transaction) in Connection.cs: line 330
at MySql.Data.MySqlClient.MySqlConnection.Open() in Connection.cs: line 494
at RA.Tests.FreeTests.OpenConnectionAndCreateCommand() in FreeTests.cs: line 150
at RA.Tests.FreeTests.ExecuteScalarInt(String query) in FreeTests.cs: line 139
at RA.Tests.FreeTests.TransactionScopeSupport() in FreeTests.cs: line 103 

And TransactionScopeSupport2() fails with the following info:

System.Transactions.TransactionAbortedException: The transaction has aborted.
at System.Transactions.TransactionStateAborted.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 RA.Tests.FreeTests.TransactionScopeSupport2() in FreeTests.cs: line 126

changed severty

Guys, it's been a week since I posted this bug report. Have you stopped Connector/NET support and maintanence?

Can I get at least a couple-words comment?

Pavel, yes you can :-) Let me deal with backlog first.

Sure, 
I will be waiting..

Pavel, I do not see a bug... Not in c/NET nor anywhere else and here's why. MySQL server does not support nested transactions, at least not on same connection ID. Thus c/NET can not support it like you wish.

Am I missing something here?

To some extent (one level), savepoints in Falcon and InnoDB can mimic this...

Pavel, thinking of this I think using savepoints in c/NET would be a valid feature request for c/NET.

I am sorry nested transactions are not supported :(
Is it possible that it will be implemented in MySQL in viewable future?
As an enterprise applications developer I think that supporting nested transactions/transactionscopes is an absolutely necessary feature.
Using different connections is not very good because it can lead to deadlocks.
As for using savepoints in c/Net, how do you imagine it? How can it look to developer? Will savepoint be automatically inserted on nested TransactionScope?

Pavel:
> I am sorry nested transactions are not supported :(

Same here.

> Is it possible that it will be implemented in MySQL in viewable future?
As an enterprise applications developer I think that supporting nested
transactions/transactionscopes is an absolutely necessary feature.

Don't know so I'll assign to Reggie and see if he can provide some answers.

> As for using savepoints in c/Net, how do you imagine it? How can it look to developer? Will savepoint be automatically inserted on nested TransactionScope?

Don't really know. You can check on section 14 of the manual to get familiar with these things.

Reggie, verified as feature request to support savepoints in c/NET. Please make your ruling.

I am using Client connector 6.0.3 and the same problem persist.
I feel terrible about this.
regards Yves

Reggie, can we please hear any comments from you?

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

798 Vladislav Vaintroub	2009-11-18
      Bug #45098 : Implemented nested transaction scopes.
      We maintain a per-thread stack of scopes now, requied to handle nested
      scopes with RequiresNew or Suppress options.

The examples given actually work as they need to. Default constructor for TransactionScope is the same as using TransactionScopeOption.Required. In this case, the nested scope joins the ambient transaction. If nested scope does not complete, the whole ambient transaction is aborted.

There seems to be a confusion  about what TransactionScope is. I myself mixed nested transactions with savepoints. They are not savepoints, but rather either the same transaction as parent scope (TransactionScopeOption.Required) or different independent transaction (TransactionScopeOption.RequresNew) or they define non-transactional context (TransactionScopeOption.Suppress).

I committed a fix for RequiresNew and Suppress now. Please keep in mind, that in nested scope using one of these options one should use a connection different from the one used in parent scope.

A 'change' entry has been added to the 6.3.0 changelog:

Nested transaction scopes were not supported. MySQL Connector/NET now implements nested transaction scopes. A per-thread stack of scopes is maintained, which is necessary in order to handle nested scopes with the RequiresNew or Suppress options.

Thank you for fixing the issue. Now we have correct Exception to be thrown when necessary.
Also, I really misunderstood, previously, how TransactionScope should work.
If nested scope does not complete, outer scope should not complete either. So it seems we have correct behavior now (well, I didn't do much testing, just a few tests). I made a couple of tests in MS SQL and there is the same behavior as with MySql Connector.
Also, one can read about nested transaction scopes here:
http://www.pluralsight.com/community/blogs/jimjohn/archive/2005/06/18/11451.aspx
Maybe someone will find useful information there and will better understand transaction scopes.

Yep, I read that article at http://www.pluralsight.com/community/blogs/jimjohn/archive/2005/06/18/11451.aspx
before starting the fix:)