SQL для Senior: питання на співбесіду

3 питання рівня Senior з теми SQL з розгорнутими відповідями, порадами та перевіркою.

Питання Флеш-картки Тест3

Тема

Усі21 Core PHP4 Laravel4 Symfony2 WordPress1 SQL3 DevOps2 Yii1 Архітектура4

Рівень

Усі Junior3 Middle5 Senior3

3 питання

SQL

Коли потрібне партиціонування таблиць і як архівувати старі дані без простою?

SQL·Senior ·партиціонування ·PostgreSQL ·MySQL

Партиціонування потрібне тоді, коли дані природно ріжуться по ключу (найчастіше по часу) і старі шматки треба видаляти чи архівувати цілком: DROP/DETACH PARTITION — це операція над метаданими замість мільйонів DELETE. Прискорення читання — побічний ефект, і лише коли ключ партиціонування є в WHERE.

Як питають

Таблиця подій виросла до 400 млн рядків, DELETE старих даних не встигає за ніч — що робите?

Чи прискорить партиціонування наші SELECT-и?

Як перевести робочу таблицю на партиції без вікна простою?

Скільки партицій — це забагато?

Пояснення

Партиціонування розбиває одну логічну таблицю на набір фізичних таблиць за значенням ключа: у PostgreSQL це декларативні PARTITION BY RANGE | LIST | HASH (з версії 10), у MySQL — RANGE, RANGE COLUMNS, LIST, HASH, KEY на нативній реалізації InnoDB (з 8.0 інші рушії партиціонування не підтримують). Планувальник під час планування й виконання відкидає партиції, які не можуть містити потрібних рядків — це partition pruning, і саме тут головне непорозуміння: прунінг вимагає, щоб ключ партиціонування був у WHERE чи в умові з’єднання. Запит WHERE user_id = 42 на таблиці, партиціонованій по created_at, читає всі партиції і працює повільніше, ніж на непартиційованій таблиці з нормальним індексом.

Тому справжній мотив партиціонування — не читання, а життєвий цикл даних і обслуговування. Видалення місяця подій через ALTER TABLE ... DETACH PARTITION у PostgreSQL або ALTER TABLE ... DROP PARTITION у MySQL — це операція над метаданими й табличним простором, вона не породжує мільйони версій рядків, не роздуває WAL/undo, не створює лагу реплік і не залишає по собі bloat, який доводиться прибирати pg_repack. Побічно менші стають і VACUUM, ANALYZE, перебудова індексів: вони йдуть по одній партиції, а не по терабайтній таблиці. Плюс холодні партиції можна тримати на дешевшому табличному просторі або взагалі винести з бази.

Плата за це — обмеження й обовʼязкова автоматизація. У PostgreSQL ключ партиціонування має входити в первинний ключ і в кожен UNIQUE, глобальних унікальних індексів немає; зовнішні ключі на партиційовану таблицю зʼявилися лише в PG 12. У MySQL ключ теж мусить входити в усі унікальні ключі, а зовнішніх ключів на партиційованих InnoDB-таблицях немає взагалі. Партиції на майбутнє треба створювати наперед — планувальником, командою Artisan або pg_partman; інакше вставка або впаде, або осяде в DEFAULT-партиції, після чого підключення нової партиції з тим самим діапазоном вимагатиме сканування дефолтної під блокуванням.

Операційна частина архівації виглядає так. Відчепити партицію потрібно неблокуючим способом: DETACH PARTITION ... CONCURRENTLY (PG 14+, поза транзакційним блоком; при перерві залишає стан detach pending, який довершує FINALIZE). Далі відчеплена таблиця — звичайна таблиця: COPY у Parquet/CSV на обʼєктне сховище, перевірка контрольних сум і DROP TABLE. У MySQL аналог — EXCHANGE PARTITION WITH TABLE, щоб вийняти дані в окрему таблицю без копіювання. Обидві операції все одно чекають на метадані-блокування за довгими читаннями, тож нічний скрипт запускають з lock_wait_timeout (MySQL) або lock_timeout (PostgreSQL) і ретраями, а не «наосліп».

