Should TransactionScope.Complete after transaction is committed

[dtkujawski]

We have noticed that if we write data using TransactionScope and then read it immediately (in another thread) the value is the old value and not the updated value. But, if there is some delay (from 50ms to 500ms depending) then the value is the correctly updated value.

We have noticed that this happend irrespective of XATransactions true/false

Example Code:

using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }, TransactionScopeAsyncFlowOption.Enabled))
{
    using (var dbContext = new SampleDbContext())
    {
        var person = ...
        dbContext.Add(person);
        dbContext.SaveChanges();
    }
    transactionScope.Complete();
}

Additionally, if we don't use TransactionScope (EF will create it's own transaction around the save operation) then this doesn't happen; the read is the correct value (even happens to be the correct value before the SaveChanges goes to the next line - which makes sense because the DB has already been updated before that statement returns to the calling function).

We've tried the same thing with SQL Server (same exact code, except for connection string and UseSqlServer vs UseMySql) and the results are as expected - any results after the transaction Scope completes are always correct.

Feel free to close this issue if this is more a MySql issue and not a MySqlConnector issue. But, though I would start here and see if there was any thoughts on your part.

[bgrainger]

Do you know if this reproduces with just plain ADO.NET (TransactionScope, MySqlConnection, MySqlCommand, etc.) and no EF/Pomelo?

That might help narrow down if it's MySQL Server, MySqlConnector, EF Core, Pomelo, etc. (or perhaps some combination of them).

[dtkujawski]

Great idea, I tested with just ADO using TransactionScope and it has the same issue. Additionally, I tested with BeginTransaction instead of TransactionScope with ADO and it worked as expected (after commit all reads were correct). See code below, also added some other scenarios. Only when TransactionScope is used does it appear to be an issue (code below is likely repetitive and testing the same flows, but added for completeness):

INCORRECT: Using TransactionScope with ADO

using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }, TransactionScopeAsyncFlowOption.Enabled))
{
    using (var connection = new MySqlConnector.MySqlConnection(SampleDbContext.ConnectionStringMySql))
    {
        connection.Open();
        using (var command = new MySqlConnector.MySqlCommand("INSERT INTO `PERSON` ...", connection))
        {
            var rows = command.ExecuteNonQuery();
        }
        connection.Close();
    }
    transactionScope.Complete();
}

CORRECT: Using BeginTransaction with ADO

using (var connection = new MySqlConnector.MySqlConnection(SampleDbContext.ConnectionStringMySql))
{
    connection.Open();
    var transaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
    using (var command = new MySqlConnector.MySqlCommand("INSERT INTO `PERSON` ...", connection, transaction))
    {
        var rows = command.ExecuteNonQuery();
    }
    transaction.Commit();
    connection.Close();
}

ALSO CORRECT: EF using not transaction scope (internally creates transaction wrapper around call)

using (var dbContext = new SampleDbContext())
{
    var person = ...
    dbContext.Add(person);
    dbContext.SaveChanges();
}

ALSO CORRECT: EF using manual transactions with BeginTransaction

using (var dbContext = new SampleDbContext())
{
    var person = ...
    dbContext.Add(person);
    var transaction = dbContext.Database.BeginTransaction();
    dbContext.SaveChanges();
    transaction.Commit();
}

[bgrainger]

INCORRECT: Using TransactionScope with ADO

What's the incorrect behavior? Does a read not return the data that was inserted?

Do you have a self-contained example that reproduces the problem?

[bgrainger]

I can't reproduce this even if I force the background thread to open the connection first then wait to query the database until immediately after the main thread completes the TransactionScope. It always reads the correct data.

[dtkujawski]

@bgrainger First, thank you for your help on this! I really appreciate it.

As requested, see attached stripped down project that demonstrates what we are seeing (ZIP file). Basically there are two methods, one that INSERTS a record (with Identity column) and one that reads for the latest record's ID. The program spins up a Parallels set of threads and all of them will read while only thread with counter #100 will do the insert (the idea is that this eliminates some variability in program startup). [note: the table DDL is provided for both MySql and SqlServer]

**NOTE: The DbContext class has some constants at the top that you can change for your environment (connection strings, schema name, etc). **

The output of the program will be the VS diagnostics output window (DEBUG) - feel free to change it to a Console.Write if you prefer. You will get log entries for when the thread starts, scope starts, etc. For testing, I just run the program and watch the output and take note of the first ID. Once the output shows the second ID then I just abort the program an look at the output in my text editor.

In SQL Server, after the INSERT is committed; you'll see that no READ after that point is the first ID, they all are the 2nd ID. In actuality, it'd be ok if the scope was already created and a read after the commit is still the first ID (like with snapshot isolation) - I expect those queries which are already in-flight to be still the 1st ID. The important point is that any NEW THREADS created after the transaction is committed should be the 2nd ID not the 1st ID. With SQL Server this is certainly the case, but with MySQL there is a period of time after the commit where even new threads created (with new transaction scopes and new queries) will read the 1st value still.

I'm attaching my log from SQL Server and my log from MySQL. In the MySQL log, some examples of threads which STARTED after the COMMIT statement which still returned the wrong value are (you can search these in your favorite text editor to see these):
00740:003, 00740:003, 08180:016, 00753:022, 01960:005, 05683:012

mysql.txt
sqlserver.txt
TestTransactions.zip

[dtkujawski]

Upon further inspection, it appears to be a behavior difference in how SQL Server and MySql handle locking between the COMPLETE command and the DISPOSE method. I'll go ahead and close this issue