Bug #71502 EntityFramework Database.BeginTransaction: Nested Transaction are not supported
Submitted: 28 Jan 2014 17:50 Modified: 14 Dec 2015 23:27
Reporter: Charles Loh Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.8.3 OS:Microsoft Windows
Assigned to: Roberto Ezequiel Garcia Ballesteros CPU Architecture:Any

[28 Jan 2014 17:50] Charles Loh
Description:
It looks like connector net is not disposing of the transaction (returned by DBContext.Database.BeginTransaction()) when exiting a using block.

While MSDN documentation states the following, "Dispose should rollback the transaction. However, the behavior of Dispose is provider specific, and should not replace calling Rollback.", should the transaction be implicitly disposed?

How to repeat:
The same code block is executed twice.

1st block) Throws the expected "MySqlException: You have an error in your SQL syntax"

2nd block) Throws "Nested transactions are not supported" when BeginTransaction is called

            using (myContext ctx = new myContext())
            {
                using (var trans = ctx.Database.BeginTransaction())
                {
                    try
                    {
                        ctx.Database.ExecuteSqlCommand("update xxx");
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        
                    }
                }
            }

            using (myContext ctx = new myContext())
            {
                //*********Exception "Nested transactions are not supported" thrown on the next line"
                using (var trans = ctx.Database.BeginTransaction())
                {
                    try
                    {
                        ctx.Database.ExecuteSqlCommand("update xxx");

                        trans.Commit();
                    }
                    catch (Exception)
                    {

                    }
                }
            }
[7 Jun 2014 18:15] Rikin Patel
I am also facing issue....
using (var objectContext = new FabriCareEntities())
{
product.Name = "p1";
context.Products.AddObject(product);
context.SaveChanges();
}

After that i got exception for unique key. This is proper.

Now i changed
using (var objectContext = new FabriCareEntities())
{
product.Name = "p2";
context.Products.AddObject(product);
context.SaveChanges();
}

Now I Got exception: 
An error occurred while starting a transaction on the provider connection. See the inner exception for details.

InnerException:
Nested transactions are not supported.
[25 Jan 2015 2:26] Ted Eiles
This it a critical issue.  Duplicate key exceptions are common in our workload because we support idempotent messages in our distributed environment. 

The secondary nested transaction issue creates inconsistent behavior
[27 Jan 2015 6:02] Ted Eiles
This bug exists in 6.9.x too.
[20 Feb 2015 0:44] Craig McNicholas
Can confirm still seeing this in 6.9.3
[27 Feb 2015 15:54] Raif Atef
This bug is due to a bug in the MySqlTransaction class.

It looks there is/was(?) an effort to disconnect the class from inheriting from DbTransaction for WinRT support (?) which was not completed before 6.9.5 was shipped (and maybe even before that).

So the class is defined as partial in MySql.Data\transaction.cs and we can see the Dispose method defined without the override keyword. 

Then in MySql.Data\extension\NonRT\MySqlTransaction.cs, the class is specified to inherit from DbTransaction.

This immediately introduces a bug since EntityFramework accesses the MySqlTransaction instance through a DbTransaction reference and because the Dispose method is not marked as override, the Dispose method of MySqlTransaction is not called, so the rollback (and other cleanup) is skipped.

Strangely enough, the Rollback method IS defined correctly with the override keyword, which indicates the release seems to be created from a half-finished work.

The only solution is to abstract the DbContext class with your own wrapper which provides its own BeginTransaction, and return a wrapper around Entity Framework's DbContextTransaction instance and keep track yourself if Rollback is needed or not.

Example:

public interface IDbContextTransaction : IDisposable
{
    void Commit();
}

sealed class DbContextTransactionAdapter : IDbContextTransaction
{
    readonly DbContextTransaction _transaction;
    bool _commited;

    public DbContextTransactionAdapter(DbContextTransaction transaction)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        this._transaction = transaction;
    }

    public void Commit()
    {
        this._transaction.Commit();
        this._commited = true;
    }

    public void Dispose()
    {
        if (!this._commited)
        {
            try
            {
                this._transaction.Rollback();
            }
            catch (Exception) {}
        }

        this._transaction.Dispose();
    }
}
[27 Feb 2015 15:59] Raif Atef
Of course, if you can recompile the connector, the correct solution is just add override keyword to Dispose(bool) method and change the accessor to protected (why it is internal is unknown to me).
[25 Mar 2015 17:00] Aron van Ammers
Still present in 6.9.6.

I created a complete example project here: https://github.com/AronVanAmmers/ReproduceMysqlNestedTransactionException

MySQL / Oracle, please fix this very basic bug.
[24 Jul 2015 20:55] Ulrich Strauss
I tried the solution from Raif Atef and altered the source.
But that does not fix it.

first call -> ok ( Primary key exception )
second call -> Nested Transaction-Problem.

Reproducible 100%
[21 Sep 2015 10:19] Justin Wyer
How is this bug non critical? Connection pools are unusable in C#, which means MySQL is unusable from C#...
[28 Sep 2015 17:04] Matt Stubblefield
Please make this critical!
[28 Sep 2015 17:38] Ulrich Strauss
Working workaround for me:

public class DatabaseContext : DbContext
    {
        public DatabaseContext() : base("connectionstring")
        {
            Database.Connection.Close();
            Database.Connection.Open();
        }
}

...

using (DatabaseContext dbctx = new DatabaseContext() )
{
my stuff;
}
[29 Sep 2015 2:23] Charles Loh
Amended severity to Critical.
[29 Sep 2015 15:19] Matt Stubblefield
Ulrich Strauss: that workaround isn't threadsafe and could have negative impact on connection pooling.

If people aren't explicitly using transactions, then one work around would be to override SaveChanges/SaveChangesAsync and commit/rollback the transaction as needed.
[16 Dec 2015 16:32] Michael Daly
Just after running into this nasty one. 

We'll have to now close all connections directly after using a transaction.

Transactions are on the critical path of things that are required when using databases. 

Any signs of this progressing up to the top of the development pipeline?

Thanks,
M
[11 Jan 2016 2:42] Jonathon Coombes
A work around for this bug is to add in the connection string "connection
reset=true"
[8 Mar 2016 19:20] Vasil Kostov
I request it to be fixed. This is really bugging the whole application and making us do workarounds which result in ugly code. This could be error prone in some aspects! It's been 2 years now.
[17 Mar 2016 20:07] Rune Antonsen
Please fix this.
[18 Mar 2016 0:51] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=80785 marked as duplicate of this one.
[22 Mar 2016 10:13] Craig McNicholas
Perhaps a comment a day will help annoy someone enough to fix it. This is still a massive issue for us and a pain in the ass.
[5 Apr 2016 17:06] Jobert Bijl
Why is this not fixed?
This is a real MySQL bummer.
[13 Apr 2016 10:47] Mike Roibu
This really needs to be fixed, it's causing so many issues.
[22 Apr 2016 10:35] Radion Corinovschi
Same here, using Connector v6.9.8
[21 Oct 2016 20:38] Jeff Bromberger
This major and obvious bug has been outstanding for nearly 3 years.  I don't understand how anybody can use MySQL/Entity Framework in a production environment with this type of support.  Are there any plans to EVER fix this??
[22 Oct 2016 11:14] Jobert Bijl
I agree that this is a real pain.
It makes use of MySQL with EntityFramework impossible.
Therefore it drives you to use MSSQL.

I don't understand either why this issue has not been fixed or at least addressed with some kind of workaround.