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

Ответ 1

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

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

• ВЫБРАТЬ данные из строки, содержащей один идентификатор (iD) и два поля данных (val1, val2)
• необязательно выполнять вычисления с данными
• UPDATE данные этой строки

Способ NO LOCKING:

ПРИМЕЧАНИЕ: весь код {между скобами для сгибания} предназначен для кода приложения, а не (обязательно) на стороне 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}

Способ OPTIMISTIC LOCKING:

- 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}

Этот последний пример показывает, что если вы проверили наличие коллизий в какой-то момент и обнаружите, что столкновение произошло, когда вы уже изменили другие таблицы/строки..... затем с транзакциями вы можете откатить ВСЕ изменения, сделанные с самого начала. Очевидно, что зависит от вас (знаю, что делает ваша заявка), чтобы решить, насколько велика сумма операций для отката для каждого возможного столкновения, на основании этого решается, где размещать границы транзакций и где проверять наличие столкновений со специальным Проверка UPDATE + AffectedRows.

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

Вариант ВЕРТИЦИОНАЛЬНОЙ ОПТИМИЗАЦИИ:

 - 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}

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

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

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

Как проверить на практике и получить уверенность

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

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

В какой-то момент вы увидите, что выполнение команды продолжается бесконечно. Затем, когда в другом окне он называется 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

затем измените порядок команд и порядок выполнения в любом порядке, о котором вы можете подумать.