Do I need to set "ConnectionReset" parameter in the connection string when using isolation levels occasionally?

Question

I have a MySql 5.6.44, and an dotnet application using Mysql.Data 8.0.12.

The application code has not specified any isolation level till now because the default one was enough. Suddenly we need to do a SERIALIZABLE transaction in a new feature, and only the transactions in that part of the code specify such level.

In dotnet, connections to the DB are pooled, so after the connection usage is over, the connection gets back to the pool. I have the concern, that the isolation level set for that operations remains in the connection affecting other transactions in the code which do not need such high level.

According to the component documentation, there is an optional connection string parameter that indicates if the connection state needs to be reset:

ConnectionReset , Connection Reset Default: false

If true, the connection state is reset when it is retrieved from the pool. The default value of false avoids making an additional server round trip when obtaining a connection, but the connection state is not reset.

Checking the decompiled component code, it seems that transaction levels are set with SESSION scope (MySqlConnection.cs:1238):

MySqlTransaction mySqlTransaction = new MySqlTransaction(this, iso);
MySqlCommand mySqlCommand = new MySqlCommand("", this);
mySqlCommand.CommandText = "SET SESSION TRANSACTION ISOLATION LEVEL ";
switch (iso)
{
  case System.Data.IsolationLevel.Chaos:
    this.Throw((Exception) new NotSupportedException(Resources.ChaosNotSu
    break;
  case System.Data.IsolationLevel.ReadUncommitted:
    mySqlCommand.CommandText += "READ UNCOMMITTED";
    break;
 ...

According to MySql documentation about transactions:

With the SESSION keyword:

The statement applies to all subsequent transactions performed within the current session.

So if I understand correctly, the isolation level set for one operation will affect other operations in our code that are not specifying an isolation level themselves, since the connections are being returned to the pool without reseting.

So being this the case, I should use the "ConnectionReset" parameter in our connection strings, am I right?

PS: The open source MySql connector (which we do not use) seems that is setting true by default in the ConnectionReset parameter by default, which makes total sense to me.

Answer 1

Probably the following allows you to use any isolation mode for your application:

BEGIN;
SELECT ... FOR UPDATE;   -- necessary if about to change a row
...
UPDATE ...;
UPDATE ...;
COMMIT;