Fixed: "Snapshot isolation level is not supported" with MySql.Data
Asking for IsolationLevel.Snapshot failed with NotSupportedException: Snapshot isolation level is not supported. Coming from SQL Server, that sounds like MySQL can't do it. It can: MySQL's default level already reads from a snapshot, under a different name.
What I Was Doing
In my .NET C# Web API, with EF Core 10.0.14 and MySql.Data 26.10.0, I wanted a transaction whose reads all saw one point in time:
g.MapGet("/iso/{level}", async (IsolationLevel level, InventoryDbContext db, CancellationToken cancellationToken) =>
{
var strategy = db.Database.CreateExecutionStrategy();
return await strategy.ExecuteAsync(
async ct =>
{
await using var transaction = await db.Database.BeginTransactionAsync(level, ct);
var count = await db.Categories.CountAsync(ct);
await Task.Delay(2000, ct);
await transaction.CommitAsync(ct);
return Results.Ok(new { level = level.ToString(), count });
},
cancellationToken);
});
GET http://localhost:5190/demo/iso/Snapshot
What I Expected
A transaction at that level.
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-b6d89c6588ee370da5177ad81236c031-c73080ae05c41cdc-00"
}
System.NotSupportedException: Snapshot isolation level is not supported
What I Checked
The driver's source. In MySqlConnection.cs, it refuses this level before it talks to the server:
case IsolationLevel.Snapshot:
Throw(new NotSupportedException(Resources.SnapshotNotSupported));
MySQL's levels. The isolation levels page lists four: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. And for REPEATABLE READ: "Consistent reads within the same transaction read the snapshot established by the first read."
Why It Happens
IsolationLevel.Snapshot is a value in .NET's enum because SQL Server has a level by that name. MySQL doesn't, so the driver has nothing to map it to. What I wanted from it, though, is what REPEATABLE READ does.
The Fix
Use RepeatableRead, or pass no level at all, since it's MySQL's default:
await using var transaction = await db.Database.BeginTransactionAsync(IsolationLevel.RepeatableRead, cancellationToken);
After the Fix
HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
{
"level": "RepeatableRead",
"count": 4
}
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Assuming a snapshot prevents every race | A write still sees the latest row | Use a conditional UPDATE |
Choosing Serializable to get a snapshot | Plain reads start taking locks | Use RepeatableRead |
| Copying SQL Server's level across | This error | Map it to RepeatableRead |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Which level am I on? | The default is REPEATABLE READ | Check it |
Chaos fails too | MySQL has no such level either | Use one of the four |
| The snapshot isn't where you expect | It's set by the first read, not BEGIN | Read earlier, or use START TRANSACTION WITH CONSISTENT SNAPSHOT |
Conclusion
MySql.Data refuses IsolationLevel.Snapshot because MySQL has no level by that name. RepeatableRead, the default, reads from a snapshot.
Additional Resources
- Transaction Isolation Levels — the four levels.
- Consistent Nonlocking Reads — how the snapshot works.
