Using TransactionScope with MySQL and Read Lock

Question

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

Answer 1

My first guess was to use SELECT FOR UPDATE and after a quick search I found a page about locking reads in the MySQL 5 reference.

If I understand correctly this is independent from the isolation level used. And take care - the isolation level just tells how the current transaction is affected by changes in other transactions. It does not tell what other transactions can do. However, more restricting locks are required for higher isolation levels.

Answer 2

As I didn't find a way to lock a row for reading with InnoDB and TransactionScope (I may be wrong) this should work:

If I run two Transactions simultaniously (without TransactionOptions) and one finishes, the other cannot compleate because of a "Deadlock" Exception.

Instead of avoiding this exception it seems to be best practice, according to the MySQL documentation, to expect a deadlock and restart the transaction.

if you set:

    TransactionOptions TransOpt = new TransactionOptions();
    TransOpt.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

for your transaction you don't get the deadlock exception, but, in my case, this would result in a dublicate unique number, which is worse.

Answer 3

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
  
  

Answer 4

Since SubSonic doesn't seem to support SELECT ... FOR UPDATE and using isolation levels would be a misuse in my opinion - what about having a user defined function, which returns a new id? This function would read the current value from tbl1 with a SELECT ... FOR UPDATE, udpate the row and return the value.

In your application code where you insert a new value you would just use:

insert into tbl2 (id, ....) values (next_id(), ...)

Answer 5

Ok, I decided to use the "backdoor" and now use a inline query:

        // start transaction
        using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
        {
            using (SharedDbConnectionScope scope = new SharedDbConnectionScope(DB._provider))
            {

                try
                {

                    Record r = new InlineQuery().ExecuteAsCollection<RecordCollection>(
                        String.Format("SELECT * FROM {0} WHERE {1} = ?param FOR UPDATE",
                                        Record.Schema.TableName,
                                        Record.Columns.Column1), "VALUE")[0];

                    // do something

                    r.Save();
                 }
             }
         }

I started 10 threads simultaniously and it works as expected. Thx to rudolfson for the "SELECT FOR UPDATE" hint.