Senior: питання на співбесіді з теми «Транзакції й блокування»

MVCC (Multi-Version Concurrency Control) - спосіб дати паралельним транзакціям узгоджений знімок даних без блокувань на читання. Читачі не блокують письменників, письменники - читачів.

Як це влаштовано в PostgreSQL: UPDATE не змінює рядок на місці, а створює нову версію рядка, позначаючи стару як застарілу (з якої транзакції вона вже невидима). DELETE лише позначає рядок. Кожна транзакція бачить ті версії, що були актуальні на момент її знімка.

Звідси потреба у VACUUM: старі версії («мертві кортежі») лишаються в таблиці, доки жодна транзакція вже не може їх бачити. VACUUM:

• звільняє місце мертвих кортежів для повторного використання;
• оновлює visibility map (потрібна для index-only scan);
• «заморожує» старі ідентифікатори транзакцій, щоб уникнути переповнення лічильника транзакцій (transaction ID wraparound) - аварійної ситуації, за якої база перестає приймати запис.

Зазвичай усе це робить фоновий autovacuum.

Типові проблеми на проді:

• Роздування (bloat) таблиць з частими UPDATE: autovacuum не встигає, таблиця й індекси ростуть, запити сповільнюються. Лікують тонким налаштуванням autovacuum для конкретних таблиць.
• Довга транзакція (чи забута відкрита сесія idle in transaction) не дає вакууму прибирати навіть старі версії по всій базі.
• VACUUM FULL повертає місце ОС, але переписує таблицю під ексклюзивним блокуванням - на проді його уникають.

InnoDB теж використовує MVCC, але зберігає старі версії в undo log, тож окремого вакууму не потребує (його роль виконує фоновий purge).

Докладніше в документації: Регулярне очищення (VACUUM)

Advisory-блокування (PostgreSQL) - блокування за довільним числом, яке не прив'язане до жодного рядка чи таблиці. База лише гарантує, що один ключ одночасно тримає лише одна сесія; що цей ключ означає - вирішує застосунок.

-- Чекати, доки звільниться
SELECT pg_advisory_lock(42);
-- ... робота ...
SELECT pg_advisory_unlock(42);

-- Не чекати: true, якщо взяли, false - якщо зайнято
SELECT pg_try_advisory_lock(hashtext('import:prices'));

-- Знімається автоматично в кінці транзакції
SELECT pg_advisory_xact_lock(42);

Коли це доречно:

• Захистити дію, а не рядок: «лише один процес імпорту одночасно», «лише один сервер виконує міграції під час деплою».
• Блокування того, чого ще немає: не можна взяти FOR UPDATE на рядок, якого ще не створено, а advisory-ключ від, наприклад, email - можна.
• Без зайвої інфраструктури: коли Redis для розподілених блокувань немає, а PostgreSQL уже є.

Пастки:

• Сесійні блокування живуть, поки живе з'єднання. Якщо процес «забув» зняти блокування, але з'єднання лишилося в пулі, ключ буде зайнятий безстроково. Транзакційний варіант (_xact_) безпечніший.
• З PgBouncer у режимі transaction pooling сесійні блокування ламаються: наступний запит може піти іншим з'єднанням.
• Ключ - число; рядкові ідентифікатори перетворюють хешем, і про можливі колізії варто пам'ятати.

MySQL має схожі GET_LOCK('name', timeout) / RELEASE_LOCK('name') з рядковими іменами.

Докладніше в документації: Advisory-блокування

Навіть транзакція, що лише читає і не тримає конфліктних блокувань, впливає на всю базу через MVCC.

Горизонт вакууму. VACUUM може прибрати мертву версію рядка, лише якщо вона невидима жодній активній транзакції. Найстаріша відкрита транзакція (чи знімок) встановлює горизонт: усі версії, що з'явилися після її початку, захищені від очищення - у всіх таблицях бази, не лише тих, що вона читала.

Що відбувається, поки транзакція висить годинами:

• Мертві версії накопичуються в таблицях з частими оновленнями (черги, лічильники, сесії).
• Таблиці й індекси роздуваються, запити сповільнюються - бо читають сторінки, повні мертвих рядків.
• Autovacuum працює, але марно: «не може видалити, бо ще потрібні».
• Після закриття транзакції роздуття саме не зникає - місце повторно використовується, але файли лишаються великими.

Джерела довгих транзакцій:

• idle in transaction - застосунок відкрив транзакцію й робить щось повільне поза базою (HTTP-запит, обробка файлу) або впав, не закривши її.
• Довгі звіти й аналітика на primary.
• Репліка з hot_standby_feedback = on, де йде довгий запит, - вона теж тримає горизонт на primary.
• Забуті prepared transactions і неактивні слоти реплікації (утримують WAL і горизонт).

Як знайти:

SELECT pid, state, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Захист:

• idle_in_transaction_session_timeout - розривати сесії, що зависли в транзакції.
• transaction_timeout (PostgreSQL 17+) - обмеження на тривалість усієї транзакції.
• Аналітику - на репліку без hot_standby_feedback чи в окреме сховище.
• У коді - не робити повільних зовнішніх дій усередині DB::transaction().

Докладніше в документації: Налаштування клієнтських з'єднань

За замовчуванням PostgreSQL чекає безкінечно: запит може виконуватися годинами, а очікування блокування - тривати вічно. Таймаути перетворюють «завис» на зрозумілу помилку.

