Fixed: "Connection must be valid and open to commit transaction" with MySql.Data
Connection must be valid and open to commit transaction sent me to look at the connection, and the connection was fine. The transaction had already been committed. A second CommitAsync on it found the connection closed, and that's the only thing the driver checks first.
What I Was Doing
In my .NET C# Web API, with EF Core 10.0.14 and MySql.Data 26.10.0, one code path reached CommitAsync twice:
g.MapPost("/tx/commit-twice", async (InventoryDbContext db, CancellationToken cancellationToken) =>
{
await using var transaction = await db.Database.BeginTransactionAsync(cancellationToken);
await transaction.CommitAsync(cancellationToken);
await transaction.CommitAsync(cancellationToken);
return Results.Ok();
});
In real code, they're usually further apart: one in a helper, and one in its caller.
What I Expected
If anything, an error saying it was already committed.
What I Got
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-251709cc18304b3f3292cdea72427580-ed525619858249db-00"
}
System.InvalidOperationException: Connection must be valid and open to commit transaction
What I Checked
The transaction log. The first commit worked, and the second is what failed:
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[20210]
Committing transaction.
fail: Microsoft.EntityFrameworkCore.Database.Transaction[20205]
An error occurred using a transaction.
The driver's source. In MySqlTransaction.cs, the connection is the first thing it checks:
if (Connection == null || (Connection.State != ConnectionState.Open && !Connection.SoftClosed))
throw new InvalidOperationException("Connection must be valid and open to commit transaction");
if (!open)
throw new InvalidOperationException("Transaction has already been committed or is not pending");
There is a more helpful message, on the next line. It isn't reached, because the connection check comes first.
Why It Happens
EF Core opened the connection for the transaction, and closes it when the transaction ends. So after the first commit, the connection is closed, and the second commit trips the first check.
The same message is what you'd see if the connection really had been lost before the commit. So check the transaction log first: if there's a Committed transaction line before the failure, it's this one.
The Fix
Commit once. Keep the CommitAsync in the one place that owns the transaction:
g.MapPost("/tx/commit", async (InventoryDbContext db, TimeProvider clock, CancellationToken cancellationToken) =>
{
var now = clock.GetUtcNow().UtcDateTime;
await using var transaction = await db.Database.BeginTransactionAsync(cancellationToken);
db.Categories.Add(new Category { Name = "Stands", CreatedAtUtc = now });
await db.SaveChangesAsync(cancellationToken);
db.Categories.Add(new Category { Name = "Mounts", CreatedAtUtc = now });
await db.SaveChangesAsync(cancellationToken);
await transaction.CommitAsync(cancellationToken);
return Results.Ok();
});
After the Fix
HTTP/1.1 200 OK
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Debugging the connection string | Nothing's wrong with it | Read the transaction log |
| Committing in a helper and in its caller | This error, or work outside the transaction | One owner |
| Doing more work after the commit, in the same transaction | It isn't in a transaction any more | Commit last |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
No Committed transaction line before the failure | The connection really was lost | The dropped-connection fix |
Connection must be valid and open to rollback transaction | A rollback after the commit | The fix |
The connection is already in a transaction | Two begins | The fix |
Conclusion
This message usually means the transaction is over, not that the connection is bad. Check for an earlier Committed transaction in the log, and make sure there's one CommitAsync.
Additional Resources
- Using Transactions — transactions in EF Core.
- MySqlTransaction.cs — the checks shown above.
