Гарантии консистентности: как правильно обновлять записи в SQL под конкуренцией

Когда в системе появляется конкурентный доступ — несколько пользователей, фоновые джобы, очереди событий, несколько экземпляров приложения — «простые» UPDATE внезапно перестают быть простыми. Один поток обновляет запись, другой параллельно читает и принимает решение, третий делает похожее обновление на основе устаревших данных. В итоге возникают эффекты, которые выглядят как “вроде всё работает”, но иногда приводят к потере данных, дублированию, нарушению ограничений логики и невозможности воспроизвести проблему.

Цель этой статьи — разобрать, какие гарантии консистентности вообще даёт SQL/СУБД, как они зависят от транзакций и уровней изоляции, и как правильно выбирать стратегию блокировок и контроля конкуренции при обновлениях. Параллельно разберём типовые ошибки, из‑за которых баг проявляется не всегда.

Что именно “ломается” при конкурентных обновлениях

Начнём с моделей проблем. Допустим, есть таблица accounts(id, balance) и операция: «снять сумму со счёта». Логика обычно выглядит как чтение текущего баланса → вычисление нового → обновление записи.

Потеря обновлений (lost update)

Два транзакционных потока читают один и тот же баланс и оба записывают новое значение. Последний UPDATE перезатирает результат первого.

Неповторяемое чтение (non-repeatable read)

Транзакция читает строку два раза и видит разные значения из‑за коммитов параллельных транзакций.

Фантомы (phantoms)

Транзакция выполняет запрос с условием (например, по диапазону дат) и дважды видит разное множество строк — появились/исчезли строки, удовлетворяющие условию.

Дублирование по “проверка → вставка”

Классика: сначала проверили, что записи нет, затем вставили. Между проверкой и вставкой другая транзакция успела вставить, и в результате появляется дубль. Даже если “иногда” — это всё равно баг.

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

Транзакции как базовая гарантия: атомарность и порядок

Транзакция в SQL — это единица работы, которая либо целиком коммитится, либо откатывается. Для консистентности важно не только “атомарность”, но и то, какие данные транзакция видит и какие блокировки/конфликты возникают при конкурентном доступе.

Минимальная структура транзакции

В зависимости от СУБД синтаксис чуть отличается, но концептуально это так:

BEGIN;

-- операции чтения/изменения

COMMIT; -- или ROLLBACK при ошибке

Для гарантий важно:

• не выходить из транзакции между “прочитать → решить → обновить”;
• понимать, что уровень изоляции влияет на то, какие значения будут “прочитаны” и что может “вклиниться” параллельно.

Уровни изоляции: почему READ COMMITTED — не “безопасно”

SQL-стандарт описывает уровни изоляции. Большинство популярных СУБД поддерживают близкие уровни, но с нюансами реализации. В практическом смысле важны четыре точки:

1. какой снимок видит транзакция (snapshot),
2. может ли меняться то, что вы читаете,
3. какие аномалии допускаются,
4. какие блокировки использует СУБД (или полагается на MVCC).

Два подхода: блокировки и MVCC

СУБД обычно выбирает один из режимов:

• блокировками (чаще при SERIALIZABLE в некоторых реализациях),
• MVCC (Multi-Version Concurrency Control — версионность строк), где чтения не блокируют записи, а транзакции “видят” согласованные версии.

Классические уровни (в терминах аномалий)

• READ UNCOMMITTED — позволяет грязные чтения (rare на практике).
• READ COMMITTED — запрещает грязные чтения. Но допускает неповторяемое чтение и фантомы.
• REPEATABLE READ — обычно гарантирует неповторяемое чтение. Фантомы могут оставаться возможными (зависит от СУБД).
• SERIALIZABLE — стремится обеспечить эффект последовательного исполнения транзакций (на практике через дополнительные механизмы: блокировки диапазонов, проверки сериализуемости или “строительство” схемы конфликтов).

Практический смысл

Если вы делаете обновление на основе ранее прочитанного значения, то READ COMMITTED часто не защищает от сценария “прочитал старое → обновил на основе старого”. В MVCC это решается не только изоляцией, но и стратегиями конкурентного контроля (ниже).

Стратегии конкуренции при обновлениях: optimistic vs pessimistic

Есть два фундаментальных подхода к конкуренции:

Оптимистичная блокировка (optimistic locking)

Предполагаем, что конфликт редок. Вместо “заблокировать заранее” мы:

• читаем текущие данные вместе с версией/updated_at,
• при обновлении проверяем, что версия не изменилась.

Если версия изменилась — отменяем операцию и повторяем (или возвращаем ошибку пользователю/вызывающему коду).

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

Пессимистичная блокировка (pessimistic locking)

