Оптимистичная и пессимистичная блокировка: version, FOR UPDATE и SKIP LOCKED

Как победить lost update в базе данных: оптимистичная блокировка через version-колонку, SELECT ... FOR UPDATE, NOWAIT и SKIP LOCKED, дедлоки, выбор подхода и вопросы на собеседовании.

17 мин чтениябазы данных · транзакции · sql · системный дизайн

Коротко

ОптимистичнаяПессимистичная
ИдеяКонфликтов мало — проверим при записиКонфликты вероятны — заблокируем заранее
МеханизмUPDATE ... WHERE version = ?SELECT ... FOR UPDATE
Что держитНичегоСтроку до COMMIT/ROLLBACK
При конфликте0 обновлённых строк → перечитать и повторитьВторая транзакция ждёт
РискиШторм повторов при частых конфликтахОжидания, дедлоки, долгие транзакции
Где уместнаФормы, API, длинные «думающие» операцииДеньги, остатки, короткие горячие транзакции
  • Lost update — две транзакции читают одно значение, обе считают новое в коде и пишут. Одна запись молча затирает другую. Read Committed от этого не защищает.
  • Оптимистичная блокировка — это compare-and-swap на уровне строки: обновляем, только если версия не изменилась с момента чтения.
  • Пессимистичная блокировка — SELECT ... FOR UPDATE в транзакции: конкуренты встают в очередь на строке.
  • NOWAIT и SKIP LOCKED меняют поведение при занятой строке: сразу ошибка или пропустить строку. Второе — готовая очередь задач на таблице.
  • Часто не нужна ни та, ни другая: атомарный UPDATE balance = balance - 30 WHERE balance >= 30 решает задачу одним запросом.

Проблема: lost update

Классический сценарий — списание со счёта, написанное «в лоб»:

balance = db.query('SELECT balance FROM accounts WHERE id = 1') db.execute('UPDATE accounts SET balance = %s WHERE id = 1', (balance - 30,))

Два запроса приходят одновременно:

T1: SELECT balance → 100
T2: SELECT balance → 100
T1: UPDATE balance = 70
T2: UPDATE balance = 70   -- списание T1 потеряно

Списали дважды по 30, а на счёте 70 вместо 40. Ни ошибки, ни предупреждения в логах. На уровне Read Committed (по умолчанию в PostgreSQL) база видит две корректные транзакции: каждая прочитала зафиксированные данные и записала своё значение. Проблема в том, что решение принято в коде приложения на основе устаревшего значения.

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

Все примеры ниже прогнаны на PostgreSQL 16 и Python с драйвером psycopg 3. Общая заготовка:

import threading import psycopg DSN = 'dbname=shop' def reset(): with psycopg.connect(DSN, autocommit=True) as conn: conn.execute('DROP TABLE IF EXISTS accounts') conn.execute( 'CREATE TABLE accounts (id int PRIMARY KEY, balance int, version int NOT NULL DEFAULT 0)' ) conn.execute('INSERT INTO accounts (id, balance) VALUES (1, 100)') def balance(): with psycopg.connect(DSN) as conn: return conn.execute('SELECT balance FROM accounts WHERE id = 1').fetchone()[0] def run_parallel(fn, n=2): barrier = threading.Barrier(n) threads = [threading.Thread(target=fn, args=(barrier,)) for _ in range(n)] for t in threads: t.start() for t in threads: t.join()

Barrier гарантирует, что оба потока дойдут до опасной точки одновременно — без него гонка воспроизводится через раз. Воспроизводим потерю:

def naive(barrier): with psycopg.connect(DSN) as conn: (b,) = conn.execute('SELECT balance FROM accounts WHERE id = 1').fetchone() barrier.wait() # оба прочитали 100 conn.execute('UPDATE accounts SET balance = %s WHERE id = 1', (b - 30,)) reset() run_parallel(naive) print(balance()) # 70, а должно быть 40

Сначала: может, блокировка не нужна

Если новое значение вычисляется из старого простой формулой, перенесите вычисление в базу:

UPDATE accounts SET balance = balance - 30 WHERE id = 1 AND balance >= 30;

UPDATE сам берёт блокировку строки, а второй конкурент в Read Committed дождётся первого и пересчитает balance - 30 уже от свежего значения. Условие balance >= 30 заменяет проверку «хватает ли денег», а число обновлённых строк (0 или 1) говорит, прошло ли списание.

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

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

