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

Bug #45098	No support for nested TransactionScope scopes
Submitted:	26 May 16:48	Modified:	18 Nov 15:23
Reporter:	Pavel Bazanov
Status:	Patch pending
Category:	Connector/Net	Severity:	S4 (Feature request)
Version:	5.2.5, 6.0.3	OS:	Any
Assigned to:	Vladislav Vaintroub	Target Version:
Tags:	Connector .NET, TransactionScope, nested
Triage:	D5 (Feature request)

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.