Do I need to set "ConnectionReset" parameter in the connection string when using isolation levels occasionally?
0
votes
1
answer
389
views
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.
Asked by vtortola
(101 rep)
Jan 27, 2020, 11:24 AM
Last activity: Apr 23, 2025, 08:09 PM
Last activity: Apr 23, 2025, 08:09 PM