Идея: «я собираюсь менять эту строку, никому не трогать до конца моей транзакции».

def pessimistic(barrier): with psycopg.connect(DSN) as conn: # транзакция до выхода из with barrier.wait() (b,) = conn.execute( 'SELECT balance FROM accounts WHERE id = 1 FOR UPDATE' ).fetchone() conn.execute('UPDATE accounts SET balance = %s WHERE id = 1', (b - 30,)) reset() run_parallel(pessimistic) print(balance()) # 40

Второй SELECT ... FOR UPDATE блокируется, пока первая транзакция не сделает COMMIT, а затем возвращает уже обновлённое значение 70. Обычные SELECT без FOR UPDATE при этом не ждут: благодаря MVCC читатели видят последнюю зафиксированную версию и не мешают писателям.

Что важно помнить

  • Блокировка живёт до конца транзакции. В режиме autocommit SELECT ... FOR UPDATE бесполезен: блокировка снимется сразу после запроса. Это частая ошибка, когда ORM или драйвер по умолчанию работает в autocommit.
  • Держите транзакцию короткой. Никаких HTTP-запросов к платёжному шлюзу, отправки писем и ожидания пользователя внутри. Каждая лишняя секунда — секунда очереди для всех, кто пришёл за этой строкой.
  • Блокируются только найденные строки. Если строки ещё нет (проверка «существует ли заказ, и если нет — создать»), FOR UPDATE ничего не заблокирует. Здесь нужен уникальный индекс и INSERT ... ON CONFLICT.
  • В MySQL InnoDB важны индексы. Блокирующее чтение по неиндексированному условию сканирует таблицу и на Repeatable Read ставит блокировки на просмотренные записи и промежутки между ними. В итоге блокируется гораздо больше, чем вы хотели.

Режимы блокировки строк в PostgreSQL

РежимКогда использовать
FOR UPDATEБудем удалять строку или менять её ключ
FOR NO KEY UPDATEБудем менять обычные поля (это же берёт простой UPDATE)
FOR SHAREЧитаем и не хотим, чтобы строку меняли, но другим читать так же можно
FOR KEY SHAREНужен только неизменный ключ — так проверяются внешние ключи

Практическое следствие: FOR UPDATE конфликтует с FOR KEY SHARE, поэтому пока вы держите FOR UPDATE на счёте, вставка заказа с внешним ключом на этот счёт будет ждать. С FOR NO KEY UPDATE вставка проходит. Проверено:

FOR UPDATE        + INSERT INTO orders (account_id) VALUES (1) → ждёт (lock timeout)
FOR NO KEY UPDATE + INSERT INTO orders (account_id) VALUES (1) → ok

Если ключ строки вы менять не собираетесь, FOR NO KEY UPDATE уменьшит лишние ожидания на горячих родительских таблицах.

NOWAIT и lock_timeout: не ждать вечно

По умолчанию ожидание блокировки ничем не ограничено. Два способа это изменить:

-- Сразу ошибка, если строка занята SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- ERROR: could not obtain lock on row in relation "accounts" (SQLSTATE 55P03) -- Ждать не дольше 200 мс в рамках текущей транзакции SET LOCAL lock_timeout = '200ms'; SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)

NOWAIT хорош для интерфейсов вида «этот документ сейчас редактирует другой пользователь». lock_timeout — страховка от того, что один зависший процесс соберёт за собой очередь из сотен соединений и исчерпает пул. В MySQL 8.0 аналоги — тот же NOWAIT и innodb_lock_wait_timeout (по умолчанию 50 секунд).

Дедлоки и порядок блокировок

Перевод денег между двумя счетами блокирует две строки. Если T1 переводит с 1 на 2, а T2 с 2 на 1, получается классический дедлок:

T1: UPDATE accounts ... WHERE id = 1   -- держит 1
T2: UPDATE accounts ... WHERE id = 2   -- держит 2
T1: UPDATE accounts ... WHERE id = 2   -- ждёт T2
T2: UPDATE accounts ... WHERE id = 1   -- ждёт T1 → цикл

PostgreSQL обнаруживает цикл (проверка запускается после deadlock_timeout, по умолчанию 1 секунда) и откатывает одну из транзакций с ошибкой 40P01 deadlock detected. Данные не портятся, но запрос пользователя падает.