Предполагаем, что конфликт вероятен. Тогда:

• блокируем строки (или диапазоны) так, чтобы конкурирующие транзакции ждали,
• и выполняем обновление в безопасном контуре.

Плюсы: предсказуемое поведение, меньше ретраев.
Минусы: риск дедлоков, падение производительности, рост времени ожидания.

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

Чаще всего оптимистичная блокировка реализуется через поле version (целое) или updated_at (временная метка) — в последнем случае нужно аккуратно, чтобы сравнение было детерминированным.

Схема с version

Пусть таблица:

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY,
  balance NUMERIC(20,2) NOT NULL,
  version INT NOT NULL DEFAULT 1
);

Вы читаете запись, фиксируете version, затем обновляете:

-- Транзакция A: снять сумму
BEGIN;

SELECT balance, version
FROM accounts
WHERE id = 42;

-- допустим, бизнес вычислил новое balance
-- new_balance = 100.00, current_version = 7

UPDATE accounts
SET balance = 100.00,
    version = version + 1
WHERE id = 42
  AND version = 7;

-- если UPDATE затронул 0 строк — значит version изменилась кем-то ещё
-- что делать: повторить или вернуть ошибку

COMMIT;

Проверка результата UPDATE обязательна. В языках/ORM обычно это выглядит как “rows affected”. Если 0 — конфликт. Тогда лучше:

• перечитать актуальное состояние,
• пересчитать новое,
• снова попробовать обновление, пока не упрёмся в лимит ретраев.

Типичная ошибка с optimistic locking

Считать version и написать UPDATE ... WHERE id = ? без AND version = ?. Формально всё выполнится успешно, но вы потеряете ключевую гарантию — “сверку” против изменения данных.

Когда optimistic locking особенно уместен

• транзакции короткие,
• конфликт редко,
• есть возможность ретраить,
• обновления не требуют сложной координации с несколькими строками (или вы готовы к логике ретраев на уровне всего набора).

Пессимистичная блокировка: SELECT ... FOR UPDATE

Если нужен жёсткий контроль, часто используют блокировку строки в транзакции.

Вариант на примере PostgreSQL

BEGIN;

SELECT balance
FROM accounts
WHERE id = 42
FOR UPDATE;

-- здесь вы гарантированно держите блокировку строки до COMMIT/ROLLBACK
-- другие транзакции будут ждать

UPDATE accounts
SET balance = balance - 50.00
WHERE id = 42;

COMMIT;

Смысл FOR UPDATE:

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

Nuance: не только строка, но и “набор строк”

Если запрос выбирает несколько строк, блокируются все строки из результата. Это критично, если условие широкое. Например:

SELECT *
FROM accounts
WHERE user_id = 10
FOR UPDATE;

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

Дедлоки

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

• всегда захватывайте строки в одинаковом порядке (например, сортируйте по id),
• минимизируйте время между захватом блокировок и коммитом,
• держите транзакции короткими.

“Я сделал транзакцию — и всё безопасно”: почему это не всегда так

Одна из самых частых иллюзий: “Я завернул в BEGIN/COMMIT, значит всё консистентно”. Но консистентность в реальности зависит от трёх слоёв:

1. уровень изоляции,
2. механизм конкурентного контроля (оптимистичный/пессимистичный, уникальные ограничения и т.п.),
3. действительно ли транзакция охватывает весь критический участок.

Ошибка №1: транзакция охватывает не весь сценарий

Например, вы делаете так:

• запрос A (в транзакции) — прочитать,
• коммит,
• затем отдельным запросом обновить.

Даже если в коде “есть транзакции”, критический участок между чтением и обновлением уже не защищён.

Ошибка №2: слишком мягкий уровень изоляции

На READ COMMITTED некоторые аномалии допустимы. Допустим, логика:

• прочитать баланс,
• проверить условие (например, что достаточно денег),
• обновить.

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

Решение: либо pессимистичная блокировка строки, либо optimistic locking с проверкой версии, либо повышение изоляции/серилизация (но это может быть дорогим).

Ошибка №3: полагаться на “SELECT вернул то, что нужно”

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

• посчитать текущее число активных заказов,
• если < лимита — увеличить счётчик.

При конкурентных запросах лимит можно превысить.

Комбинирование стратегий: “версионность + блокировка” и что не стоит делать

На практике иногда смешивают подходы:

• оптимистичная проверка версии,
• и дополнительные блокировки для “узких мест”.

Но смешивание требует аккуратности: чтобы не получить лишние дедлоки или бесконечные ретраи.

Пример нежелательной логики

• в одной ветке используется FOR UPDATE,
• в другой — optimistic retry без блокировок,
• но порядок захвата ресурсов и время выполнения разные.

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

Стабильнее:

