Fixed: "Connection must be valid and open to rollback transaction" with MySql.Data

My log said Connection must be valid and open to rollback transaction, and that wasn't what had gone wrong. Something had failed after the commit, my catch tried to roll back a transaction that was already finished, and the new exception replaced the real one. The fix also removes code: you rarely need RollbackAsync at all.

What I Was Doing

In my .NET C# Web API, with EF Core 10.0.14 and MySql.Data 26.10.0, I had the common try, commit, catch, roll back shape. The try also covered some work after the commit:

An endpoint

g.MapPost("/tx/rollback-after-commit", async (InventoryDbContext db, TimeProvider clock, CancellationToken cancellationToken) =>
{
    await using var transaction = await db.Database.BeginTransactionAsync(cancellationToken);

    try
    {
        await transaction.CommitAsync(cancellationToken);

        throw new InvalidOperationException("Something after the commit failed.");
    }
    catch (InvalidOperationException)
    {
        await transaction.RollbackAsync(cancellationToken);
        throw;
    }
});

What I Expected

The real error in my log, Something after the commit failed.

What I Got

Response

HTTP/1.1 500 Internal Server Error
Content-Type: application/problem+json

{
  "type": "https://tools.ietf.org/html/rfc9110#section-15.6.1",
  "title": "An error occurred while processing your request.",
  "status": 500,
  "traceId": "00-86757526461c48be8a302039502c1a1b-4a0a7aa113e08eb1-00"
}

API log

System.InvalidOperationException: Connection must be valid and open to rollback transaction

The real message is nowhere in the log.

What I Checked

The transaction log. The commit had worked:

API log

dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20200]
      Began transaction with isolation level 'Unspecified'.
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20210]
      Committing transaction.
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20202]
      Committed transaction.
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20211]
      Rolling back transaction.
fail: Microsoft.EntityFrameworkCore.Database.Transaction[20205]
      An error occurred using a transaction.

The driver's source. RollbackInternalAsync in MySqlTransaction.cs checks the connection first, as the commit does, and after a commit the connection is closed.

What await using does by itself. A request that leaves without committing gets a rollback from the transaction's DisposeAsync. The general query log for one, from How to Use a Transaction in EF Core with BeginTransactionAsync:

Output

+------------------------------------------------------------------------+
| statement                                                              |
+------------------------------------------------------------------------+
| SET TRANSACTION ISOLATION LEVEL REPEATABLE READ                        |
| BEGIN                                                                  |
| SAVEPOINT `__EFSavePoint`                                              |
| INSERT INTO `categories` (`CreatedAtUtc`, `Name`) VALUES (timestamp('2 |
| SELECT `Id` FROM `categories` WHERE ROW_COUNT() = 1 AND `Id` = LAST_IN |
| RELEASE SAVEPOINT `__EFSavePoint`                                      |
| SAVEPOINT `__EFSavePoint`                                              |
| INSERT INTO `categories` (`CreatedAtUtc`, `Name`) VALUES (timestamp('2 |
| SELECT `Id` FROM `categories` WHERE ROW_COUNT() = 1 AND `Id` = LAST_IN |
| RELEASE SAVEPOINT `__EFSavePoint`                                      |
| ROLLBACK                                                               |
+------------------------------------------------------------------------+

Why It Happens

The commit ended the transaction, and later code threw. The catch covers both, so it can't tell which one failed. RollbackAsync then throws from inside the catch, and the new exception replaces the one you needed.

The Fix

Remove the try and the catch, and let await using roll back. Keep anything that can fail after the commit away from the transaction:

An endpoint (excerpt)

await using var transaction = await db.Database.BeginTransactionAsync(cancellationToken);

// ...the work...

await transaction.CommitAsync(cancellationToken);

// Anything after this line isn't part of the transaction.

If the work throws before the commit, await using rolls back, and the original exception reaches your log.

After the Fix

A failing SaveChangesAsync, with no catch, now reports what really went wrong, and nothing is saved:

API log

---> MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'Cables' for key 'categories.IX_categories_Name'

Output (the row the transaction added before that failure)

+--------+
| Stands |
+--------+
|      0 |
+--------+

Common Mistakes

Mistake	What happens	Do this instead
A rollback in every catch	It can hide the real error	Rely on await using
Doing an email or a call inside the try	Its failure looks like a database failure	Do it after the commit
Swallowing the first exception	You can't tell what failed	Let it go up

Troubleshooting

Symptom	Likely cause	Fix
No real error in the log, only this one	The catch replaced it	Remove the catch
You need to roll back and carry on	The transaction must end first	Call RollbackAsync before any commit, as a plain branch
Connection must be valid and open to commit transaction	Two commits	The fix

Conclusion

This message usually means you rolled back something that had already committed, and it's hiding the error you wanted. await using rolls back by itself, so you rarely need RollbackAsync, and work after the commit doesn't belong in that try.

Additional Resources

• Using Transactions — transactions in EF Core.
• MySqlTransaction.cs — the checks.

Related

• How to Use a Transaction in EF Core with BeginTransactionAsync
• Why You Should Not Do Slow Work Inside a Database Transaction
• Fixed: "Connection must be valid and open to commit transaction" with MySql.Data