Лечение — брать блокировки всегда в одном порядке, например по возрастанию id:

def transfer(conn, src, dst, amount): with conn.transaction(): # Блокируем обе строки в одном порядке — по возрастанию id rows = conn.execute( 'SELECT id, balance FROM accounts WHERE id = ANY(%s) ORDER BY id FOR UPDATE', ([src, dst],), ).fetchall() balances = dict(rows) if balances[src] < amount: raise ValueError('недостаточно средств') conn.execute('UPDATE accounts SET balance = balance - %s WHERE id = %s', (amount, src)) conn.execute('UPDATE accounts SET balance = balance + %s WHERE id = %s', (amount, dst))

Четыре потока по 200 встречных переводов между счетами 1 и 2 отработали без единого дедлока, итоговые балансы сошлись. Даже с упорядочиванием дедлоки в сложной системе возможны, поэтому ошибку 40P01 стоит ловить и повторять транзакцию целиком.

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

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

UPDATE accounts SET balance = 70, version = version + 1 WHERE id = 1 AND version = 0;

Если с момента чтения кто-то успел обновить строку, версия уже 1, условие не выполнится и запрос обновит 0 строк. Это сигнал конфликта: перечитываем данные и пробуем снова.

def optimistic(barrier, retries=5): with psycopg.connect(DSN, autocommit=True) as conn: for attempt in range(retries): b, v = conn.execute( 'SELECT balance, version FROM accounts WHERE id = 1' ).fetchone() if attempt == 0: barrier.wait() # оба прочитали версию 0 cur = conn.execute( 'UPDATE accounts SET balance = %s, version = version + 1 ' 'WHERE id = 1 AND version = %s', (b - 30, v), ) if cur.rowcount == 1: return print(f' конфликт на версии {v}, повторяем') raise RuntimeError('не удалось обновить') reset() run_parallel(optimistic) print(balance())

Вывод:

  конфликт на версии 0, повторяем
40

Один поток выиграл с первой попытки, второй обнаружил конфликт, перечитал баланс 70 и записал 40.

Почему это работает даже на Read Committed

Кажется, что между проверкой version = 0 и записью есть зазор. Его нет: UPDATE берёт блокировку строки. Если две транзакции одновременно выполняют UPDATE ... WHERE version = 3, вторая ждёт первую, а после её COMMIT PostgreSQL перепроверяет условие на новой версии строки. Там version = 4, поэтому второй UPDATE обновляет 0 строк. Это проверено: вторая транзакция висела, пока первая не зафиксировалась, и вернула rowcount = 0.

Иначе говоря, оптимистичная блокировка не совсем без блокировок: короткая блокировка на время самого UPDATE всё равно есть. Не держится только долгая блокировка между чтением и записью.

Варианты версии

  • Целочисленный счётчик — самый надёжный вариант. Монотонный, сравнение точное.
  • updated_at — работает, пока точность времени выше частоты обновлений и все серверы пишут время одинаково. Две записи в одну миллисекунду или округление в драйвере — и конфликт пропущен. Лучше не надо.
  • Хеш содержимого — подходит, когда схему менять нельзя, но сравнивать неудобно и дорого.
  • Системный xmin в PostgreSQL — идентификатор транзакции, создавшей текущую версию строки. Его используют как токен конкурентности, например, в EF Core с провайдером Npgsql. Удобно, но это внутренняя деталь, и она меняется при любом обновлении строки, даже если ваши поля не трогали.

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

Большинство ORM умеют это из коробки:

  • JPA/Hibernate — поле с аннотацией @Version; при конфликте бросается OptimisticLockException.
  • Rails ActiveRecord — колонка lock_version, исключение ActiveRecord::StaleObjectError.
  • Django — встроенного механизма нет, пишут вручную: Account.objects.filter(id=1, version=v).update(balance=..., version=F('version') + 1) возвращает число обновлённых строк.

В HTTP тот же приём называется conditional request: сервер отдаёт версию ресурса в заголовке ETag, клиент присылает её в If-Match при PUT, и сервер отвечает 412 Precondition Failed, если ресурс изменился. Так работают многие REST API для совместного редактирования.

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

Что делать при конфликте

