Чому читання не чекає запису
Без версій база мала б вибір з двох поганих: або читач чекає, поки запис закомітять, або бачить напівзроблену зміну. дає третій шлях. UPDATE пише нову версію рядка, а стара лишається на місці, і кожна транзакція читає ту версію, яку дозволяє її . Читання нікого не блокує, і його ніхто не блокує. Блокуються лише два записи в той самий рядок.
У PostgreSQL обидві версії лежать у самій таблиці. Кожна має службові поля : хто її створив і хто видалив. UPDATE ставить старій версії xmax і дописує нову. Стара стає , коли її вже не бачить жоден знімок, і тоді її прибирає VACUUM або autovacuum.
InnoDB робить навпаки: у таблиці лише остання версія, а UPDATE змінює рядок на місці. Що було до зміни, InnoDB записує в , і рядок тримає вказівник на цей запис. Читач, якому потрібна старша версія, відновлює її з undo за своїм . Коли старі записи undo вже нікому не потрібні, їх прибирає purge.
Звідси головне правило обох баз: старі версії живуть, поки живе найстаріший знімок. Одна транзакція, яку забули закомітити, тримає мертві версії всієї бази. У PostgreSQL таблиці й індекси пухнуть, в InnoDB росте undo і повільнішають читання, яким доводиться розмотувати довгий ланцюжок версій.
Знімок на оператор чи на транзакцію
T1 двічі читає лічильник переглядів вакансії, а між її читаннями T2 збільшує його і комітить. Посередині версії рядка: суцільна рамка це закомічена версія, пунктир ще ні, закреслена стара, яку база тримає для чужих знімків.
Перемикайте рівень ізоляції T1. Рівні відрізняються тим, коли береться знімок, і від цього ж залежить, як довго базі доводиться тримати стару версію.
BEGIN
SELECT views
→ 7
→ 7
UPDATE views = 8 COMMIT SELECT views
→ 8 НЕПОВТОРЮВАНЕ
→ 7
COMMIT
Рівень за замовчуванням. Після t3 v1 лежить у таблиці з xmax = номер T2, і VACUUM, запущений між читаннями T1, уже може її прибрати: ми перевірили, що читаюча транзакція в стані idle in transaction на READ COMMITTED не має backend_xmin і не заважає прибиранню.
На READ COMMITTED InnoDB створює read view для кожного оператора і закриває після нього. Поки T1 простоювала між читаннями, 3 000 оновлень в іншій сесії підняли History list length лише до 89: purge встигав за ними.
Знімок береться першим оператором транзакції, а не командою BEGIN: зміна, закомічена між BEGIN і першим SELECT, видна. Поки T1 відкрита, VACUUM пише про v1 «dead but not yet removable».
Рівень за замовчуванням. Read view створює перше читання, а не START TRANSACTION. Щоб узяти знімок одразу, є START TRANSACTION WITH CONSISTENT SNAPSHOT. Друге читання InnoDB відновило з undo log: у самій таблиці вже 8.
Сценарій виконано на MySQL 8.0.46 і PostgreSQL 16.15 у двох сесіях. На обох рівнях обидві бази повернули те саме, що показано на таймлайні.
Де лежать старі версії і хто їх прибирає
Обидві бази дають читачу знімок, але зберігають історію по-різному, і від цього залежить, що саме болить: у PostgreSQL розмір таблиць, в InnoDB undo і довгі ланцюжки версій. Виберіть два-три варіанти. Поруч схема: де лежить стара версія після UPDATE.
| PostgreSQL | InnoDB | SQLite · WAL | MyISAM | |
|---|---|---|---|---|
| Де стара версія | у самій таблиці, поруч з новою | в undo log | стара сторінка в основному файлі | ✗ версій немає |
| Що робить UPDATE | нова версія рядка, старій ставиться xmax | змінює рядок на місці, старе значення в undo | нова сторінка дописується у файл -wal | на місці, під блокуванням таблиці |
| Хто прибирає | VACUUM і autovacuum | purge-потоки | checkpoint | ✗ не потрібно |
| Читання під час запису | ✓ не чекають | ✓ не чекають | ✓ не чекають, записувач один | ✗ чекають запису |
| Знімок за замовчуванням | на кожен оператор (READ COMMITTED) | з першого читання транзакції (REPEATABLE READ) | на всю транзакцію читання | ✗ немає |
| Довга транзакція | мертві версії не прибираються, таблиці й індекси пухнуть | росте History list length і undo, старі читання повільнішають | checkpoint не може дійти до кінця, файл -wal росте | ✗ транзакцій немає |
| Що дивитися | n_dead_tup, backend_xmin у pg_stat_activity | History list length, INNODB_TRX | розмір файлу -wal | Waiting for table level lock у PROCESSLIST |
| Коли брати | Читання дешеві, а за частими UPDATE має встигати autovacuum. Довгі транзакції тут найдорожчі. | Таблиця не пухне від UPDATE, зате довга транзакція роздуває undo, і хтось мусить розмотувати версії. | Знімок для всієї бази одразу. Без PRAGMA journal_mode = WAL читач блокує записувача: ми отримали database is locked. | Для порівняння: рушій без MVCC. Читач і записувач стоять в одній черзі на таблицю. |
Обидві версії в одній сторінці таблиці. Транзакція 738 створила нову і поставила себе в xmax старої. Кому видима яка, вирішує знімок читача. Після VACUUM слот 1 стає вказівником на слот 3, а місце старої версії вільним для нових рядків.
Схематично. У таблиці лише остання версія з прихованими полями DB_TRX_ID і DB_ROLL_PTR. Старе значення лежить в undo, і читач зі старим read view іде за вказівником. Довгий ланцюжок оновлень означає довгий шлях назад.
Значення перевірено на PostgreSQL 16.15, MySQL 8.0.46 і SQLite 3.45.1. Номери транзакцій у схемі PostgreSQL справжні, схема InnoDB умовна.
Як побачити версії рядка
У PostgreSQL версії видно прямо: xmin і xmax читаються звичайним SELECT, а розширення pageinspect показує сторінку таблиці разом з версіями, яких ваш знімок уже не бачить. InnoDB таких колонок у SQL не дає, але приховані поля запису видно в моніторі блокувань.
xmin | xmax | ctid | id | status ------+------+-------+----+-------- 737 | 738 | (0,1) | 1 | active lp | t_xmin | t_xmax | t_ctid ----+--------+--------+-------- 1 | 737 | 738 | (0,3) 2 | 737 | 0 | (0,2) 3 | 738 | 0 | (0,3) lp | lp_flags | t_xmin | t_xmax | t_ctid ----+----------+--------+--------+-------- 1 | 2 | | | 2 | 1 | 737 | 0 | (0,2) 3 | 1 | 738 | 0 | (0,3)
T1 бачить стару версію: xmax = 738 означає, що її замінює транзакція 738, але та ще не закомітила. На сторінці три записи: стара версія рядка 1 з посиланням t_ctid на нову (0,3), рядок 2 і нова версія. Після VACUUM слот 1 став перенаправленням (lp_flags = 2): це HOT-оновлення, колонка status не в індексі, тож індекс і далі веде на слот 1.
RECORD LOCKS space id 5 page no 4 n bits 72 index PRIMARY of table `demo`.`accounts` Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000000095f5; asc ;; 2: len 7; hex 01000000dd1f60; asc `;; 3: len 4; hex 80000046; asc F;;
Таблиця має дві колонки, id і balance, а полів у записі чотири. Поле 0 це id = 1, поле 3 це balance = 70 (0x46). Між ними приховані DB_TRX_ID (6 байтів, транзакція 0x95f5 = 38389, яка змінила рядок останньою) і DB_ROLL_PTR (7 байтів, вказівник на її запис в undo log).
BEGIN ще не знімок
Інтуїція підказує, що знімок береться на BEGIN. Ні MySQL, ні PostgreSQL так не роблять: знімок бере перше читання. Різниця видна, коли між BEGIN і першим SELECT інша сесія встигає закомітити. SQLite у режимі WAL бере знімок на першому читанні транзакції і тримає його до COMMIT.
9 -- перше читання вже після коміту B 9 -- знімок узято на START, 10 не видно
Read view створює перше звичайне читання. WITH CONSISTENT SNAPSHOT створює його одразу, і має сенс лише на REPEATABLE READ: на READ COMMITTED кожен оператор однаково бере свій.
views
-------
2 Документація PostgreSQL каже прямо: знімок на REPEATABLE READ береться на початку першого оператора, який не керує транзакцією. BEGIN і SET TRANSACTION до них не належать.
7 7 8
У режимі WAL записувач не чекає читача, а читач до кінця транзакції бачить базу такою, як на першому SELECT. У режимі журналу за замовчуванням (DELETE) той самий UPDATE отримав Error: stepping, database is locked (5).
Одна забута транзакція тримає всю базу
Типовий сценарій у PHP: відкрили транзакцію, щось прочитали, а потім пішли в HTTP-запит до зовнішнього API чи відправку листа. Сесія стоїть idle in transaction, її знімок старіший за всі зміни, що відбуваються далі, і база не може прибрати жодної версії, яку цей знімок теоретично може побачити.
Нижче в обох базах сесія A відкрила транзакцію REPEATABLE READ і прочитала таблицю, а сесія B оновила 100 000 рядків vacancies (PostgreSQL) і зробила 3 000 окремих оновлень (MySQL).
pid | state | backend_xmin | xact_age -------+---------------------+--------------+---------- 19339 | idle in transaction | 760 | 00:00:02 INFO: vacuuming "demo.public.vacancies" tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable removable cutoff: 760, which was 1 XIDs old when operation ended pg_size_pretty ---------------- 13 MB -- до UPDATE було 6672 kB -- після COMMIT у сесії A VACUUM: 100000 removed, розмір 13 MB -- після VACUUM FULL: 6672 kB
removable cutoff 760 це backend_xmin сесії A: усе новіше VACUUM чіпати не має права. Звичайний VACUUM місце позначає вільним для нових рядків, але файл не зменшує. Зменшує VACUUM FULL, який переписує таблицю під ACCESS EXCLUSIVE блокуванням. Транзакція на READ COMMITTED, яка вже щось записала, тримає прибирання так само, через свій backend_xid.
History list length 61 -- до оновлень History list length 3000 -- після 3 000 оновлень, A ще відкрита +-----------------+-----------+---------------------+---------------------+ | trx_id | trx_state | trx_started | trx_isolation_level | +-----------------+-----------+---------------------+---------------------+ | 421155131616472 | RUNNING | 2026-10-08 22:36:31 | REPEATABLE READ | +-----------------+-----------+---------------------+---------------------+
History list length рахує записи undo закомічених транзакцій, які purge ще не прибрав. Без довгої транзакції ті самі 3 000 оновлень не піднімали його вище кількох сотень, бо purge встигав. INNODB_TRX показує всі відкриті транзакції з часом старту: найстаріша і є винуватцем.
Як не тримати знімок довше, ніж треба
Laravel сам транзакцій не затягує: DB::transaction() комітить, щойно замикання повернулося. Затягує код усередині замикання. Усе нижче звірено з vendor/laravel/framework 13.30, SQL для PostgreSQL виконано на 16.15.
Що на сайті створює нові версії
Сайт працює на PostgreSQL з рівнем READ COMMITTED за замовчуванням, а налаштувань autovacuum ні в міграціях, ні в конфігу немає, тож діють стандартні пороги. Найбільше версій створюють два місця. Погодинна синхронізація зберігає кожну вакансію, яку джерело ще показує, одним UPDATE з усіма колонками, навіть коли змінився лише last_seen_at. У PostgreSQL це нова версія рядка на кожну вакансію щогодини, а старі прибирає autovacuum. І кожен перегляд сторінки вакансії це UPDATE vacancies SET views = views + 1. Довгих транзакцій у коді немає: усі DB::transaction() містять лише роботу з базою.
routes/console.phphiring:sync запускається щогодини з withoutOverlapping(50), тож два прогони синхронізації одночасно не оновлюють ті самі рядки.src/Hiring/Infrastructure/Catalog/EloquentJobPageQuery.phprecordView() збільшує views атомарно в самій базі. Колонка views не в індексі, тож PostgreSQL може зробити це HOT-оновленням без нових записів в індексах, якщо на сторінці таблиці є вільне місце.src/ContentOps/Infrastructure/Persistence/EloquentContentTaskRepository.phpleaseBatch() тримає транзакцію лише на час SELECT … FOR UPDATE і запису оренди. Обробка задачі йде вже після коміту, у локальному воркері.src/Hiring/Infrastructure/Persistence/EloquentApplicationRepository.phpsave() відгуку в транзакції пише рядок і нові події історії та виходить: зовнішніх викликів усередині немає.Де MVCC обертається проти вас
Зовнішній виклик усередині транзакції
DB::transaction() з HTTP-запитом до платіжки чи відправкою листа. Поки зовнішній сервіс думає, сесія стоїть idle in transaction і тримає старі версії всієї бази, а заодно й блокування рядків, які встигла змінити.
Натомість: У транзакції лише SQL. Зовнішні виклики до неї або після, через DB::afterCommit() чи job з afterCommit.
Вимкнути autovacuum, бо «він навантажує диск»
Мертві версії накопичуються, таблиці й індекси ростуть, запити читають усе більше сторінок. Окрім того, без VACUUM рано чи пізно настає захисне заморожування від переповнення лічильника транзакцій, і тоді VACUUM прийде сам, причому агресивно.
Натомість: Не вимикати, а налаштувати: частіше й меншими порціями для гарячих таблиць (autovacuum_vacuum_scale_factor на таблицю).
Чекати, що VACUUM зменшить файл
Після масового DELETE або UPDATE таблиця займає вдвічі більше місця, VACUUM пройшов, а розмір той самий. Звичайний VACUUM лише позначає місце вільним для нових рядків.
Натомість: Часто цього досить: таблиця заповнить дірки новими рядками. Якщо місце треба повернути системі, VACUUM FULL під ACCESS EXCLUSIVE блокуванням або pg_repack без довгого блокування.
Масова зміна однією транзакцією
UPDATE мільйона рядків або перенесення даних в одній транзакції тримає знімок і всі нові версії до кінця. В InnoDB відкат такої транзакції ще й іде через undo рядок за рядком і триває довше за саму зміну.
Натомість: Пакетами по кілька тисяч рядків з комітом після кожного (chunkById), якщо атомарність усього пакета не потрібна.
Шукати довгу транзакцію не там
Таблиця пухне, а повільних запитів у логах немає. Винуватець не виконує запитів зовсім: це відкрита транзакція в консолі чи завислий воркер.
Натомість: PostgreSQL: pg_stat_activity, стан idle in transaction і найстаріший xact_start. MySQL: information_schema.INNODB_TRX за trx_started.
- MVCC
- Multiversion concurrency control. База тримає кілька версій рядка, і кожна транзакція бачить ту, що відповідає її знімку. Так працюють PostgreSQL, InnoDB, Oracle, SQLite у режимі WAL на рівні всієї бази.
- Знімок
- Список транзакцій, чиї зміни видно. Версія видима, якщо її створила закомічена до знімка транзакція і її ще не видалила така сама. На READ COMMITTED кожен оператор бере свій знімок, на REPEATABLE READ один діє до кінця транзакції.
- xmin і xmax
- Приховані колонки кожного рядка в PostgreSQL. xmin це номер транзакції, яка створила версію, xmax номер тієї, що її видалила чи замінила, або 0. Їх можна прочитати звичайним SELECT xmin, xmax, ctid FROM таблиця.
- Мертва версія
- Версія рядка, яку вже не бачить жодна транзакція. Місце вона займає, поки VACUUM не позначить його вільним. Лічильник n_dead_tup у pg_stat_user_tables.
- Undo log
- Журнал InnoDB зі старими значеннями змінених рядків. Потрібен для ROLLBACK і для читання старих версій. У MySQL 8.0 лежить в окремих undo tablespaces, undo_001 і undo_002.
- Read view
- Знімок InnoDB: які транзакції були активні в момент його створення. На REPEATABLE READ створюється першим читанням транзакції, на READ COMMITTED заново для кожного оператора.
Питання зі співбесід
UPDATE не перезаписує рядок для всіх, а створює нову версію. Поки транзакція не закомітила, інші читають попередню закомічену версію. У PostgreSQL вона лежить у таблиці поруч з новою, в InnoDB відновлюється з undo log. Чекати доводиться лише іншому записувачу того самого рядка.
Версія, яку замінив UPDATE або видалив DELETE і яку більше не бачить жоден знімок. VACUUM прибирає лише версії, видалені транзакціями, старшими за найстаріший активний знімок або незавершену транзакцію в базі. Тому одна відкрита транзакція може тримати мільйони мертвих версій, і VACUUM VERBOSE покаже їх як dead but not yet removable.
Ні. UPDATE створив нову версію кожного рядка, і на якийсь час у таблиці були обидві. Звичайний VACUUM позначає місце мертвих версій вільним для нових рядків, але файл не зменшує, хіба що порожні сторінки в самому кінці. Повернути місце системі можна через VACUUM FULL, який переписує таблицю під повним блокуванням, або pg_repack.
В обох базах вона не дає прибрати старі версії, але болить різне. У PostgreSQL мертві версії лишаються в таблицях та індексах: ростуть розміри, сканування читають більше сторінок, autovacuum працює вхолосту. В InnoDB таблиця не росте, зате росте undo і History list length, а запити зі старим read view розмотують довгі ланцюжки версій. Важливий і рівень: на READ COMMITTED транзакція, яка нічого не записала, між операторами знімка не тримає. Шукати винуватця треба в pg_stat_activity (idle in transaction, backend_xmin) і в information_schema.INNODB_TRX.