C# – Using TransactionScope with MySQL and Read Lock

cMySQLsubsonictransactionstransactionscope

I have the following situation:

If have a MySQL db with a InnoDB table which I use to store unique numbers.
I start a transaction, read the value (eg. 1000471), store this value in another table and update the incremented value (100472). Now I want to avoid that somebody else even reads the value while my transaction is running.

If I would use plain MySQL I would do something like this:

Exceute("LOCK tbl1 READ");
Execute("SELECT … from tbl1");
Execute("INSERT into tbl2");
Execute("UNLOCK TABLES");

but since I am using SubSonic as a DAL and the code should be independent from mysql, I have to use the TransactionScope.

My code:

        TransactionOptions TransOpt = new TransactionOptions();
        TransOpt.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
        TransOpt.Timeout = new TimeSpan(0, 2, 0);

        using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, TransOpt))
        {

             // Select Row from tbl1

             // Do something

             ts.Complete();
        }

According to the help of TransactionOptions

system.transactions.isolationlevel

The effect I want to reach could be implemented with IsolationLevel.ReadCommitted, but I can still read the row from outside the transaction (If I try to change it, I get a lock, so the transaction is working)

Does anybody has a suggestion? Is a read lock even possible with TransactionScope

Best Answer

If anyone is interested, this is how TransactionOptions affect MySql:

Lets say I have two methods.

Method1 starts a transaction, selects a row from my table, increments the value and updates the table.

Method2 is the same, but between select and update I added a sleep of 1000ms.

Now imagine I have the following code:

    Private Sub Button1_Click(sender as Object, e as System.EventArgs) Handles Button1.Click

        Dim thread1 As New Threading.Thread(AddressOf Method1)
        Dim thread2 As New Threading.Thread(AddressOf Method2)

        thread2.Start() // I start thread 2 first, because this one sleeps
        thread1.Start()

    End Sub

Without transactions this would happen:
thread2 starts, reads the value 5, then sleeps,
thread1 starts, reads the value 5, updates the value to 6,
thread2 updates the value to 6, too.

Effect: I have the unique number two times.

What I want:
thread2 starts, reads the value 5, then sleeps,
thread1 starts, trys to reads the value, but get a lock and sleeps,
thread2 updates the value to 6,
thread1 continues, reads the value 6, updates the value to 7

That's how to start transaction with the TransactionScope:

        TransactionOptions Opts = new TransactionOptions();
        Opts.IsolationLevel = IsolationLevel.ReadUncommitted;

        // start Transaction
        using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, Opts))
        {
            // Do your work and call complete
            ts.Complete();
        }

That can even manage distributed transactions. If an Exception is thrown ts.Complete is never called and the Dispose() Part of the Scope rolls back the transaction.

Here's an overview how the different IsolationLevels affect the transaction:

  • IsolationLevel.Chaos
    Throws a NotSupportedException - Chaos isolation level is not supported

  • IsolationLevel.ReadCommited
    The transactions do not interfere each other (two identical reads, bad)

  • IsolationLevel.ReadUncommitted
    The transactions do not interfere each other (two identical reads, bad)

  • IsolationLevel.RepeatableRead
    The transactions do not interfere each other (two identical reads, bad)

  • IsolationLevel.Serializable
    Throws a MySqlException - Deadlock found when trying to get lock; try restarting transaction during Update

  • IsolationLevel.Snapshot
    Throws a MySqlException - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 during Connection.Open()

  • IsolationLevel.Unspecified
    Throws a MySqlException - Deadlock found when trying to get lock; try restarting transaction during Update

  • TransactionOptions not set
    Throws a MySqlException - Deadlock found when trying to get lock; try restarting transaction during Update

Related Topic