Повтор — не единственный вариант, и не всегда правильный:

  • Автоматический повтор — когда операцию можно безопасно пересчитать от новых данных: списание, инкремент, изменение статуса по правилам. Ограничьте число попыток и добавьте небольшую случайную задержку, иначе при высокой конкуренции потоки будут сталкиваться снова и снова.
  • Вернуть ошибку пользователю — когда решение принимал человек на основе того, что видел: «Документ изменён другим пользователем, обновите страницу». Молча перезаписать чужие правки — то же самое lost update.
  • Слияние — когда изменения затрагивают разные поля и их можно объединить. Сложнее, но так работают, например, вики и редакторы с историей правок.

SKIP LOCKED: очередь задач на таблице

SKIP LOCKED говорит: «если строка занята, не жди и не падай, просто пропусти её». Обычный SELECT для отчёта так писать нельзя — он молча не увидит часть данных. Зато для очереди задач это ровно то, что нужно: каждый воркер берёт свободные задачи и не мешает остальным.

CLAIM = """ UPDATE jobs SET status = 'done', worker = %s WHERE id IN ( SELECT id FROM jobs WHERE status = 'new' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED ) RETURNING id """ def worker(name): with psycopg.connect(DSN) as conn: while True: with conn.transaction(): ids = [row[0] for row in conn.execute(CLAIM, (name,))] if not ids: return

Четыре воркера разобрали 200 задач пачками по 10: распределение вышло 50/60/40/50, ни одна задача не досталась двоим, ни одна не осталась. Без SKIP LOCKED воркеры выстраивались бы в очередь за одними и теми же первыми строками.

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

  1. Держать транзакцию на время обработки. Если воркер упал, транзакция откатится, и задача снова станет доступна. Просто, но долгие задачи держат соединение и открытую транзакцию, а это мешает очистке старых версий строк (VACUUM).
  2. Сразу пометить status = 'processing' с отметкой времени и закоммитить. Обработка идёт вне транзакции, а отдельный процесс возвращает в очередь задачи, зависшие дольше таймаута. Это аренда (lease) — та же идея, что TTL в распределённых блокировках, со всеми её проблемами: задачу может обработать дважды, если воркер «проснулся» после таймаута. Обработчик должен быть идемпотентным.

SKIP LOCKED есть в PostgreSQL с 9.5, в MySQL с 8.0 и в Oracle. На таком механизме построены библиотеки очередей поверх PostgreSQL, вроде pg-boss и river. Когда поток задач измеряется тысячами в секунду, лучше переходить на брокер сообщений.

Как это связано с уровнями изоляции

Блокировки не отменяют уровни изоляции, а дополняют их:

  • Read Committed (PostgreSQL по умолчанию) — lost update возможен, закрываем атомарным UPDATE, FOR UPDATE или версией.
  • Repeatable Read в PostgreSQL сам ловит lost update: вторая транзакция при попытке обновить строку, изменённую после её снимка, получает 40001 could not serialize access due to concurrent update. По сути это встроенная оптимистичная блокировка, и транзакцию нужно повторять.
  • Repeatable Read в MySQL InnoDB так не делает: обычный SELECT читает снимок, а UPDATE пишет поверх последней версии. Lost update остаётся, нужен FOR UPDATE или версия.
  • Serializable закрывает всё, включая write skew, но платить приходится повторами транзакций.

Версия работает одинаково на любом уровне и в любой СУБД, поэтому её любят в коде, который должен быть переносимым.

Когда что выбирать

СитуацияПодход
Новое значение — простая формула от старогоАтомарный UPDATE ... SET x = x - 1 WHERE ...
Пользователь редактирует сущность в формеВерсия + ошибка «данные изменились»
REST API с параллельными клиентамиВерсия через ETag / If-Match
Конфликты редки, транзакция короткаяВерсия с автоматическим повтором
Горячая строка, много конкурентов (остаток популярного товара)FOR UPDATE или атомарный UPDATE
Несколько строк в одной операции (перевод между счетами)FOR UPDATE в фиксированном порядке
Повтор дорог или имеет побочные эффектыFOR UPDATE
Несколько воркеров разбирают задачи из таблицыFOR UPDATE SKIP LOCKED
Ресурс вне одной базы (файл, внешний API, несколько сервисов)Распределённая блокировка + fencing token

