К содержимому
Войти

PHP Road Map

🔐 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 показывает обе:

СлотxminxmaxЧто это
1733734старая версия, balance 100
37340новая версия, 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 orders92 МБ0
ещё один update по всем100 МБ1 000 000
vacuum full orders50 МБ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 updatedelete, 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 EXCLUSIVEinsert, update, deleteSHARE от create index, ACCESS EXCLUSIVE и ещё два режима
SHAREcreate 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. Части про многоверсионность, очистку и блокировки