Оптимистичная и пессимистичная блокировка: version, FOR UPDATE и SKIP LOCKED
Как победить lost update в базе данных: оптимистичная блокировка через version-колонку, SELECT ... FOR UPDATE, NOWAIT и SKIP LOCKED, дедлоки, выбор подхода и вопросы на собеседовании.
Коротко
| Оптимистичная | Пессимистичная | |
|---|---|---|
| Идея | Конфликтов мало — проверим при записи | Конфликты вероятны — заблокируем заранее |
| Механизм | 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 воркеры выстраивались бы в очередь за одними и теми же первыми строками.
В примере задача сразу помечается выполненной, чтобы не усложнять код. В реальности есть два подхода:
- Держать транзакцию на время обработки. Если воркер упал, транзакция откатится, и задача снова станет доступна. Просто, но долгие задачи держат соединение и открытую транзакцию, а это мешает очистке старых версий строк (VACUUM).
- Сразу пометить
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.