🔐 MVCC и блокировки
Postgres не перезаписывает строку при update, а кладёт рядом новую версию. На этом держится главное свойство базы: чтение не ждёт записи, а запись не ждёт чтения. За него платят мусором, который убирает VACUUM, и блокировками там, где две транзакции хотят изменить одно и то же. На собеседовании мидла отсюда спрашивают про select for update, дедлоки, оптимистичную блокировку и про то, почему таблица распухла. Все цифры ниже сняты на PostgreSQL 16 в Docker
🧠 Версии строк
У каждой версии строки есть служебные поля. xmin — номер транзакции, которая эту версию создала. xmax — номер транзакции, которая её удалила или заменила, ноль, если версия живая. ctid — физический адрес: страница и номер слота на ней. Их можно выбрать прямо в запросе:
select ctid, xmin, xmax, id, balance from accounts;
-- (0,1) | 733 | 0 | 1 | 100
update accounts set balance = 90 where id = 1;
select ctid, xmin, xmax, id, balance from accounts;
-- (0,3) | 734 | 0 | 1 | 90Строка переехала в слот 3, а в слоте 1 осталась старая версия с xmax = 734. Расширение pageinspect показывает обе:
| Слот | xmin | xmax | Что это |
|---|---|---|---|
| 1 | 733 | 734 | старая версия, balance 100 |
| 3 | 734 | 0 | новая версия, balance 90 |
Какую из них увидит запрос, решает снимок: список транзакций, закоммиченных на момент его создания. Версия видна, если её xmin закоммичен до снимка, а xmax пустой или не закоммичен к моменту снимка. Транзакция, взявшая снимок до 734, продолжает читать 100 и никого не ждёт. Так работают уровни изоляции со страницы «Транзакции и уровни изоляции»: Read Committed берёт новый снимок на каждый запрос, Repeatable Read один на транзакцию
delete версию не стирает, а только проставляет xmax. rollback тоже ничего не откатывает на диске: версии отменённой транзакции остаются, просто транзакция помечена отменённой, и её версии никто не видит
🧹 VACUUM и автовакуум
Старые версии, которые уже не видит ни один снимок, называют мёртвыми строками. Пока их не убрали, они занимают место в таблице и в индексах. Как это выглядит на таблице в миллион строк:
| Шаг | Размер таблицы | Мёртвых строк |
|---|---|---|
| вставили 1 млн строк | 42 МБ | 0 |
update orders set status = 'paid' по всем | 92 МБ | 1 000 000 |
vacuum orders | 92 МБ | 0 |
ещё один update по всем | 100 МБ | 1 000 000 |
vacuum full orders | 50 МБ | 0 |
Обычный vacuum помечает место мёртвых строк свободным, но файл почти не уменьшает: отрезать он может только пустые страницы в самом конце. Зато следующий массовый update лёг в освобождённое место, и таблица выросла всего на 8 МБ. Обычный vacuum работает параллельно с чтением и записью, ему мешает только смена схемы
vacuum full переписывает таблицу целиком и отдаёт место системе: 50 МБ вместо 100, а не 42, потому что статус shipped длиннее new. Цена — блокировка ACCESS EXCLUSIVE на всё время работы: таблицу нельзя даже читать. Ещё ему нужно свободное место на диске под новую копию. На живой нагрузке его не запускают, документация Postgres прямо советует обходиться обычным vacuum
Руками vacuum почти не зовут, это делает автовакуум. По умолчанию он проверяет базы раз в минуту и чистит таблицу, когда мёртвых строк больше чем 50 + 0.2 × число строк. Для таблицы на 10 млн строк это два миллиона мёртвых версий. Для больших горячих таблиц порог снижают точечно:
alter table orders set (autovacuum_vacuum_scale_factor = 0.02);Долгая транзакция держит мусор
VACUUM не имеет права удалить версию, которую может увидеть хоть один живой снимок. Поэтому одна открытая транзакция блокирует очистку во всей базе, а не только в своей таблице. В тесте сессия держала открытый Repeatable Read и читала таблицу accounts, а в это время в orders обновили 100 тысяч строк:
vacuum (verbose) orders;
tuples: 0 removed, 1100000 remain, 100000 are dead but not yet removable
-- после commit той сессии
tuples: 100000 removedНайти виновника помогает pg_stat_activity: у него заполнен backend_xmin, а now() - xact_start измеряется часами. Обычные причины на проде — забытая транзакция в состоянии idle in transaction, многочасовой отчёт, брошенный слот репликации. По умолчанию idle_in_transaction_session_timeout равен нулю, то есть такая сессия висит вечно. Таймаут ставят на роль сервиса: alter role app set idle_in_transaction_session_timeout = '5min'
Переполнение счётчика транзакций
Номер транзакции 32-битный, и Postgres сравнивает номера по кругу: для каждой транзакции примерно два миллиарда считаются прошлым и два миллиарда будущим. Чтобы старые строки не «уехали в будущее», VACUUM их замораживает: помечает видимыми для всех независимо от номера. Таблицу, где самая старая незамороженная транзакция старше 200 млн (autovacuum_freeze_max_age), автовакуум чистит принудительно, даже если он выключен. Если не успевает и до предела остаётся меньше трёх миллионов, база перестаёт выдавать новые номера и принимает только чтение. Авария редкая, но она объясняет, почему VACUUM нельзя просто выключить
🔐 Блокировки строк
Снимки решают проблему чтения. Две записи в одну строку снимком не развести: второй update ждёт, пока первая транзакция закончится. Ждать можно и явно, заблокировав строку при чтении:
begin;
select * from accounts where id = 1 for update;
-- проверили баланс в приложении
update accounts set balance = balance - 30 where id = 1;
commit;Пока транзакция открыта, чужой update, delete и select for update этой строки ждут. Обычный select не ждёт: в тесте он вернул баланс за 0,8 мс при висящем for update. Блокировки строк останавливают только тех, кто пишет или блокирует
Режимов четыре, от сильного к слабому:
| Режим | Кто берёт | Кого блокирует |
|---|---|---|
for update | delete, update ключевого столбца, явный select for update | все остальные режимы |
for no key update | любой другой update | всех, кроме for key share |
for share | явный запрос | update, delete, for update, for no key update |
for key share | проверка внешнего ключа при вставке дочерней строки | только for update |
Последние две строки объясняют частый вопрос про внешние ключи. Вставка в user_orders с user_id = 1 берёт for key share на пользователя. Пока другая транзакция меняет у этого пользователя имя, вставка проходит за 2 мс: обычный update берёт for no key update, и они совместимы. Стоит заблокировать пользователя через for update, и та же вставка ждёт 1,5 секунды до его коммита
Два модификатора меняют поведение при занятой строке. nowait сразу падает с could not obtain lock on row in relation "accounts". skip locked пропускает занятые строки, и на нём строят очередь задач прямо в Postgres:
-- каждый воркер забирает свою пачку
select id from jobs
where status = 'new'
order by id
limit 3
for update skip locked;Первый воркер получил задачи 1, 2, 3 и держит их. Второй в это же время за 1 мс получил 4, 5, 6, а не ждал. Документация честно предупреждает: результат такого запроса не согласован, для обычной бизнес-логики он не годится, только для таблиц-очередей
🧱 Блокировки таблиц
Каждый запрос берёт блокировку и на таблицу. Режимов восемь, на практике хватает четырёх:
| Режим | Кто берёт | С чем конфликтует |
|---|---|---|
ACCESS SHARE | любой select | только с ACCESS EXCLUSIVE |
ROW EXCLUSIVE | insert, update, delete | SHARE от create index, ACCESS EXCLUSIVE и ещё два режима |
SHARE | create index без concurrently | любая запись |
ACCESS EXCLUSIVE | многие формы alter table, drop, truncate, vacuum full | со всеми, включая select |
Отсюда самая коварная авария с миграцией. alter table orders add column note text выполняется за миллисекунды, но ему нужен ACCESS EXCLUSIVE. Если открыта транзакция, которая хотя бы раз читала таблицу, alter встаёт в очередь: блокировка таблицы держится до конца транзакции, а не до конца запроса. А все новые запросы к таблице встают за ним, даже обычные select, которые с первым запросом не конфликтуют. В тесте выглядело так:
pid 157 транзакция прочитала orders и не закрыта работает
pid 166 alter table orders add column note ждёт 157
pid 173 select count(*) from orders where id=5 ждёт 166Выборка одной строки по ключу заняла 5 секунд. На проде на месте 173 окажутся сотни запросов, пул соединений кончится, и сервис ляжет из-за миграции, которая сама по себе мгновенная. Лечение — lock_timeout перед DDL: миграция не ждёт дольше заданного, падает и повторяется позже:
set lock_timeout = '1s';
alter table orders add column note text;
-- через 1 с: ERROR: canceling statement due to lock timeoutПо умолчанию lock_timeout равен нулю, и ожидание бесконечно. Кто кого держит, показывает pg_blocking_pids(pid): в тесте у 166 это {157}, у 173 — {166}
💀 Дедлоки
Дедлок — две транзакции, каждая из которых ждёт блокировку, которую держит другая. Классика с переводом денег:
-- A: перевод с 1 на 2 -- B: перевод с 2 на 1
begin; begin;
update accounts ... where id = 1; update accounts ... where id = 2;
update accounts ... where id = 2; update accounts ... where id = 1;
-- A ждёт B -- B ждёт AСами по себе они не разойдутся. Когда ожидание длится дольше deadlock_timeout (по умолчанию 1 секунда), Postgres ищет цикл, находит его и откатывает одну из транзакций:
ERROR: deadlock detected
DETAIL: Process 123 waits for ShareLock on transaction 756; blocked by process 130.
Process 130 waits for ShareLock on transaction 755; blocked by process 123.Вторая транзакция в тесте сразу закоммитилась. Какую из двух откатят, документация предсказывать не обещает. Защита одна: брать блокировки в одном порядке. Для перевода это значит сначала обновить счёт с меньшим id, потом с большим, независимо от направления. Если порядок гарантировать нельзя, транзакцию после дедлока повторяют, как после ошибки сериализации. У дедлока свой код, SQLSTATE 40P01
🛠️ Оптимистичная блокировка
select for update — пессимистичная блокировка: строку держат с момента чтения до коммита. Она плохо подходит, когда между чтением и записью проходят секунды или минуты, например пользователь редактирует карточку товара в админке. Держать транзакцию открытой так долго нельзя
Оптимистичная блокировка ничего не держит. В таблице есть столбец version, приложение читает его вместе с данными и при записи проверяет, что он не изменился:
update accounts set balance = 70, version = version + 1
where id = 1 and version = 1; -- UPDATE 1, version стала 2
update accounts set balance = 80, version = version + 1
where id = 1 and version = 1; -- UPDATE 0: кто-то успел раньшеUPDATE 0 приложение превращает в ошибку конфликта: перечитать данные и попробовать снова или показать пользователю, что карточку уже изменили. В HTTP API это часто 409 Conflict или 412 с заголовком If-Match
| Пессимистичная | Оптимистичная | |
|---|---|---|
| Как | select for update в транзакции | столбец version в where |
| Конфликт | вторая транзакция ждёт | вторая запись получает UPDATE 0 |
| Подходит | короткие операции с частыми конфликтами: списание баланса | длинные сценарии с редкими конфликтами: редактирование в UI |
| Риск | дедлоки, долгие ожидания | много повторов при частых конфликтах |
⚠️ Где ошибаются
Запускают vacuum full на проде, чтобы вернуть место. Таблица закрыта для всех запросов, пока переписывается целиком. Если место правда нужно вернуть без простоя, используют расширение pg_repack, а чаще разбираются, почему автовакуум не успевал
Не ставят lock_timeout в миграциях. Добавление столбца, которое на тесте проходит мгновенно, на проде встаёт за аналитическим запросом и выстраивает очередь из всех запросов к таблице
Держат транзакцию открытой, пока ходят в сеть. Транзакция с for update, которая ждёт ответа платёжного шлюза, держит строку и снимок. Растут ожидания у соседей и мусор во всей базе
Обновляют пачку строк в случайном порядке. Два воркера, которые обновляют одни и те же 100 строк в разном порядке, ловят дедлоки под нагрузкой. Сортировка по ключу перед update или select ... order by id for update убирает проблему
Строят очередь на select for update без skip locked. Все воркеры выбирают одну и ту же первую задачу и выстраиваются друг за другом. Работает один, остальные ждут
🎯 Вопросы с собеседований
Что такое MVCC и зачем он нужен?
Multiversion concurrency control: при изменении строки база не перезаписывает её, а создаёт новую версию. Каждый запрос читает данные по своему снимку и видит те версии, которые были закоммичены к его началу. Поэтому чтение не ждёт записи и запись не ждёт чтения. Цена — старые версии копятся, и их убирает VACUUM
Что происходит в Postgres при update строки?
Создаётся новая версия строки с xmin текущей транзакции, а у старой проставляется xmax. Старая остаётся на месте, пока её видят чьи-то снимки, потом её убирает VACUUM. Индексы тоже получают новую запись, если только обновление не HOT: новая версия легла на ту же страницу и индексированные столбцы не менялись
Что делает VACUUM и чем отличается от VACUUM FULL?
Обычный VACUUM находит мёртвые версии, которые уже никто не видит, и помечает их место свободным для новых строк. Он работает параллельно с чтением и записью, но файл таблицы почти не уменьшает. Ещё он замораживает старые строки и обновляет карту видимости. VACUUM FULL переписывает таблицу целиком и возвращает место системе, но держит ACCESS EXCLUSIVE всё это время, так что таблица недоступна даже для чтения
Почему таблица растёт, хотя строк в ней не прибавляется?
Каждый update оставляет мёртвую версию, и пока VACUUM её не убрал, она занимает место. Обычно автовакуум не успевает: таблица большая, а порог по умолчанию 20% строк, или очистку держит долгая открытая транзакция. Смотреть n_dead_tup в pg_stat_user_tables, время последнего автовакуума там же и backend_xmin в pg_stat_activity
Чем select for update отличается от обычного select?
Обычный select ничего не блокирует и читает по снимку. for update блокирует найденные строки до конца транзакции: чужие update, delete и select for update по ним будут ждать. Обычные select при этом продолжают читать без ожидания. Его применяют в сценарии «прочитать, проверить, записать», чтобы между чтением и записью строку никто не изменил
Что такое дедлок и как Postgres с ним справляется?
Две транзакции ждут друг друга по кругу: A держит строку 1 и хочет 2, B держит 2 и хочет 1. Через deadlock_timeout, по умолчанию секунду ожидания, Postgres проверяет граф ожиданий, находит цикл и откатывает одну транзакцию с ошибкой deadlock detected. Чтобы дедлоков не было, блокировки берут в одном порядке, например по возрастанию id. Если это невозможно, транзакцию повторяют
Оптимистичная или пессимистичная блокировка: когда что?
Пессимистичная, select for update, держит строку от чтения до коммита и подходит для коротких операций, где конфликт вероятен, например списания со счёта. Оптимистичная ничего не держит: при записи проверяется version в where, и если строку изменили, update вернёт ноль строк, а приложение повторит операцию или сообщит о конфликте. Её берут для длинных сценариев с редкими конфликтами, вроде редактирования формы в интерфейсе
Почему миграция с add column положила сервис?
alter table нужен ACCESS EXCLUSIVE, и он ждёт, пока закончатся все открытые транзакции, которые трогали таблицу. Пока он ждёт, все новые запросы, даже обычные select, встают в очередь за ним. Одна долгая транзакция плюс мгновенная миграция дают остановку всех запросов к таблице. Защита — set lock_timeout перед DDL и повтор миграции
Как сделать очередь задач на Postgres?
Таблица задач и select ... where status = 'new' order by id limit N for update skip locked в транзакции. Каждый воркер блокирует свою пачку, а занятые строки остальные пропускают, вместо того чтобы ждать. После обработки воркер меняет статус и коммитит. Если воркер упал, транзакция откатится, блокировка снимется, и задачи заберёт другой
📚 Что почитать
- Explicit Locking — все режимы блокировок таблиц и строк с таблицей конфликтов, дедлоки и advisory-блокировки
- Routine Vacuuming — что делает VACUUM, формулы порогов автовакуума и переполнение счётчика транзакций
- SELECT, раздел The Locking Clause —
for update,nowaitиskip lockedс оговорками - PostgreSQL 18 изнутри — книга Егора Рогова, бесплатный PDF. Части про многоверсионность, очистку и блокировки