MySQL Bugs: #50773: Multiple simultaneous connections inside the same transaction are not supported.

Bug #50773	Multiple simultaneous connections inside the same transaction are not supported.
Submitted:	31 Jan 2010 23:38	Modified:	22 Feb 2010 19:44
Reporter:	Pavel Bazanov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	trunk	OS:	Any
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	TransactionScope

Description:
Hello,
If I try to simultaneously create 2 connections inside the same TransactionScope I get the following exception:
"Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported."

So, why not just automatically use same connection in TransactionScope? For example, MySqlDataProvide.CreateConnectionObject() could return existing connection if it's inside TransactionScope.

For example my code can look like this:

...
using(var transactionScope = new TransactionScope())
{
  CustomersAccessor.InsertCustomer(customer);
  CustomersAccessor.SaveAdditionalDetails(customer);
}
...
Each acessor's method in DAL is using DbManager class, for example:

public void InsertCustomer(Customer customer)
{
  using(var db = new DbManager())
  {
    ...
  }
}

And inside the constructor of DbManager class:

_conn = _dataProvider.CreateConnectionObject();
_conn.ConnectionString = _connectionString;
_conn.Open(); // exception here

Currently, because of bad TransactionScope support I had to create my own simulation of TransactionScope - LocalTransaction class.

In DbManager's constructor it checks if it's inside LocalTransaction and if yes - takes existing connection from TLS.

How to repeat:
the bug can easily be repeated

PS. I just want to add, that TransactionScope support is CRITICAL for serious applications. If there is no support for TransactionScope then people have to make their own DbManager's and TransactionScope implementations. Otherwise normal development of serious business applications is just not possible with MySql :(
I would give highest priority to proper TransactionScope support.

Hi Pavel.

First of all I do not see this report as S2 but rather S4 (feature request).

Although I have a problem imagining a use case where this is absolutely necessary and being that each connection is given it's own ConnectionId by which MySQL server determines it's transaction scope (making this very hard to implement) I have to verify the problem after reading
http://msdn.microsoft.com/en-us/librarysystem.transactions.transactionscope.aspx
and other pages where it is clear this is supported in TransactionScope class.

Test case used

Attachment: Bug50773.txt (text/plain), 2.74 KiB.

Hi Tonci,

One of the use cases can be lazy loading: inside a DAL-method you access a lazy-loaded property and that leads to calling another DAL-method which creates connection (takes it from pool) and loads the object from the database.

Another use case is very similar to the first one, except that one DAL-method directly calls another DAL-method. Some people think it's not a good design, but anyway it has a right to exist.

PS. Is it something with MSDN server or your link to MSDN site is incorrect?

Tonci, and thanks for the test case, because in my initial post I had a wrong pseudo-code example (there is no multiple simultaneous connections inside the same transaction in my example).

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx should be the correct link.

Pavel

That is exactly how it does work right now. However, for that to work you have to close the first connection otherwise it can't reuse it.  Note that the connection is not actually closed.  Please see the ReusingSameConnection test case in the test suite we ship in our source distribution.

We realize this is not the same as true XA support.

Hi Reggie,

>However, for that to work you have to close the first connection otherwise it can't reuse it.

I know that I have to close the first connection, but sometimes it's not acceptable. Why can't you use (return) the existing connection?

The bug is not clean?
my test is the error. It's use Tonci test case.

MySql5.1.4 and connector 6.2.3

For your answer.
thanks

Any news?