• statement_timeout - максимальний час виконання однієї команди. Після нього запит скасовується з помилкою.
• lock_timeout - максимальний час очікування блокування. Сам запит може йти довго, але чекати на чужі блокування - не довше вказаного.
• idle_in_transaction_session_timeout - розірвати сесію, що простоює всередині відкритої транзакції.
• transaction_timeout (PostgreSQL 17+) - ліміт на всю транзакцію.

Де налаштовувати - на різних рівнях:

-- для ролі застосунку
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

-- для окремої сесії чи транзакції
SET lock_timeout = '3s';                    -- для сесії
SET LOCAL statement_timeout = '5min';       -- лише до кінця поточної транзакції

Типові значення:

• Веб-запити: statement_timeout кілька секунд - веб-запит, що чекає базу хвилину, однаково нікому не потрібен, а тримає з'єднання з пулу.
• Міграції: lock_timeout 2-5 секунд, щоб ALTER TABLE не вишикував чергу з усіх запитів; statement_timeout - побільше або вимкнений для довгих операцій на зразок CREATE INDEX CONCURRENTLY.
• Звіти й фонові задачі: окрема роль чи SET LOCAL з більшими лімітами.

Застереження:

• Глобальний statement_timeout у postgresql.conf зачепить і адміністративні операції - pg_dump, вакуум вручну. Краще налаштовувати на роль застосунку.
• З PgBouncer у режимі transaction pooling SET без LOCAL «протікає» в інші сесії - використовувати SET LOCAL або налаштування ролі.
• Помилку таймауту застосунок має обробляти: логувати з текстом запиту й показувати користувачу зрозуміле повідомлення, а не 500.

Докладніше в документації: statement_timeout

PostgreSQL можна використати як надійну чергу без окремого брокера - так працюють драйвер database у Laravel, good_job у Rails, pg-boss, River.

Основа - FOR UPDATE SKIP LOCKED:

BEGIN;

SELECT id, payload
FROM jobs
WHERE queue = 'default' AND available_at <= now()
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- обробили завдання
DELETE FROM jobs WHERE id = $1;
COMMIT;

Кілька воркерів одночасно беруть різні рядки: заблокований іншим воркером рядок просто пропускається. Якщо воркер впав, транзакція відкотиться, блокування зніметься - завдання повернеться в чергу автоматично.

LISTEN / NOTIFY - щоб воркери не опитували таблицю постійно:

-- воркер
LISTEN new_job;

-- при додаванні завдання (можна тригером)
NOTIFY new_job;

Повідомлення доставляються після коміту транзакції-відправника, тож воркер не прокинеться раніше, ніж завдання стане видимим.

Переваги: транзакційність - завдання створюється в тій самій транзакції, що й дані (немає завдань для незбережених замовлень і загублених завдань), одна система замість двох, звичні бекапи й моніторинг.

Межі:

• Навантаження: тисячі завдань на секунду - уже відчутно для бази: кожне завдання - вставка, оновлення, видалення, мертві версії й вакуум. Таблиця черги роздувається й потребує агресивного autovacuum.
• NOTIFY не надійний як черга: повідомлення не зберігаються, якщо слухача немає; розмір payload обмежений (8000 байтів). Це лише «будильник», джерело правди - таблиця.
• Конкуренція з основним навантаженням бази за ресурси.

Коли брати Redis/RabbitMQ/SQS: високий потік завдань, потреба в pub/sub, затримках і пріоритетах без навантаження на основну базу. Для типового застосунку з десятками-сотнями завдань на хвилину черга в PostgreSQL - цілком розумний вибір.

Докладніше в документації: NOTIFY

Двофазний коміт (2PC) - протокол, щоб кілька незалежних баз (чи ресурсів) закомітили спільну транзакцію атомарно: або всі, або жодна.

1. Фаза підготовки: координатор просить кожного учасника підготуватися. Учасник виконує все, крім фіксації, гарантує, що зможе закомітити навіть після перезапуску, і відповідає «готовий».
2. Фаза фіксації: якщо готові всі - координатор надсилає COMMIT кожному; якщо хтось відмовив - ROLLBACK усім.

У PostgreSQL це команди:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'transfer-42';     -- фаза 1

-- пізніше, за рішенням координатора
COMMIT PREPARED 'transfer-42';         -- фаза 2
-- або ROLLBACK PREPARED 'transfer-42';

За замовчуванням вимкнено: max_prepared_transactions = 0.

Чому в застосунках його уникають:

• Блокування на час координації. Підготовлена транзакція тримає блокування й горизонт вакууму, доки координатор не прийме рішення. Якщо координатор упав між фазами, транзакція «зависає» - її доводиться розв'язувати вручну, а тим часом таблиці роздуваються.
• Координатор - єдина точка відмови й складна частина системи, яку треба писати й підтримувати.
• Не всі учасники підтримують 2PC: зовнішні API, черги, пошта не вміють «підготуватися».
• Знижує доступність: транзакція можлива, лише коли доступні всі учасники.

Що використовують натомість:

• Одна база - найпростіше, якщо дані можна тримати разом.
• Transactional outbox: подія записується в ту саму базу, що й зміна, в одній транзакції, а окремий процес надійно відправляє її далі.
• Саги - послідовність локальних транзакцій з компенсуючими діями при збої («скасувати бронювання, якщо оплата не пройшла»).
• Ідемпотентність і повтори замість атомарності між системами.

Докладніше в документації: PREPARE TRANSACTION