How to Set the Transaction Isolation Level in EF Core

BeginTransactionAsync takes an isolation level as its first argument, and that's the whole API. The useful part is knowing what gets sent, how to confirm that MySQL really is using it, and which levels the driver refuses.

Prerequisites

• A .NET C# project using EF Core. I used EF Core 10.0.14, MySql.Data 26.10.0 and MySQL 9.7.4.
• What the levels do. READ COMMITTED vs REPEATABLE READ in MySQL shows the difference between the two you're most likely to choose.

Step 1: Pass the Level

IsolationLevel here is System.Data.IsolationLevel:

An endpoint

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);
});

Step 2: See What's Sent

For READ COMMITTED:

Output (MySQL's log)

+------------------------------------------------+
| statement                                      |
+------------------------------------------------+
| SET TRANSACTION ISOLATION LEVEL READ COMMITTED |
| BEGIN                                          |
| SELECT COUNT(*) FROM `categories` AS `c`       |
| COMMIT                                         |
+------------------------------------------------+

The driver sends SET TRANSACTION ISOLATION LEVEL before each BEGIN, which applies to the next transaction only. So this doesn't affect other transactions on the connection. With no level, it sends REPEATABLE READ, MySQL's default.

Step 3: Confirm It in MySQL

While the transaction was open, another session asked information_schema.innodb_trx:

Output (ReadCommitted)

+---------------------+
| trx_isolation_level |
+---------------------+
| READ COMMITTED      |
+---------------------+

Output (Serializable)

+---------------------+
| trx_isolation_level |
+---------------------+
| SERIALIZABLE        |
+---------------------+

Step 4: Know What Maps to What

IsolationLevel	MySQL
ReadUncommitted	READ UNCOMMITTED
ReadCommitted	READ COMMITTED
RepeatableRead, or Unspecified	REPEATABLE READ
Serializable	SERIALIZABLE
Snapshot	Refused with NotSupportedException
Chaos	Refused with NotSupportedException

Common Mistakes

Mistake	What happens	Do this instead
Raising the level to fix a race	Plain reads lock under Serializable, and it can still deadlock	Use a conditional UPDATE
Using the wrong IsolationLevel	System.Transactions has one too	Use System.Data
Expecting it to apply to SaveChangesAsync alone	That call starts its own, at the default	Start one with the level

Troubleshooting

Symptom	Likely cause	Fix
Snapshot isolation level is not supported	MySQL has no such level	The fix
The level seems to be ignored	You checked another transaction	Check while it's open
does not support user-initiated transactions	Retries are on	The fix

Conclusion

Pass a System.Data.IsolationLevel to BeginTransactionAsync. The driver sets it before BEGIN, for that transaction only, and innodb_trx shows whether it took effect.

Additional Resources

• Transaction Isolation Levels — the four levels.
• SET TRANSACTION Statement — the statement the driver sends.

Related

• READ COMMITTED vs REPEATABLE READ in MySQL
• How to Check and Change the Transaction Isolation Level in MySQL
• Fixed: "Snapshot isolation level is not supported" with MySql.Data