Оптимистическая блокировка в MySQL

Question

Я не могу найти какие-либо подробности о оптимистической блокировке в MySQL. Однако я прочитал, что при запуске транзакции синхронизируются обновления двух объектов - это не мешает двум пользователям обновлять данные одновременно, вызывая конфликт.

Видимо оптимистичная блокировка решит эту проблему? Как это применяется в MySQL. Есть ли синтаксис SQL / ключевое слово для этого? Или MySQL имеет поведение по умолчанию?

Спасибо, парни.

Answer 1

Дело в том, что Оптимистическая блокировка не является функцией базы данных, ни для MySQL, ни для других: оптимистическая блокировка - это практика, которая применяется с использованием БД со стандартными инструкциями.

Давайте рассмотрим очень простой пример и скажем, что вы хотите сделать это в коде, который одновременно могут запускать несколько пользователей / клиентов:

1. ВЫБЕРИТЕ данные из строки, имеющей один зарегистрированный идентификатор (iD) и два поля данных (val1, val2)
2. по желанию сделать ваши расчеты с данными
3. ОБНОВЛЕНИЕ данных этой строки

НЕТ БЛОКИРОВКИ способ это:

ПРИМЕЧАНИЕ: весь код {между скручиваемыми скобками} должен быть в коде приложения, а не (обязательно) на стороне SQL

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId;
 - {go on with your other code}

Оптимальный способ блокировки:

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

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

Что насчет СДЕЛОК тогда?

 - SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - BEGIN TRANSACTION;
 - UPDATE anotherTable
       SET col1 = @newCol1,
           col2 = @newCol2
       WHERE iD = @theId;
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     COMMIT TRANSACTION;
 -     {go on with your other code}
 - {else}
 -     ROLLBACK TRANSACTION;
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

Этот последний пример показывает, что если вы проверяете наличие коллизий в какой-то момент и обнаруживаете, что коллизия произошла, когда вы уже изменили другие таблицы / строки.. ... затем с помощью транзакций вы можете откатить ВСЕ изменения, которые вы сделали с тех пор начало. Очевидно, что именно вы (те, кто знает, что делает ваше приложение) должны решить, насколько велик объем операций отката для каждого возможного столкновения, и на основании этого решить, где поставить границы транзакций и где проверить наличие конфликтов со специальными ОБНОВЛЕНИЕ + проверка ActedRows.

В этом случае с транзакциями мы отделили момент, когда мы выполняем ОБНОВЛЕНИЕ с момента, когда оно совершено. Так что же происходит, когда "другой процесс" выполняет обновление в этот период времени? Чтобы точно знать, что происходит, необходимо углубиться в детали уровня изоляции (и как управлять каждым двигателем). В качестве примера в случае Micosoft SQL Server с READ_COMMITTED обновленные строки блокируются до тех пор, пока COMMIT не сможет "другой процесс" ничего не делать (находится в режиме ожидания) с этими строками, а также с SELECT (фактически он может только READ_COMMITTED), Таким образом, поскольку действие "другой процесс" отложено, его ОБНОВЛЕНИЕ завершится неудачей.

Опция VERSIONING OPTIMISTIC LOCKING:

 - SELECT iD, val1, val2, version
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2,
           version = version + 1
       WHERE iD = @theId
           AND version = @oldversion;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

Здесь показано, что вместо того, чтобы проверять, является ли значение все тем же для всех полей, мы можем использовать выделенное поле (которое изменяется каждый раз, когда мы делаем ОБНОВЛЕНИЕ), чтобы увидеть, был ли кто-то быстрее нас и изменил строку между нашими ВЫБРАТЬ и ОБНОВИТЬ. Здесь отсутствие транзакций связано с простотой, как в первом примере, и не связано с использованием столбца версии. Опять же, использование этого столбца зависит от реализации в коде приложения, а не от функции ядра базы данных.

Более того, есть и другие моменты, которые, я думаю, сделали бы этот ответ слишком длинным (уже слишком длинным), поэтому я упомяну их только с некоторыми ссылками:

• уровень изоляции транзакции ( здесь для MySQL) о влиянии транзакции на SELECT.
• для INSERT для таблиц с первичными ключами, которые не были сгенерированы автоматически (или с уникальными ограничениями), автоматически произойдет сбой без необходимости конкретной проверки, если два процесса попытаются вставить одинаковые значения там, где они должны быть уникальными.
• если у вас нет столбца идентификатора (первичный ключ или уникальные ограничения), то также для одного SELECT + UPDATE требуются транзакции, потому что вы можете получить неожиданность, потому что после внесенных другими изменениями будет больше строк, чем ожидалось, соответствующих критериям предложения WHERE в UPDATE.

Как проверить на практике и стать уверенным

Поскольку значение уровня изоляции и реализация могут отличаться, лучшим советом (как обычно на этом сайте) является выполнение теста на используемой платформе / среде.

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

В какой-то момент вы увидите, что выполнение команды продолжается до бесконечности. Затем, когда в другом окне он называется COMMIT или ROLLBACK, он завершает выполнение.

Вот несколько очень простых команд, готовых к тестированию, как только что описано.

Используйте их для создания таблицы и одной полезной строки:

CREATE TABLE theTable(
    iD int NOT NULL,
    val1 int NOT NULL,
    val2 int NOT NULL
)
INSERT INTO theTable (iD, val1, val2) VALUES (1, 2 ,3);

Затем следующее на двух разных окнах и пошагово:

BEGIN TRAN

SELECT val1, val2 FROM theTable WHERE iD = 1;

UPDATE theTable
  SET val1=11
  WHERE iD = 1 AND val1 = 2 AND val2 = 3;

COMMIT TRAN

затем измените порядок команд и порядок их выполнения в любом порядке.