Ability to use session transaction isolation level #801
Description
The transaction isolation level (MySQL docs) can be set one of two ways:
SET SESSION TRANSACTION ISOLATION LEVELSET TRANSACTION ISOLATION LEVEL
(1) changes the isolation level for all future transactions issued on the current connection (unless it's changed again); (2) changes it for just the next transaction.
Connector/NET does (1), MySqlConnector does (2).
(2) seems like a better fit for the ADO.NET API, because BeginTransaction(IsolationLevel) clearly just affects that specific transaction. However, it's reportedly incompatible with ProxySQL: PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#1043 (comment), sysown/proxysql#2305 (comment).
If all usages of transactions are through the ADO.NET API, then the difference should not be observable because the isolation level will always be set before each transaction. But if code is manually executing START TRANSACTION, it would be affected by the server default or session isolation level.
We could add a new connection string option that controls whether SESSION isolation levels are used. Alternatively, because the difference probably isn't observable (or may even be relied on by code being ported from Connector/NET), we could change change the default implementation in MySqlConnector for everyone.