PostgreSQL locksБлокировки в PostgreSQL

Which locks Postgres commands take, who they block, and how to change the schema on a busy database without the app noticing. An endless-question trainer at the end.

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

How it worksКак это устроено

Lock levels, what conflicts with what, and how the queue forms.

Уровни блокировок, что с чем конфликтует и как выстраивается очередь.

Table lock levelsУровни блокировки таблицы

Eight levels from weak to strong. The bar height shows how many levels a lock conflicts with, the color — what the app feels meanwhile. The word ROW in the name means "I work with rows", not "I locked a row": UPDATE … WHERE id = 42 holds ROW EXCLUSIVE on the table so it isn't rebuilt, and locks the row itself separately.

Восемь уровней от слабого к сильному. Высота столбца показывает, со сколькими уровнями конфликтует блокировка, цвет — что в это время почувствует приложение. Слово ROW в названии значит «работаю со строками», а не «заблокировал строку»: UPDATE … WHERE id = 42 держит ROW EXCLUSIVE на таблицу, чтобы её не перестроили, а саму строку блокирует отдельно.

Compare two commandsСравнить две команды

Conflict matrixМатрица конфликтов 8 × 8

× — two locks can't be held on the same table at once. Click a row or column to highlight a level.

× — две блокировки не могут одновременно удерживаться на одной таблице. Нажмите на строку или столбец, чтобы выделить уровень.

QueueОчередь

Locks are granted in order, and the queue isn't jumped: a new request lines up behind a waiter if it conflicts with it, even when it doesn't conflict with those already working. Here you can build your own queue for the orders table. Time doesn't pass: a transaction holds its lock until you press COMMIT.

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

Only table locks are shown, without row locks and without waiting on old transactions for CONCURRENTLY.

Показаны только блокировки таблицы, без блокировок строк и без ожидания старых транзакций у CONCURRENTLY.

Row locksБлокировки строк

Taken only on the rows a query touched. A plain SELECT neither locks a row nor waits: it reads the last committed version. Row locks are stored in the rows themselves, so pg_locks shows them only when someone is waiting.

Берутся только на те строки, которые затронул запрос. Обычный SELECT строки не блокирует и не ждёт: он читает последнюю закоммиченную версию. Блокировки строк хранятся в самих строках, поэтому в pg_locks их видно, только когда кто-то ждёт.

An UPDATE that changes only ordinary columns (name, status, last_login) takes the weaker FOR NO KEY UPDATE: references to the row from other tables won't break. An UPDATE of a key column and DELETE take FOR UPDATE, because references may become invalid afterwards.

UPDATE, который меняет только обычные колонки (имя, статус, last_login), берёт более слабую FOR NO KEY UPDATE: ссылки на строку из других таблиц от этого не сломаются. UPDATE ключевой колонки и DELETE берут FOR UPDATE, потому что после них ссылки могут стать недействительными.

Foreign-key example. Inserting an order with user_id = 7 takes FOR KEY SHARE on the row of user 7 so it isn't deleted. A plain UPDATE of the user, for example last_login, takes FOR NO KEY UPDATE. These levels are compatible, so order inserts and user updates don't interfere. A DELETE of the user takes FOR UPDATE and will wait.

Пример с внешним ключом. Вставка заказа с user_id = 7 берёт FOR KEY SHARE на строку пользователя 7, чтобы его не удалили. Обычный UPDATE пользователя, например last_login, берёт FOR NO KEY UPDATE. Эти уровни совместимы, поэтому вставки заказов и обновления пользователя не мешают друг другу. А DELETE пользователя берёт FOR UPDATE и будет ждать.

Instead of waiting: with NOWAIT the query fails immediately with an error, with SKIP LOCKED locked rows are skipped (handy for task queues), with lock_timeout the query waits no longer than the set limit.

Вместо ожидания: с NOWAIT запрос сразу падает с ошибкой, с SKIP LOCKED заблокированные строки пропускаются (удобно для очередей задач), с lock_timeout запрос ждёт не дольше заданного.

Changing the schemaКак менять схему

What to check before a migration, what to replace dangerous commands with, and how to check your SQL.

Что проверить перед миграцией, чем заменить опасные команды и как проверить свой SQL.

Before a migrationПеред миграцией

RecipesРецепты

A dangerous command, a safe replacement, and the cost of that replacement. The timeouts from the checklist are assumed in every recipe. The safe variants share a common cost:

Опасная команда, безопасная замена и цена этой замены. Таймауты из чеклиста подразумеваются в каждом рецепте. У безопасных вариантов есть общая цена:

The plain variant makes more sense when the table is small, is created in the same migration, there's no load, or the change must go in one transaction with others.

Обычный вариант разумнее, когда таблица маленькая, создаётся в той же миграции, нагрузки нет или изменение должно пройти в одной транзакции с другими.

Check a migrationПроверить миграцию

Paste your migration SQL: dangerous commands are highlighted, with links to recipes nearby. The check runs by patterns right in the browser, is sent nowhere, and sees neither table sizes nor load.

Вставьте SQL миграции: опасные команды подсветятся, рядом будут ссылки на рецепты. Проверка идёт по шаблонам прямо в браузере, никуда не отправляется и не видит ни размер таблиц, ни нагрузку.

Command referenceСправочник команд

What lock each command takes, who it stops, and how much work it does.

Какую блокировку берёт каждая команда, кого она остановит и сколько работы сделает.

Migration toolsИнструменты миграций

Most often safe techniques break not in SQL but in the tool: it wraps the migration in a transaction and CONCURRENTLY fails, or it doesn't set timeouts.

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

