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

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

Представим ситуацию, в которой два пользователя, работающие с одним и тем же приложением, одновременно пытаются обновить одну и ту же запись:

Как видите, первый пользователь извлекает запись из таблицы счетов и пытается снять со счёта $70, опираясь на полученные данные. В то же время второй пользователь выполняет точно такую же операцию, также опираясь на выборку, которую только что сделал из базы данных. После двух обновлений на счету устанавливается отрицательный баланс. Именно о том, как подобного избежать, рассказано в этой статье. Как предотвратить такие ситуации на уровне базы данных?

В MySQL 8 подобные ситуации устраняются при помощи двух механизмов: пессимистической или оптимистической блокировки.

Пессимистическая блокировка

При такой блокировке мы не допускаем других пользователей к каким-либо операциям над таблицами или отдельными строками до тех пор, пока конкретная транзакция не завершится. Этот механизм, в отличие от оптимистической блокировки, встроен в саму базу данных, и именно он обеспечивает нам исключительный доступ к некоторому ресурсу. Существуют блокировки двух типов: исключительные и совместные. Поскольку в вышеприведённом примере речь идёт об отдельных строках в базе данных, я сосредоточусь только на блокировании строк, а не целых таблиц. Здесь стоит отметить, что можно параллельно блокировать как целые таблицы, так и отдельные строки.

Типы блокировок

Совместная блокировка

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

Можно приобрести блокировку такого типа, добавив FOR SHARE в конце каждого запроса на выборку данных, например:

START TRANSACTION; SELECT * from accounts WHERE owner_id = 1 FOR SHARE; # что-то делаем COMMIT;

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

Исключительная блокировка

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

Чтобы установить исключительную блокировку, добавляем FOR UPDATE в конце запроса на выборку, например:

START TRANSACTION; SELECT * from accounts WHERE owner_id = 1 FOR UPDATE; # что-то делаем COMMIT;

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

Как заблокировать диапазон строк

Выше было показано, как блокировать отдельные записи. В MySQL также допускается блокировка целых диапазонов данных. Например, SELECT * FROM <аккаунты> WHERE id > 1 FOR UPDATE заблокирует все записи с id больше 1 и не позволит вставлять в базу данных новые записи.

Если попытаться заблокировать диапазон, в котором отсутствуют записи (max id = 10, а мы пытаемся заблокировать id > 100), то в рамках другой транзакции можно задать для этого диапазона другую блокировку.

Такую блокировку можно устанавливать не только по идентификатору, но и по другим критериям:

SELECT * FROM <пользователь> WHERE first_name = 'John' FOR UPDATE

Вышеприведённый запрос блокирует все строки, удовлетворяющие условию first_name = John. Теперь будет запрещено извлечь такую строку или вставить новую, если в соответствующем столбце у неё будет указанное значение.

Другие варианты блокировок

Что касается вышеописанных запросов, точнее, условий FOR SHARE и FOR UPDATE, можно добавить ещё две опции. Первая — NOWAIT. Она не позволяет запросу дожидаться, пока освободятся заблокированные сейчас строки, и, если данные не удаётся извлечь немедленно — выбрасывает ошибку. Вторая — SKIP LOCKED. С ней в рамках запроса можно извлекать только те строки, которые в настоящий момент не заблокированы другими транзакциями.

Вот, например, как могу использоваться эти опции:

SELECT * FROM user WHERE first_name = 'John' FOR UPDATE NOWAIT

SELECT * FROM user WHERE first_name = 'John' FOR UPDATE SKIP LOCKED

Взаимные блокировки

При использовании пессимистических блокировок иногда возникают ситуации, когда сразу несколько потоков пытаются извлечь и заблокировать одну и ту же строку. Если строки не будут высвобождены в течение указанного времени (по умолчанию в MySQL для этого отводится 50 секунд), то ожидающие транзакции будут выбрасывать ошибки такого вида:

[40001][1205] Lock wait timeout exceeded; try restarting transaction

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

Оптимистическая блокировка не предусмотрена в MySQL как фича. Придерживаясь такой стратегии, мы берём номер версии интересующей нас записи и проверяем, не изменился ли он с момента обновления данных. Например, допустим, что в нашей таблице аккаунтов есть дополнительный столбец — version.

Мы извлекаем запись из базы данных при помощи следующего запроса:

SELECT * FROM account WHERE id = 1;

Затем, когда хотим обновить запись, отталкиваемся от столбца с версией, например:

UPDATE account SET balance = balance - 70, version = version + 1 WHERE id = 1 AND version = 1

В условии UPDATE, точнее, в его части WHERE, используем тот номер версии, который мы извлекли при запросе.

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

Номер версии не обязательно будет целочисленным. В таком качестве вполне могут использоваться даты или контрольные суммы.

Какой вариант блокировки выбрать?

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

Пессимистическая блокировка вполне подойдёт в ситуациях, подразумевающих пакетную обработку, в особенности, когда в работу вовлечено множество потребителей. В таких случаях, когда должна обрабатываться каждая строка, критически важно не допустить, чтобы несколько потребителей одновременно выбрали одну и ту же строку. Используя условие FOR UPDATE SKIP LOCKED, можно гарантировать, что строку блокирует и обрабатывает строго один потребитель. В это время другие пропускают её и переходят к следующей доступной строке. Такой подход помогает поддерживать целостность данных и избегать конфликтов в сценариях с пакетной обработкой.

С другой стороны, оптимистическая блокировка может быть более предпочтительна в ситуациях, когда невелик риск возникновения побочных эффектов, если некая операция будет выполнена повторно. Она особенно уместна, когда приложение не вызывает никаких внешних API и не рассылает сообщений в ходе работы. В таких случаях потенциальные конфликты из-за оптимистической блокировки вряд ли повлекут серьёзные последствия. Оптимистическая блокировка допускает, чтобы доступ к данным и их изменение конкурентно осуществлялся в рамках множества транзакций сразу, и поэтому при оптимистической блокировке повышается эффективность и производительность работы.

Заключение

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