0

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.

  • Just do the SET before each transaction; ignore the connection parameters. Very few programmers change the isolation level; would you care to describe your use case? – Rick James Jan 29 at 16:42
  • This is a large application which is not setting the transaction level in any of the hundreds of queries. Basically I have a table that define time periods, with a StartDate and an EndDate, where period overlapping is not allowed. When I add a new period, I check for existing periods in the expected range, and if there is not already one, I proceed with the INSERT. By using SERIALIZABLE I wanted to avoid a race condition where to transactions try at the same time to insert rows that overlaps. – vtortola Jan 31 at 8:10
0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.