How to avoid a "Nested transactions are not supported." error?

Question

I am using EF6 to do some pretty simple integration with a MySql database.

The Nested transactions are not supported. error occurs after I do the following:

1. Attempt to add a key that already exists... Which leads to the error: Duplicate entry 'asdf' for key 'UserName_UNIQUE'
2. Attempt to add anything afterwards... Which leads to the error: Nested transactions are not supported.

I guess I'm not sure what would be Nested about these two queries... What am I doing wrong:

And for some code

using (var db = C2SCore.BuildDatabaseContext())
{
  db.Users.Add(new UserProfile { UserName = UserName, Password = Password });
  db.SaveChanges(); // <- Errors occur here...
}

This snippet runs (as my the flow described above implies) for every UserProfile I add.

Solution 1

I have exactly the same problem. Try the following workaround by wrapping it into a TransactionScope:

using System.Transactions; // Add assembly in references

using (var db = C2SCore.BuildDatabaseContext())
{
  using (var tran = new TransactionScope())
  {
    db.Users.Add(new UserProfile { UserName = UserName, Password = Password });
    db.SaveChanges(); // <- Should work now after first exception
    tran.Complete();
  }
}

<package id="MySql.Data" version="6.8.3" targetFramework="net45" />
<package id="MySql.Data.Entities" version="6.8.3.0" targetFramework="net45" />

However, they are aware of it: http://bugs.mysql.com/bug.php?id=71502

Solution 2

Please I just want to reiterate "they are aware of it" but without people signing up and bumping the thread it is likely going to take longer to resolve when this is an urgent issue.

http://bugs.mysql.com/bug.php?id=71502

Solution 3

Try adding a db.Connection.Open() at the start and db.Connection.Close() at the end.

Solution 4

Seeing how it is now 2017 and this issue still exists I am going to post the only workaround I have been able to find that does not involve pulling the source code. Adding ConnectionReset=True to the connection string will resolve the issue. It should be noted this will incur an additional round trip to the database for every connection used in the connection pool.

Here is an example: server=localhost;port=3306;database=somedatabase;uid=someuser;password=itsasecret;ConnectionReset=True;

View more solutions

Comments

[rae1]

Where is the duplicate key code?

[poy]

@rae1 The UserName is the key... If that's what you're asking.

[user2864740]

What happens if explicitly opening up a wrapping TransactionScope? And there isn't a context open from elsewhere, is there?

[rae1]

Where does the duplicate key error occurs and where does the "Nested transactions ..." error occurs?

[poy]

@rae1 They both occur at the db.SaveChanges()

[poy]

@user2864740 I don't have the TransactionScope class available to me... Do you have to do something special to get it?

[user2864740]

@Andrew See TransactionScope (it is from System.Transactions); with an ambient TS present I believe all the EF operations will use it (and if it is aborted it can't be subsequently used). However, this sort of error normally comes when using a deferred LINQ/fetch operation - which is why I wonder if there is another context (that is indirectly accessible) lurking about.

[static-max]

I have the exactly the same problem. However, they are aware of it: bugs.mysql.com/bug.php?id=71502

[Luke]

I've got this problem too. Any solution!? Seems like this Mysql/C# stuff is a duff!

[user2864740]

There is a using around the context object and the code provided does not indicate any context leakage.

[AronVanAmmers]

This is the only workaround for this I found, thanks. It does require a lot of changes to your code however as every operation with a DbContext.SaveChanges() has to be wrapped in a TransactionScope. Could be useful if your app just has a few of those though.

[AronVanAmmers]

Correction: that doesn't work for me. The exception doesn't happen, but the data isn't saved to the database.