Главный критерий — частота конфликтов. При редких конфликтах оптимистичный подход почти бесплатен: никто не ждёт, повтор случается раз в тысячу запросов. При частых всё переворачивается: большая часть работы выбрасывается и повторяется, и под нагрузкой это может превратиться в шторм повторов. Тогда честная очередь на блокировке эффективнее.

Второй критерий — время между чтением и записью. Если это секунды или минуты (человек, внешний сервис), держать блокировку нельзя, остаётся версия.

Типичные ошибки

  • FOR UPDATE в autocommit. Блокировка снимается сразу после SELECT, гонка остаётся. Проверяйте, что чтение и запись в одной транзакции.
  • Внешние вызовы внутри транзакции с блокировкой. Платёжный шлюз ответил за 30 секунд — и 30 секунд все остальные запросы к этому счёту стоят.
  • Забыли проверить rowcount. UPDATE ... WHERE version = ? без проверки числа строк — это не блокировка, а молчаливая потеря изменений.
  • Версию не увеличили в одном из путей кода (админка, миграция, ручной SQL). Оптимистичная блокировка работает, только если её соблюдают все, кто пишет в таблицу.
  • Бесконечные повторы без задержки. Ограничивайте число попыток и добавляйте случайную паузу.
  • SKIP LOCKED в отчётах и проверках. Строки, которые сейчас кто-то держит, просто пропадут из результата.
  • Блокировки в разном порядке в разных местах кода — источник дедлоков, которые воспроизводятся только под нагрузкой.

Вопросы на собеседовании

«Что такое lost update и как его воспроизвести?» Две транзакции читают одно значение, вычисляют новое в приложении и пишут. Вторая запись затирает первую. Воспроизводится на Read Committed в любой популярной СУБД. Решения — атомарный UPDATE, FOR UPDATE, версия, в PostgreSQL ещё Repeatable Read.

«Чем оптимистичная блокировка отличается от пессимистичной?» Пессимистичная предотвращает конфликт: блокирует строку при чтении, конкуренты ждут. Оптимистичная обнаруживает конфликт: при записи проверяет версию, и проигравший повторяет. Первая платит ожиданием, вторая — повторами.

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

«Есть ли гонка между проверкой версии и записью?» Нет, если проверка и запись — один UPDATE ... WHERE version = ?. Строка блокируется на время выполнения, а конкурентный UPDATE после ожидания перепроверяет условие на свежей версии. Гонка появляется, если сначала сделать SELECT version, сравнить в коде, а потом UPDATE без условия.

«Что будет, если две транзакции блокируют строки в разном порядке?» Дедлок. База обнаружит цикл и откатит одну из транзакций. Профилактика — единый порядок блокировок, короткие транзакции, повтор при ошибке дедлока.

«Как сделать очередь задач на PostgreSQL?» SELECT ... FOR UPDATE SKIP LOCKED LIMIT n в подзапросе UPDATE ... RETURNING. Обсудить: держать ли транзакцию на время обработки, что делать с упавшими воркерами, идемпотентность обработчика, когда пора на брокер.

«Проектируем бронирование мест в кинотеатре. Как не продать одно место дважды?» Хороший ответ сочетает несколько уровней. Уникальный индекс на (session_id, seat_id) в таблице броней — последняя линия защиты. Временная бронь со сроком действия, пока пользователь оплачивает, вместо блокировки на время оплаты. Атомарный UPDATE seats SET status = 'held' WHERE id = ? AND status = 'free' или версия для смены статуса.

«Чем блокировка в БД отличается от распределённой блокировки?» Блокировка строки привязана к транзакции и соединению: упало соединение — блокировка снята, и база гарантирует, что владелец один. Распределённая блокировка на Redis или ZooKeeper живёт по TTL или сессии и не защищает от «проснувшегося» старого владельца без fencing token. Если ресурс — строки одной базы, блокировки базы проще и надёжнее.

Итог

Lost update не выдаёт ошибок, поэтому его находят по жалобам пользователей, а не по логам. Начните с вопроса, можно ли выразить изменение одним атомарным UPDATE, — чаще всего можно. Если нет, выбирайте по частоте конфликтов и длине операции: редкие конфликты и долгое «раздумье» — версия, горячие строки и короткие транзакции — SELECT ... FOR UPDATE. Для очередей задач — SKIP LOCKED. И в любом варианте проверяйте результат: rowcount для версии, порядок блокировок и таймауты для FOR UPDATE.

Потренироваться в спокойной обстановке

Все задачи