Использование TransactionScope с MySQL и блокировкой чтения

Если кому-то интересно, вот как TransactionOptions влияет на MySql:

Допустим, у меня есть два метода.

Method1 запускает транзакцию, выбирает строку из моей таблицы, увеличивает значение и обновляет таблицу.

Метод 2 такой же, но между выбором и обновлением я добавил сон на 1000 мс.

А теперь представьте, что у меня есть следующий код:

    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

Без транзакций это могло бы произойти:
thread2 запускается, считывает значение 5, затем засыпает,
thread1 запускается, считывает значение 5, обновляет значение до 6,
thread2 также обновляет значение до 6.

Эффект: У меня два раза уникальный номер.

Что я хочу:
thread2 запускается, считывает значение 5, затем засыпает,
thread1 запускается, пытается прочитать значение, но получает блокировку и засыпает,
thread2 обновляет значение до 6,
thread1 продолжает, читает значение 6, обновляет значение до 7

Вот как начать транзакцию с помощью 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();
        }

Это может даже управлять распределенными транзакциями. Если генерируется исключение, ts.Complete никогда не вызывается, а часть области действия Dispose () откатывает транзакцию.

Вот обзор того, как разные IsolationLevels влияют на транзакцию:

• IsolationLevel.Chaos
  Выдает исключение NotSupportedException - уровень изоляции Chaos не поддерживается
  
  
• IsolationLevel.ReadCommited
  Транзакции не мешают друг другу (два идентичных чтения, неверно)
  
  
• IsolationLevel.ReadUncommitted
  Транзакции не мешают друг другу (два идентичных чтения, неверно)
  
  
• IsolationLevel.RepeatableRead
  Транзакции не мешают друг другу (два идентичных чтения, неверно)
  
  
• IsolationLevel.Serializable
  Выдает исключение MySqlException - обнаружена тупиковая ситуация при попытке получить блокировку; попробуйте перезапустить транзакцию во время обновления
  
  
• IsolationLevel.Snapshot
  Выдает исключение MySqlException - у вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, на предмет правильного синтаксиса для использования рядом с "" в строке 1 во время Connection.Open ()
  
  
• IsolationLevel.Unspecified
  Выдает исключение MySqlException - обнаружена тупиковая ситуация при попытке получить блокировку; попробуйте перезапустить транзакцию во время обновления
  
  
• TransactionOptions not set
  Выдает исключение MySqlException - обнаружена тупиковая ситуация при попытке получить блокировку; попробуйте перезапустить транзакцию во время обновления
  
  

Сначала я решил использовать SELECT FOR UPDATE, и после быстрого поиска я нашел страницу о блокировка чтения в справочнике MySQL 5.

Если я правильно понимаю, это не зависит от используемого уровня изоляции. И будьте осторожны - уровень изоляции просто говорит о том, как на текущую транзакцию влияют изменения в других транзакциях. Он не сообщает, что могут делать другие транзакции. Однако для более высоких уровней изоляции требуются более ограничивающие блокировки.

Поскольку я не нашел способа заблокировать строку для чтения с помощью InnoDB и TransactionScope (я могу ошибаться), это должно сработать:

Если я запускаю две транзакции одновременно (без TransactionOptions) и одна завершается, другая не может быть завершена из-за исключения «взаимоблокировки».

Вместо того, чтобы избегать этого исключения, это кажется лучшей практикой, согласно MySQL документации, чтобы ожидать тупиковой ситуации и перезапустить транзакцию.

если вы установите:

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

для вашей транзакции вы не получите исключение взаимоблокировки, но в моем случае это приведет к дублированию уникального номера, что еще хуже.

Поскольку SubSonic, похоже, не поддерживает SELECT ... FOR UPDATE, и, на мой взгляд, использование уровней изоляции было бы неправильным - как насчет наличия определяемая пользователем функция, которая возвращает новый идентификатор? Эта функция считывает текущее значение из tbl1 с SELECT ... FOR UPDATE, обновляет строку и возвращает значение.

В коде вашего приложения, где вы вставляете новое значение, которое вы бы просто использовали:

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

Хорошо, я решил использовать «бэкдор» и теперь использую встроенный запрос:

        // 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();
                 }
             }
         }

Я запустил 10 потоков одновременно, и все работает как положено. Спасибо rudolfson за подсказку «ВЫБРАТЬ ДЛЯ ОБНОВЛЕНИЯ».