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
Triage: D5 (Feature request)

[31 Jan 2010 23:38] Pavel Bazanov
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
[1 Feb 2010 12:57] Pavel Bazanov
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.
[2 Feb 2010 10:15] Tonci Grgin
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.
[2 Feb 2010 10:18] Tonci Grgin
Test case used

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

[2 Feb 2010 11:38] Pavel Bazanov
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?
[2 Feb 2010 11:41] Pavel Bazanov
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).
[2 Feb 2010 12:00] Tonci Grgin
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx should be the correct link.
[22 Feb 2010 16:07] Reggie Burnett
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.
[22 Feb 2010 19:44] Pavel Bazanov
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?
[3 Jun 2010 13:52] ye wf
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
[25 Oct 2011 12:28] Matteo Spreafico
Any news?