• выбрать одну стратегию на уровень бизнес-операции (в идеале),
• либо явно унифицировать порядок ресурсов и правила ретраев.

Уникальные ограничения и “upsert”: лучший механизм против некоторых гонок

Для задач “создать, если не существует” или “обновить с инкрементом, избегая гонок” часто вместо ручной логики “select then insert/update” используют уникальные индексы и атомарные конструкции.

INSERT ... ON CONFLICT (PostgreSQL)

Допустим, таблица user_tokens(user_id, token_value) с уникальностью user_id:

CREATE TABLE user_tokens (
  user_id BIGINT PRIMARY KEY,
  token_value TEXT NOT NULL
);

Тогда атомарно:

INSERT INTO user_tokens(user_id, token_value)
VALUES (42, 'abc')
ON CONFLICT (user_id)
DO UPDATE SET token_value = EXCLUDED.token_value;

Это снижает вероятность дублей, потому что гонка “проверил/вставил” исчезает: конфликт обрабатывается на уровне индекса.

Типичная ошибка

Оставлять бизнес-логику “проверка → вставка” без уникального ограничения. Даже при правильных транзакциях без уникального индекса вы всё равно можете получить дубль при конкуренции.

Как выбрать уровень изоляции и стратегию блокировок: практический подход

Нет универсального ответа “всегда serializable” или “всегда optimistic”. Но есть рабочий метод выбора.

1) Определите инварианты

Что вы обязаны сохранить?

• сумма денег не должна стать отрицательной?
• номер заказа уникален?
• лимит по квоте не должен превышаться?

Инварианты важнее, чем “хочу максимальную изоляцию”.

2) Оцените характер конкуренции

• Конфликты редки? Оптимистичная блокировка или upsert с уникальными ограничениями.
• Конфликты часты и критично избежать повторов? Пессимистичная блокировка.

3) Обратите внимание на длительность транзакции

Чем дольше транзакция удерживает блокировки, тем сильнее деградация при конкуренции.

• Транзакции должны быть короткими.
• Не делайте внутри FOR UPDATE сетевые вызовы, тяжёлые вычисления и ожидания.

4) Подумайте о ретраях и идемпотентности

При optimistic locking ретраи неизбежны. Значит:

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

Тестирование проблем консистентности: как поймать баг “когда-нибудь”

Баги под конкуренцией редко воспроизводятся в одиночном тесте. Чтобы уменьшить вероятность “промахов”, используйте:

Стресс-тесты и параллельные сценарии

Запускайте N параллельных транзакций, которые:

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

Инструменты СУБД

• логирование блокировок/дедлоков,
• мониторинг ожиданий,
• наблюдение за количеством конфликтов при optimistic locking.

Инвариантные проверки

В конце теста валидируйте:

• что баланс не стал некорректным,
• что счетчики/лимиты не нарушены,
• что отсутствуют дубли по уникальным ключам.

Если инварианты не заложены в тесты, вы можете получить “вроде работает” и пропустить гонку.

Типовые анти-паттерны, которые встречаются чаще всего

1. Select → Update без проверки версии

   • “всё завернули в транзакцию, значит безопасно”.
   • На деле транзакция не гарантирует, что вы обновляете на актуальном снимке, если не обеспечен контроль конфликтов.

2. Сравнение updated_at без чёткого смысла

   • Например, сравнивают время с округлением или не учитывают различия часовых поясов/точности.
   • Лучше версия (число) или системно управляемый xmin/идентификатор версии (зависит от СУБД).

3. Широкие FOR UPDATE

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

4. Дедлоки из-за разного порядка захвата

   • Два кода захватывают строки в разном порядке.
   • Под нагрузкой дедлоки становятся нормой.

5. Отсутствие уникальных индексов

   • Логика “проверили, что нет” не заменяет ограничения целостности на уровне базы.

Практический пример “правильного” обновления: баланс с optimistic locking

Сведём всё в одну картину. Допустим, требуется атомарно снять деньги, не уходя в отрицательный баланс, и система допускает ретраи.

Таблица

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY,
  balance NUMERIC(20,2) NOT NULL,
  version INT NOT NULL DEFAULT 1
);

Процедура обновления

(Схема в SQL-стиле; в реальном приложении обычно это обёртка в коде.)

-- Предположим, что приложение передаёт amount и делает цикл ретраев
BEGIN;

-- 1) прочитать текущее значение и версию
SELECT balance, version
FROM accounts
WHERE id = 42;

-- 2) вычислить новое значение (например, new_balance = balance - 50)
-- 3) обновить только если версия не изменилась и баланс не уйдёт в минус
UPDATE accounts
SET balance = balance - 50.00,
    version = version + 1
WHERE id =