When something breaksКогда что-то сломалось

Diagnostic queries, deadlocks, table bloat, and walkthroughs of typical incidents.

Запросы для диагностики, дедлоки, раздувание таблиц и разборы типичных инцидентов.

How to see what's lockedКак посмотреть, что заблокировано

Queries are grouped by symptom. You can copy without expanding.

Запросы сгруппированы по симптому. Копировать можно не раскрывая.

Something is hanging right nowЧто-то висит сейчас

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state,
       wait_event_type, wait_event,
       now() - xact_start AS xact_age, left(query, 80) AS query
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
ORDER BY xact_start;

SELECT l.pid, l.relation::regclass AS object, l.mode, l.granted,
       left(a.query, 60) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'relation'
  AND l.database = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY object, l.granted DESC;

SELECT l.pid, l.locktype, l.relation::regclass AS tbl, l.page, l.tuple,
       l.transactionid, l.mode, l.granted
FROM pg_locks l
WHERE l.locktype IN ('tuple', 'transactionid');

-- find the row by the address from page and tuple
SELECT * FROM orders WHERE ctid = '(1234,5)';

SELECT l.pid, l.locktype, l.relation::regclass AS tbl, l.page, l.tuple,
       l.transactionid, l.mode, l.granted
FROM pg_locks l
WHERE l.locktype IN ('tuple', 'transactionid');

-- найти строку по адресу из page и tuple
SELECT * FROM orders WHERE ctid = '(1234,5)';

SELECT l.pid, l.classid, l.objid, l.mode, l.granted,
       a.state, a.application_name, now() - a.state_change AS since,
       left(a.query, 60) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory'
ORDER BY l.granted DESC, since DESC;

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

CREATE EXTENSION IF NOT EXISTS pgrowlocks;

SELECT t.id, r.modes, r.pids
FROM orders t
JOIN pgrowlocks('orders') r ON t.ctid = r.locked_row;

Tables are bloatingТаблицы раздуваются

SELECT pid, state, backend_xid, backend_xmin,
       now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY xact_start
LIMIT 10;

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

During and after a migrationВо время и после миграции

SELECT pid, phase, blocks_done, blocks_total, lockers_done, lockers_total
FROM pg_stat_progress_create_index;

SELECT indexrelid::regclass AS index, indrelid::regclass AS tbl
FROM pg_index
WHERE NOT indisvalid;

Post-mortemРазбор задним числом

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

DeadlocksДедлоки

A deadlock is when two transactions wait for each other: each holds what the other needs. They won't get out of it on their own, so if a transaction waits for a lock longer than deadlock_timeout (1 second by default), Postgres checks for a cycle and cancels one of the transactions with error 40P01. The other then continues.

Дедлок — это когда две транзакции ждут друг друга: каждая держит то, что нужно другой. Сами они из этого не выйдут, поэтому если транзакция ждёт блокировку дольше deadlock_timeout (по умолчанию 1 секунда), Postgres проверяет, нет ли цикла, и отменяет одну из транзакций с ошибкой 40P01. Вторая после этого продолжает работу.

How to avoid. Lock rows in the same order in all transactions, for example by ascending id. It's convenient to take everything you need up front in one query: SELECT … WHERE id IN (1, 2) ORDER BY id FOR UPDATE. Keep transactions short. Handle error 40P01 by retrying the transaction in code: even with a careful order, rare deadlocks are possible.

Как избегать. Блокировать строки во всех транзакциях в одном порядке, например по возрастанию id. Удобно брать всё нужное заранее одним запросом: SELECT … WHERE id IN (1, 2) ORDER BY id FOR UPDATE. Держать транзакции короткими. Обрабатывать ошибку 40P01 повтором транзакции в коде: даже при аккуратном порядке редкие дедлоки возможны.

Long transactions and VACUUMДолгие транзакции и VACUUM

UPDATE doesn't overwrite a row, it creates a new version of it; the old one becomes dead. VACUUM removes dead versions, but only those that no open transaction can still see. So one long transaction, even one that blocks nothing and works with another table, prevents cleaning every table in the database, and they bloat.

UPDATE не перезаписывает строку, а создаёт её новую версию; старая становится мёртвой. VACUUM удаляет мёртвые версии, но только те, которые уже не может увидеть ни одна открытая транзакция. Поэтому одна долгая транзакция, даже ничего не блокирующая и работающая с другой таблицей, мешает чистить все таблицы базы, и они раздуваются.

Protection: idle_in_transaction_session_timeout closes forgotten transactions, statement_timeout limits long queries, and the "Oldest transactions" query from diagnostics shows who holds the horizon right now.

Защита: idle_in_transaction_session_timeout закрывает забытые транзакции, statement_timeout ограничивает долгие запросы, а запрос «Самые старые транзакции» из диагностики показывает, кто держит горизонт сейчас.

Incident walkthroughsРазборы инцидентов

Typical lock situations in prod: what you see in monitoring, what to do right now, and how to prevent a repeat.

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

TrainerТренажёр

Questions are generated at random and never run out, answers are saved in this browser. Commands and levels in the text are clickable. Keys: 1–4 — answer, ← and → — flip.

Вопросы генерируются случайно и не заканчиваются, ответы сохраняются в этом браузере. Команды и уровни в тексте кликабельны. Клавиши: 1–4 — ответ, ← и → — листать.

Details for each command: the PostgreSQL documentation, the Explicit Locking and ALTER TABLE sections.

Подробности по каждой команде: документация PostgreSQL, разделы Explicit Locking и ALTER TABLE.