Fixed: MySQL Connection Stays on SERIALIZABLE After TransactionScope with MySql.Data

I used a TransactionScope once, and afterwards a plain query on the same pooled connection reported its isolation level as SERIALIZABLE. The scope's default level is Serializable, the driver sets it for the whole session, and nothing puts it back. Passing the level you want avoids all of it.

What I Was Doing

My .NET C# Web API uses EF Core 10.0.14 and MySql.Data 26.10.0, and one endpoint uses a TransactionScope with no options:

An endpoint (excerpt)

using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);

A second endpoint asks MySQL for the connection's level:

An endpoint

g.MapGet("/scope-level", async (InventoryDbContext db, CancellationToken cancellationToken) =>
    Results.Ok(new
    {
        level = await db.Database
            .SqlQuery<string>($"SELECT @@transaction_isolation AS Value")
            .FirstAsync(cancellationToken),
    }));

With a pool of one connection, every request gets the same one. I called the second endpoint, then the first, then the second.

What I Expected

REPEATABLE-READ, MySQL's default, before and after.

What I Got

Output

{"level":"REPEATABLE-READ"}
scope 200
{"level":"SERIALIZABLE"}

What I Checked

What the driver sent. MySQL's general query log, for the scope:

Output

+--------------+------------------------------------------------------------------------+
| command_type | statement                                                              |
+--------------+------------------------------------------------------------------------+
| Init DB      | inventory                                                              |
| Query        | SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE                   |
| Query        | BEGIN                                                                  |
| Query        | INSERT INTO `categories` (`CreatedAtUtc`, `Name`) VALUES (timestamp('2 |
| Query        | SELECT `Id` FROM `categories` WHERE ROW_COUNT() = 1 AND `Id` = LAST_IN |
| Init DB      | inventory                                                              |
| Query        | UPDATE `products` AS `p` SET `ReorderLevel` = `p`.`ReorderLevel` + 1 W |
| Query        | ROLLBACK                                                               |
+--------------+------------------------------------------------------------------------+

SET SESSION applies to the connection, not only to the next transaction.

The scope's default. The TransactionOptions.IsolationLevel default is Serializable, and EF Core's log says so too: Enlisted in an ambient transaction with isolation level 'Serializable'.

Whether pooling should reset it. Oracle's connection options say of ConnectionReset: "Default: false", and "The default value of false avoids making an additional server round trip when obtaining a connection, but the connection state is not reset."

Why It Happens

System.Transactions defaults to its strictest level, and the driver applies it to the session, not to one transaction. Then the connection goes back to the pool as it is. A plain BeginTransactionAsync is protected, because the driver sends its own level before each one, so the effect is limited to statements outside a transaction. It's still a setting nobody asked for, left behind on a connection that another request will use.

The Fix

Say which level you want:

An endpoint (excerpt)

using var scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead },
    TransactionScopeAsyncFlowOption.Enabled);

If you don't need an ambient transaction, BeginTransactionAsync doesn't have this problem at all.

After the Fix

Output (MySQL's log)

+--------------+------------------------------------------------------------------------+
| command_type | statement                                                              |
+--------------+------------------------------------------------------------------------+
| Init DB      | inventory                                                              |
| Query        | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ                |
| Query        | BEGIN                                                                  |
| Query        | UPDATE `products` AS `p` SET `ReorderLevel` = `p`.`ReorderLevel` + 1 W |
| Query        | COMMIT                                                                 |
+--------------+------------------------------------------------------------------------+

Output (the level afterwards)

{"level":"REPEATABLE-READ"}

Common Mistakes

Mistake	What happens	Do this instead
Using new TransactionScope() with no options	Serializable	Pass TransactionOptions
Turning on ConnectionReset for every connection	A round trip for each one	Set the level you want
Assuming the scope's level ends with the scope	It stays on the pooled connection	Check @@transaction_isolation

Troubleshooting

Symptom	Likely cause	Fix
More locks or deadlocks after adding a scope	SERIALIZABLE turns plain reads into locking ones inside a transaction	Use RepeatableRead
does not support user-initiated transactions	The scope is outside the strategy	The fix
It only shows on some requests	Different pooled connections	Test with Maximum Pool Size=1

Conclusion

A default TransactionScope is Serializable, and MySql.Data sets that on the session, where it stays. Pass TransactionOptions with the level you want.

Additional Resources

• TransactionOptions.IsolationLevel — the default.
• Connector/NET Connection Options — ConnectionReset.

Related

• How to Use TransactionScope with EF Core and MySQL
• How to Set the Transaction Isolation Level in EF Core
• How to Check and Change the Transaction Isolation Level in MySQL