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

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

Для решения подобных проблем используются два подхода: пессимистическая и оптимистическая блокировки. Разберём их подробнее, сравним их особенности и рассмотрим, в каких случаях стоит применять каждый из них. 

Проблема: одновременное изменение баланса средств пользователя

Один из классических примеров — изменение баланса средств на банковском счету пользователя. 

В таблице account хранятся данные о балансе пользователей. Одновременные изменения одной и той же записи двумя запросами могут привести к конфликтам. Требуется способ, который обеспечит консистентность данных.

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

Пессимистическая блокировка (pessimistic locking) запрещает любые изменения данных другими запросами до завершения текущей транзакции. Это предотвращает конкуренцию за ресурсы:

BEGIN;
-- Блокируем запись для изменений другими транзакциями
SELECT balance FROM account WHERE id = 1 FOR UPDATE;

-- Выполняем операцию изменения баланса
UPDATE account SET balance = balance - 100 WHERE id = 1;

COMMIT;

Ключевое слово FOR UPDATE блокирует строку с id = 1. Пока транзакция не завершится, другие запросы не смогут прочитать или изменить эту запись.

Преимущества

• надёжность: данные защищены от одновременных изменений.
• простота: управление блокировками осуществляет база данных.

Недостатки

• Снижение производительности при высокой нагрузке из-за увеличения времени ожидания.
• Возможность взаимных блокировок (deadlocks), когда две транзакции ожидают завершения друг друга.

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

Оптимистическая блокировка (optimistic locking) исходит из предположения, что конфликты случаются редко. Она не блокирует данные, но проверяет, изменились ли они с момента чтения до записи. Это позволяет избежать лишних задержек.

Для реализации оптимистической блокировки требуется добавить в таблицу колонку version, которая будет отслеживать изменения:

ALTER TABLE account ADD COLUMN version INT DEFAULT 0;

Обновление выполняется следующим образом:

BEGIN;

-- Читаем текущий баланс и версию записи
SELECT balance, version FROM account WHERE id = 1;

-- Пытаемся обновить запись, проверяя, не изменилась ли версия
UPDATE account
SET balance = :balance - 100, version = version + 1
WHERE id = 1 AND version = :version;

-- Проверяем, была ли обновлена строка
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    THROW 'Conflict detected';
ELSE
    COMMIT;
END IF;

Если версия записи изменилась, обновление не выполняется, предотвращая перезапись данных.

Преимущества

• Высокая производительность при редких конфликтах.
• Отсутствие deadlock'ов.

Недостатки

• Требуется добавление и управление колонкой версии.
• Необходима обработка конфликтов в приложении.

Сравнение подходов

Таким образом, выбор между описанными стратегиями зависит от специфики задачи, уровня нагрузки и требований к консистентности данных.

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

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