Межа застосовності проста: якщо немає ключа, по якому дані і фільтруються, і застарівають, партиціонування додасть складності без вигоди — краще індекси, keyset-пагінація і чистка пачками через pt-archiver. Партиціонування також не є шардингом: воно живе в межах одного сервера і не рятує від нестачі IOPS чи обʼєму, а лише робить обслуговування великої таблиці передбачуваним.

КодSQL

-- PostgreSQL 14+: розбиття по діапазону часу
CREATE TABLE events (
    id          bigserial,
    occurred_at timestamptz NOT NULL,
    payload     jsonb NOT NULL,
    PRIMARY KEY (id, occurred_at)   -- ключ партиціонування має бути в PK
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- Прунінг спрацює лише за наявності occurred_at у WHERE
EXPLAIN SELECT count(*) FROM events WHERE occurred_at >= '2026-09-01';

-- Архівація місяця: відчіпляємо без довгого блокування (не в транзакції)
ALTER TABLE events DETACH PARTITION events_2026_03 CONCURRENTLY;
-- відчеплена таблиця живе окремо: вивантажити в холодне сховище і прибрати
DROP TABLE events_2026_03;          -- метадані, а не мільйони DELETE

-- Підключення історичної таблиці без повного сканування під блокуванням
ALTER TABLE events_2026_02 ADD CONSTRAINT events_2026_02_rng
    CHECK (occurred_at >= '2026-02-01'
       AND occurred_at <  '2026-03-01') NOT VALID;
ALTER TABLE events_2026_02 VALIDATE CONSTRAINT events_2026_02_rng;
ALTER TABLE events ATTACH PARTITION events_2026_02
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- MySQL 8.4: та сама ідея, ключ мусить входити в кожен унікальний ключ
-- ALTER TABLE events DROP PARTITION p2026_03;
-- SET SESSION lock_wait_timeout = 5;  -- щоб не висіти за довгим читанням

Що хоче почути інтервʼюер

Що головний мотив — дешеве видалення й обслуговування (DROP PARTITION замість DELETE, VACUUM та REINDEX по одній партиції, бекап лише активної), а не магічне прискорення запитів.

Що прунінг партицій працює лише коли ключ партиціонування є в WHERE або JOIN; без нього план читає всі партиції, і стає гірше, ніж було.

Обмеження на ключі: у PostgreSQL ключ партиціонування має входити в PRIMARY KEY і будь-який UNIQUE; у MySQL — у кожен унікальний ключ, а зовнішні ключі на партиційованих InnoDB-таблицях не підтримуються.

Операційний план архівації: у PostgreSQL 14+ `DETACH PARTITION ... CONCURRENTLY`, вивантаження відчепленої таблиці й `DROP TABLE`; у MySQL — `ALTER TABLE ... DROP PARTITION` або `EXCHANGE PARTITION`.

Що партиції треба створювати наперед (планувальник, pg_partman), інакше вставка в неіснуючий діапазон падає або втрапляє в DEFAULT-партицію, з якої її потім важко дістати.

Якщо партиціонування не варіант — видалення пачками по keyset-діапазону з паузами, з оглядом на реплікаційний лаг, а не один гігантський DELETE.

Типові помилки

Партиціонувати «щоб SELECT був швидший», коли проблему вирішує звичайний індекс: партиціонування не замінює індекс і не зменшує кількість читаних рядків, якщо ключ не у WHERE.

Партиціонувати по `created_at`, а фільтрувати в запитах по `user_id`: прунінгу немає, планувальник обходить сотні партицій, латентність зростає.

Робити партицію на день на п’ять років уперед: тисячі партицій роздувають планування, `pg_class` і памʼять під час prepared statements.

Чистити історію одним `DELETE FROM events WHERE created_at < now() - interval '1 year'`: довга транзакція, розпухання undo/WAL, лаг реплік, а в PostgreSQL місце все одно не повертається без VACUUM FULL чи pg_repack.

Викликати `ATTACH PARTITION` на великій таблиці без заздалегідь провалідованого CHECK — база сканує її цілком під блокуванням.

Забути, що `DROP PARTITION` у MySQL і `DETACH` у PostgreSQL чекають на завершення довгих читань: без `lock_wait_timeout`/`statement_timeout` нічний скрипт стає інцидентом.

ПОРАДА

Сформулюйте критерій одним реченням: партиціонування виправдане, коли є природний ключ життєвого циклу даних і ви хочете видаляти дані шматками, а не рядками. І одразу назвіть ціну — прунінг лише по ключу, обмеження на унікальні ключі, обовʼязкова автоматизація створення партицій.

Додаткові питанняЗ ВІДПОВІДЯМИ

Два шляхи. Перший, у PostgreSQL: створити нову партиційовану таблицю, писати в неї (dual-write у застосунку або тригер на старій), фонову історію переливати пачками, потім перейменувати таблиці в одній транзакції. Другий, дешевший: створити порожню партиційовану таблицю, додати на стару таблицю CHECK на її діапазон дат через `NOT VALID` + `VALIDATE CONSTRAINT` (обидва без ACCESS EXCLUSIVE), і `ATTACH PARTITION` — тоді підключення не сканує дані, а стара таблиця стає партицією «все до сьогодні». У MySQL `ALTER TABLE ... PARTITION BY` перебудовує таблицю копіюванням, тому в проді це роблять через gh-ost або pt-online-schema-change.

Видаляти пачками по діапазону первинного ключа, а не по OFFSET: у циклі беремо `WHERE id > :last AND created_at < :cutoff ORDER BY id LIMIT 5000`, запамʼятовуємо останній id, кожна пачка — окрема коротка транзакція, між пачками пауза й перевірка лагу реплік (`pg_stat_replication` / `SHOW REPLICA STATUS`). Готова реалізація — `pt-archiver` з Percona Toolkit, який уміє одночасно писати рядки у файл чи архівну таблицю. У PostgreSQL після масового видалення місце всередині файлів звільнить autovacuum, а щоб повернути його ОС — `pg_repack` без ексклюзивного блокування.

Орієнтир — десятки-сотні, а не тисячі. Кожна партиція має власні файли, статистику й рядки в системних каталогах; планувальник у PostgreSQL до версії 12 обробляв усі партиції на етапі планування, і хоч у 12+ це набагато дешевше, а виконавчий прунінг (з 11) відсікає партиції вже під час виконання, витрати на планування та памʼять під prepared statements ростуть лінійно. Практичний вибір: партиція на місяць для довгої історії, на день — лише коли ретеншн короткий (наприклад, 30–90 днів логів). У MySQL ліміт жорсткий: 8192 партиції на таблицю, але задовго до нього стає боляче через відкриті файлові дескриптори.

Партиціонування — всередині одного сервера й однієї логічної таблиці: планувальник сам обирає партиції, транзакції і бекапи звичайні. Шардинг розкладає дані по різних інстансах, і за це платять розподіленими транзакціями, крос-шардовими JOIN-ами й ребалансуванням. Партиціонування вирішує проблему обслуговування та життєвого циклу даних, шардинг — проблему обсягу запису й обʼєму, який не вміщається в один вузол. Порядок дій майже завжди такий: індекси → партиціонування → репліки на читання → і лише потім шардинг.

Сторінка питання →

SQL

Як виникають deadlock-и і як ви їх діагностуєте та уникаєте?

SQL·Senior ·deadlock ·блокування ·InnoDB

Deadlock — це цикл очікувань блокувань: база сама його виявляє й відкочує одну транзакцію (MySQL 1213/40001, PostgreSQL 40P01), тому застосунок зобовʼязаний повторити її, а зменшують частоту єдиним порядком блокування, короткими транзакціями та індексами під UPDATE.

Як питають

Раз на добу в логах «Deadlock found when trying to get lock; try restarting transaction» — що робити?

Чим deadlock відрізняється від lock wait timeout?

Два запити оновлюють різні рядки — звідки взагалі взявся deadlock?

Чи можна позбутися deadlock-ів повністю, а не ретраїти їх?

Пояснення

Deadlock виникає, коли транзакція A тримає блокування, потрібне B, а B тримає блокування, потрібне A: у графі очікувань зʼявляється цикл, який не розірветься сам ніколи. Тому бази не чекають, а виявляють його. InnoDB перевіряє граф щоразу, коли транзакція стає в чергу за блокуванням (innodb_deadlock_detect увімкнено за замовчуванням), і відкочує жертву — транзакцію, яка змінила найменше рядків, тобто найдешевшу для скасування. PostgreSQL робить це ліниво: сесія, яка прочекала довше за deadlock_timeout (1 секунда), запускає перевірку, і якщо цикл знайдено, саме вона отримує помилку 40P01 deadlock detected із DETAIL про те, який процес на що чекав.

Це принципово інша подія, ніж lock wait timeout, і плутанина тут коштує найдорожче. Lock wait timeout exceeded (MySQL 1205) означає лінійне очікування без циклу, яке впʼялося в innodb_lock_wait_timeout — 50 секунд за замовчуванням; при дефолтному innodb_rollback_on_timeout=OFF відкочується лише останній оператор, і транзакція лишається відкритою з половиною змін, тож застосунок мусить явно зробити ROLLBACK. Deadlock натомість відкочує транзакцію-жертву повністю: повторювати треба весь блок від BEGIN, а не оператор, що впав. Звідси вимога до архітектури — блок транзакції має бути ідемпотентним, а листи, платіжні виклики та HTTP до зовнішніх систем живуть за межами COMMIT.

Друга поширена хиба — вважати, що без явного FOR UPDATE deadlock неможливий. Блокування бере кожен UPDATE, DELETE та INSERT, і причини найчастіше буденні: два сценарії оновлюють ті самі рядки в різному порядку; пакетний upsert з несортованим набором ключів; INSERT, який на конфлікті унікального індексу спершу бере shared-лок на існуючому записі; вставка в дочірню таблицю, що тримає лок на батьківському рядку за зовнішнім ключем. Окремо варто памʼятати, що блокування беруться по індексних записах: UPDATE ... WHERE status = 'pending' без індексу на status заблокує все, що просканував, а в MySQL на REPEATABLE READ ще й проміжки між записами через gap- і next-key-локи.

Діагностика зводиться до трьох кроків. У MySQL: SHOW ENGINE INNODB STATUS показує секцію LATEST DETECTED DEADLOCK з обома операторами, іменами індексів і режимами блокувань — але лише останній випадок, тому на проді вмикають innodb_print_all_deadlocks, і всі події падають в error log; поточні очікування видно в performance_schema.data_lock_waits і data_locks. У PostgreSQL текст помилки вже містить обидва процеси, а живу картину дають pg_blocking_pids() разом із pg_stat_activity, плюс log_lock_waits для очікувань довших за deadlock_timeout. Мета читання — знайти два місця в коді, які беруть ті самі обʼєкти в різному порядку.

Профілактика працює в такому порядку. Єдиний порядок блокування (найпростіше — завжди за зростанням первинного ключа: ORDER BY id ... FOR UPDATE, сортований і дедуплікований масив у пакетних операціях). Коротші транзакції: усе, що можна порахувати до BEGIN, рахується до нього, мережеві виклики виносяться назовні. Індекси під умови UPDATE, щоб площа блокування збігалася з набором рядків, які справді змінюються. Там, де вистачає одного оператора, блокування не потрібне взагалі — UPDATE ... WHERE balance >= :amount із перевіркою affected rows атомарний за визначенням, а для черг є FOR UPDATE SKIP LOCKED. І межа чесності: повністю прибрати deadlock-и в конкурентному застосунку неможливо, тому ретрай із невеликим backoff лишається обовʼязковим — правильна ціль не «нуль deadlock-ів», а рідкісні й непомітні для користувача.

КодSQL

-- Класичний цикл: сесії беруть ті самі рядки у зворотному порядку
-- Сесія A                                   | Сесія B
BEGIN;                                    -- | BEGIN;
UPDATE accounts SET balance = balance - 10
  WHERE id = 1;   -- X-лок на рядку 1     -- | UPDATE ... WHERE id = 2;  -- X-лок на 2
UPDATE accounts SET balance = balance + 10
  WHERE id = 2;   -- чекає на B           -- | UPDATE ... WHERE id = 1;  -- чекає на A
-- MySQL: ERROR 1213 (40001) Deadlock found; вся транзакція-жертва відкочена
-- PostgreSQL: ERROR 40P01 deadlock detected (детектор спрацював за deadlock_timeout)

-- Виправлення: єдиний порядок блокування в усіх сценаріях
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;  -- завжди за зростанням
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

-- Діагностика MySQL 8.0
SHOW ENGINE INNODB STATUS;                    -- секція LATEST DETECTED DEADLOCK, лише останній
SET GLOBAL innodb_print_all_deadlocks = ON;   -- усі випадки — в error log
SELECT * FROM performance_schema.data_lock_waits;  -- хто кого чекає прямо зараз

-- Діагностика PostgreSQL
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, left(query, 60)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
-- ALTER SYSTEM SET log_lock_waits = on;  -- очікування довші за deadlock_timeout (1 с) — у лог

-- Часто блокувань узагалі не треба: один атомарний оператор замість read-modify-write
UPDATE accounts SET balance = balance - 10 WHERE id = 1 AND balance >= 10;

Що хоче почути інтервʼюер

Що deadlock — це виявлений цикл у графі очікувань, а не таймаут: InnoDB перевіряє граф одразу і відкочує транзакцію, яка змінила менше рядків, PostgreSQL запускає детектор після deadlock_timeout (1 с) і вбиває ту сесію, чиє очікування спричинило перевірку.

Різницю з lock wait timeout: у MySQL deadlock (1213) відкочує всю транзакцію-жертву, а «Lock wait timeout exceeded» (1205) при дефолтному innodb_rollback_on_timeout=OFF відкочує лише оператор, лишаючи транзакцію відкритою з половиною змін.

Що deadlock можливий без жодного FOR UPDATE: звичайні UPDATE, INSERT з унікальним ключем, ON DUPLICATE KEY UPDATE, вставка в дочірню таблицю з FK і навіть один пакетний UPDATE конфліктують блокуваннями.

Конкретні інструменти діагностики: SHOW ENGINE INNODB STATUS і innodb_print_all_deadlocks, performance_schema.data_lock_waits; у PostgreSQL pg_blocking_pids(), pg_locks, log_lock_waits і DETAIL у тексті помилки.

Що блокування беруться по індексних записах: UPDATE без придатного індексу блокує все, що просканував, тому індекс — це не лише швидкість, а й менша площа конфлікту.

Типові помилки

Плутати deadlock із lock wait timeout і «лікувати» його підняттям innodb_lock_wait_timeout: цикл не розсмокчеться за жодний час очікування.

Вважати deadlock багом бази або наслідком неправильного рівня ізоляції й шукати рішення в SERIALIZABLE, який навпаки додає конфліктів.

Ретраїти транзакцію, всередині якої є Mail::send або запит до платіжного шлюзу: після повтору клієнт отримає два листи і два списання.

Ловити 40001 і повторювати лише останній оператор: транзакція-жертва вже відкочена цілком, повторювати треба її всю з самого BEGIN.

Робити пакетний UPDATE або upsert з масиву id у довільному порядку: два воркери з перетинними наборами блокують рядки в різній послідовності й регулярно зустрічаються в циклі.

Дивитись лише SHOW ENGINE INNODB STATUS і робити висновок про частоту: там зберігається тільки останній deadlock, без innodb_print_all_deadlocks історії немає.

ПОРАДА

Скажіть, що deadlock — штатна подія розподіленого доступу, а не аварія: правильна архітектура це «повторюваний блок транзакції + єдиний порядок блокування». І одразу назвіть різницю 1213 і 1205 — вона показує, що ви бачили ці помилки в проді, а не читали про них.

Додаткові питанняЗ ВІДПОВІДЯМИ

InnoDB відкочує транзакцію з найменшою кількістю змін (за обсягом undo), тобто найдешевшу для скасування — і робить це повністю, включно з уже виконаними операторами. PostgreSQL простіший: жертвою стає сесія, чиє очікування довше за deadlock_timeout запустило перевірку графа, і вона отримує 40P01. Для коду висновок один: жертва — це не завжди «неважливий» запит, тому повторюваним має бути весь блок транзакції, а всі неідемпотентні дії (листи, HTTP до зовнішніх систем, платежі) виносяться за COMMIT. У Laravel це другий аргумент DB::transaction($cb, 3) плюс ->afterCommit() для job-ів.

Блокування бере кожен UPDATE, DELETE та INSERT. Типові джерела: два UPDATE, що зачіпають ті самі рядки в різному порядку; INSERT з унікальним індексом, який при конфлікті бере S-лок на існуючому записі, а потім хоче X-лок — класика для ON DUPLICATE KEY UPDATE та INSERT ... ON CONFLICT з перетинними пакетами; вставка в дочірню таблицю, яка бере shared-лок на батьківський рядок за зовнішнім ключем; UPDATE без індексу на колонці умови, який блокує весь просканований діапазон. У MySQL на REPEATABLE READ додаються gap- і next-key-локи, тож конфліктувати можуть навіть вставки неіснуючих ще рядків у той самий проміжок індексу.

SHOW ENGINE INNODB STATUS показує дві транзакції: для кожної — активний оператор, а також рядки WAITING FOR THIS LOCK і HOLDS THE LOCK(S) із назвою індексу, номером сторінки й типом (RECORD LOCKS ... lock_mode X locks rec but not gap / gap before rec). Практично беруть три речі: які саме два індекси задіяні, який режим блокування, і в якому порядку кожна транзакція їх брала — цього достатньо, щоб знайти в коді два сценарії з різним порядком. Показано лише останній випадок, тому на проді вмикають innodb_print_all_deadlocks=ON, і всі deadlock-и пишуться в error log. Поточні очікування видно в performance_schema.data_lock_waits разом із data_locks (INFORMATION_SCHEMA.INNODB_LOCKS прибрано в 8.0).

Не боротися за ті самі рядки: SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED — заблоковані рядки пропускаються, і кожен воркер бере свій. Для пакетних оновлень — сортувати ключі перед виконанням (ORDER BY id у SELECT ... FOR UPDATE або сортований масив в upsert) і прибирати дублікати з пакета. Плюс загальне: тримати транзакцію короткою, рахувати все, що можна, до BEGIN, і не тримати блокування на час зовнішнього HTTP-виклику.

Сторінка питання →

SQL

Які рівні ізоляції транзакцій ви використовуєте і чому?

SQL·Senior ·транзакції ·ізоляція ·PostgreSQL

MySQL за замовчуванням REPEATABLE READ, PostgreSQL — READ COMMITTED; рівень визначає, які аномалії можливі, але втрачене оновлення закривають не рівнем, а атомарним UPDATE або SELECT FOR UPDATE.

Як питають

Що таке phantom read і на якому рівні він можливий?

Чому два паралельні запити списали з балансу більше, ніж там було?

Чому SERIALIZABLE вимагає повторювати транзакцію?

Пояснення

Рівень ізоляції визначає, що транзакція бачить із паралельних змін. READ UNCOMMITTED допускає читання незафіксованих даних і в PostgreSQL фактично дорівнює READ COMMITTED. READ COMMITTED бачить лише закомічені дані, але між двома своїми запитами може побачити різні значення. REPEATABLE READ фіксує знімок на початку транзакції; у PostgreSQL це також закриває фантоми, у MySQL InnoDB звичайні читання йдуть зі знімка, а locking reads захищені gap-локами. SERIALIZABLE гарантує результат, еквівалентний послідовному виконанню. За замовчуванням MySQL працює на REPEATABLE READ, PostgreSQL на READ COMMITTED, і для більшості застосунків дефолт лишають.

Практична проблема, яку рівень ізоляції не вирішує, — втрачене оновлення. Два запити читають баланс 100, обидва рахують у PHP і обидва записують результат: одне списання зникає. На READ COMMITTED і REPEATABLE READ це відбувається мовчки. Рішення — не читати й записувати окремо: UPDATE ... SET balance = balance - :amount WHERE balance >= :amount виконує обидві дії атомарно під блокуванням рядка, а нуль affected rows означає недостатньо коштів. Коли між читанням і записом потрібна логіка, застосовують SELECT ... FOR UPDATE у короткій транзакції.

SERIALIZABLE у PostgreSQL реалізований без блокувань: база відстежує залежності й відкидає одну з конфліктних транзакцій із помилкою serialization_failure. Це штатна ситуація, і код має повторити транзакцію цілком. Так само треба обробляти deadlock. У відповіді найкраще працює реальний випадок: подвійне списання, двічі використаний промокод, і те, як саме гонку закрили.

КодSQL

-- Lost update: обидві транзакції читають 100, обидві пишуть 100 - 70 = 30.
-- Рівень ізоляції нижче SERIALIZABLE цього не ловить.
SELECT balance FROM accounts WHERE id = 1;        -- 100 в обох сесіях
UPDATE accounts SET balance = 30 WHERE id = 1;    -- списали 140, баланс 30

-- Рішення 1: атомарний UPDATE з умовою, перевіряємо affected rows
UPDATE accounts
SET balance = balance - 70
WHERE id = 1 AND balance >= 70;                   -- друга сесія отримає 0 rows

-- Рішення 2: блокування рядка на час короткої логіки
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- друга сесія чекає
-- бізнес-перевірки в PHP
UPDATE accounts SET balance = balance - 70 WHERE id = 1;
INSERT INTO ledger (account_id, amount, operation_key) VALUES (1, -70, 'op-9f1c'); -- UNIQUE(operation_key)
COMMIT;

-- Рішення 3: SERIALIZABLE, готові повторити при 40001
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... на serialization_failure застосунок повторює всю транзакцію

-- Черга на базі: воркери беруть різні рядки без очікування
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

Що хоче почути інтервʼюер

Чотири рівні й аномалії, які кожен допускає: dirty read, non-repeatable read, phantom read, і lost update як окрема практична проблема.

Що READ COMMITTED бачить зміни інших транзакцій між своїми запитами, а REPEATABLE READ фіксує знімок на початку і в PostgreSQL також закриває фантоми.

Що патерн прочитати в PHP, порахувати, записати ламається на будь-якому рівні нижче SERIALIZABLE, а рішення це UPDATE з виразом і умовою або SELECT FOR UPDATE.

Що SERIALIZABLE у PostgreSQL це SSI без блокувань, який відкидає транзакцію з помилкою serialization_failure, тому код мусить бути готовий повторити її.

Досвід реальної гонки: подвійне списання, дублікат промокоду, перевищення ліміту, і як саме її закрили.

Типові помилки

Вважати, що REPEATABLE READ гарантує відсутність будь-яких аномалій, зокрема lost update.

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

Використовувати SELECT FOR UPDATE без транзакції або з довгою логікою всередині, тримаючи блокування на час HTTP-запиту.

Не обробляти deadlock і serialization failure: замість повтору транзакції показувати користувачу 500.

Не знати, що в MySQL REPEATABLE READ фантоми частково закриті gap-локами, а в PostgreSQL знімком, і поведінка при UPDATE конфліктних рядків відрізняється.

ПОРАДА

Наведіть реальний випадок гонки, який ловили в продакшені — це переконує краще за перелік рівнів. І покажіть, що знаєте різницю дефолтів MySQL і PostgreSQL.

Додаткові питанняЗ ВІДПОВІДЯМИ

Повторний SELECT з тією ж умовою повертає нові рядки, які вставила інша транзакція. За стандартом можливий на READ COMMITTED і REPEATABLE READ. У PostgreSQL REPEATABLE READ працює на знімку й фантомів не показує; у MySQL InnoDB звичайні SELECT читають знімок, але locking reads і UPDATE можуть побачити нові рядки, від чого захищають gap-локи.

PostgreSQL реалізує SERIALIZABLE через SSI: транзакції не блокують одна одну, а база відстежує залежності читань і записів. Якщо виявляється цикл, який неможливо впорядкувати послідовно, одна з транзакцій відкидається з помилкою 40001 serialization_failure. Це не помилка коду, а нормальна ситуація, тому застосунок мусить повторити транзакцію цілком, у Laravel це другий аргумент DB::transaction.

Атомарний UPDATE accounts SET balance = balance - :amount WHERE id = :id AND balance >= :amount і перевірка affected rows: нуль означає недостатньо коштів. База виконує читання й запис одним кроком під блокуванням рядка. Якщо потрібна складна логіка між читанням і записом, SELECT ... FOR UPDATE у транзакції, коротка секція і запис. Плюс унікальний ключ операції для ідемпотентності.

FOR UPDATE блокує рядки на запис і читання іншими locking reads, FOR SHARE дозволяє іншим читати з FOR SHARE, але не змінювати. SKIP LOCKED пропускає вже заблоковані рядки замість очікування, що робить його ідеальним для черг на базі: кілька воркерів беруть різні завдання без конфліктів. NOWAIT замість очікування одразу повертає помилку.

Сторінка питання →

Прогрес карток і тестів зберігається у профілі. Створити профіль·Увійти