---

🟢 Junior Level

У PostgreSQL рівнем ізоляції транзакцій за замовчуванням є Read Committed.

Суть у 30 секундах

На рівні Read Committed транзакція гарантовано захищена від «брудного читання» (Dirty Read): будь-який запит бачить лише ті зміни, які були успішно зафіксовані (COMMITTED) іншими транзакціями до початку виконання поточного оператора SQL.

Головна архітектурна особливість PostgreSQL на цьому рівні:

Кожен окремий SQL-запит (SELECT, UPDATE, DELETE) усередині однієї і тієї ж транзакції отримує свій власний, свіжий знімок даних (Statement-level Snapshot)!

Якщо всередині однієї відкритої транзакції виконати SELECT balance FROM accounts WHERE id = 1, зачекати пару секунд і повторити точно такий самий SELECT, то другий запит побачить зміни, зафіксовані паралельними транзакціями за цей час.

Транзакція A (Read Committed)          Паралельна транзакція B
----------------------------          -------------------------
BEGIN;
SELECT count(*) FROM orders; -> 100
                                      BEGIN;
                                      INSERT INTO orders ...;
                                      COMMIT; -- Зафіксовано!
SELECT count(*) FROM orders; -> 101   -- Побачили новий рядок!
COMMIT;

Які аномалії можливі на дефолтному рівні в PostgreSQL

Аномалія за стандартом ANSI SQL	Статус у PostgreSQL (Read Committed)	Чому так відбувається
Брудне читання (Dirty Read)	❌ Неможливо	PostgreSQL перевіряє біти коміту в заголовку рядка; незафіксовані кортежі не видно
Неповторюване читання (Non-Repeatable Read)	⚠️ Можливо	Повторний SELECT отримує новий знімок даних і бачить свіжі зміни рядків
Фантомне читання (Phantom Read)	⚠️ Можливо	Новий знімок даних бачить додані або видалені рядки в діапазоні
Втрачене оновлення (Lost Update)	⚠️ Можливо	При шаблоні «прочитав у пам’ять Java -> змінив -> записав» (Read-Modify-Write)

Практичний приклад на Java та Spring Boot

За замовчуванням Spring-анотація @Transactional використовує рівень ізоляції підлеглої СУБД (Isolation.DEFAULT транслюється в Read Committed для PostgreSQL):

package com.example.service;

import com.example.entity.Account;
import com.example.repository.AccountRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;

@Service
@RequiredArgsConstructor
public class AccountService {

    private final AccountRepository accountRepository;

    @Transactional // За замовчуванням: Read Committed у PostgreSQL
    public void printBalanceTwice(Long accountId) throws InterruptedException {
        // Перший SELECT: створює Snapshot #1
        Account firstRead = accountRepository.findById(accountId).orElseThrow();
        System.out.println("Перше читання балансу: " + firstRead.getBalance());

        // Імітація тривалої бізнес-логіки (2 секунди)
        // У цей момент паралельний потік виконує UPDATE accounts SET balance = 5000 WHERE id = 1
        Thread.sleep(2000);

        // Другий SELECT (у тій самій транзакції!): створює Snapshot #2
        // УВАГА: EntityManager кешує сутність у Persistence Context (1st level cache).
        // Щоб побачити фізичний запит до БД, робимо refresh або нативний запит:
        Account secondRead = accountRepository.findByIdFresh(accountId);
        System.out.println("Друге читання балансу: " + secondRead.getBalance());
        // secondRead.getBalance() може відрізнятися від firstRead.getBalance()!
    }
}

⚠️ Важливе зауваження про JPA/Hibernate: Первинний кеш Hibernate (EntityManager) за замовчуванням повертає вже завантажену в сесію сутність. Тому в чистому Spring Data JPA ефект неповторюваного читання може бути замаскований кешем L1. Проте при нативних запитах (@Query(nativeQuery = true)), виклику entityManager.refresh(...) або прямому читанні через JdbcTemplate кожен SELECT звертається до нового знімка PostgreSQL.

---

🟡 Middle Level

Архітектура MVCC у PostgreSQL: чому саме Read Committed

PostgreSQL реалізує ізоляцію транзакцій через MVCC (Multi-Version Concurrency Control). На відміну від СУБД із блокувальником (де читачі блокують письменників, а письменники — читачів), у PostgreSQL:

Читачі ніколи не блокують письменників, а письменники ніколи не блокують читачів.

Кожна зміна рядка (UPDATE) або видалення (DELETE) фізично не перезаписує дані на диску «за місцем», а створює нову версію рядка (tuple):

• INSERT: створює кортеж з ідентифікатором поточної транзакції в системному полі xmin та xmax = 0.
• DELETE: не стирає кортеж, а записує ID транзакції, що видаляє, у системне поле xmax.
• UPDATE: являє собою атомарну зв’язку DELETE старої версії (встановлення xmax) + INSERT нової версії (новий кортеж із новим xmin і зв’язкою через вказівник ctid).

Сторінка таблиці (Heap Page):
+---------------------------------------------------------------------------------+
| Версія 1: [xmin: 100, xmax: 105, balance: 1000] -> (Мертвий кортеж після коміту 105)
| Версія 2: [xmin: 105, xmax: 0,   balance: 1500] -> (Живий кортеж для знімка >= 105)
+---------------------------------------------------------------------------------+

Життєвий цикл знімка даних (Snapshot) у Read Committed

У ядрі PostgreSQL знімок описується структурою SnapshotData:

• xmin: найменший XID (Transaction ID), який був активний на момент побудови знімка. Усі транзакції з id < xmin гарантовано завершені.
• xmax: перший ще не присвоєний XID. Усі транзакції з id >= xmax почалися пізніше і є невидимими.
• xip[]: масив активних (in-progress) транзакцій в інтервалі між xmin та xmax.

Різниця між рівнями ізоляції в PostgreSQL:

Read Committed:
  Запит 1 (SELECT) -----> GetTransactionSnapshot() -> Snapshot #1 створюється
  Запит 2 (SELECT) -----> GetTransactionSnapshot() -> Snapshot #2 створюється заново!

Repeatable Read:
  Запит 1 (SELECT) -----> GetTransactionSnapshot() -> Snapshot фіксується на всю транзакцію
  Запит 2 (SELECT) -----> Перевикористовує раніше створений Snapshot!

У PostgreSQL на рівні Read Committed функція GetTransactionSnapshot() викликається перед кожним окремим SQL-виразом. Це забезпечує мінімальне утримання старих знімків і максимальну продуктивність, але відкриває вікно для аномалій Non-Repeatable Read та Phantom Read.

Механізм EvalPlanQual (EPQ) при конкурентних UPDATE

Що відбувається на рівні Read Committed, коли дві транзакції одночасно намагаються оновити один і той самий рядок?

-- Таблиця accounts: id = 1, balance = 1000, is_active = true

-- Транзакція T1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND is_active = true;
-- T1 захоплює ексклюзивне блокування рядка (RowExclusiveLock / XMAX)

-- Транзакція T2 (паралельно):
BEGIN;
UPDATE accounts SET is_active = false WHERE id = 1 AND is_active = true;
-- T2 виявляє, що рядок заблокований T1, і ПРИЗУПИНЯЄТЬСЯ (чекає завершення T1)

Коли T1 виконує COMMIT:

1. T2 розблоковується.
2. Оскільки T2 працює на рівні Read Committed, вона не падає з помилкою серіалізації!
3. Вмикається механізм EvalPlanQual (EPQ): T2 зчитує найсвіжішу зафіксовану версію рядка (створену T1) і повторно обчислює предикат WHERE.
4. У нашому прикладі: для T2 умова is_active = true усе ще істинна (T1 змінив тільки баланс). Тому T2 успішно застосовує свій UPDATE, встановивши is_active = false.
5. Якби T1 змінив is_active = false, то після повторної перевірки предикат для T2 повернув би false, і рядок був би пропущений без оновлення!

---

🔴 Senior Level

Глибокий аналіз ядра PostgreSQL: Commit Log та Hint Bits

Кожного разу під час перевірки видимості кортежу (HeapTupleSatisfiesVisibility) перевіряти статус транзакції через центральний менеджер транзакцій (ProcArrayLock) було б катастрофічно дорого для продуктивності.

PostgreSQL вирішує це трьома шарами оптимізації:

1. CLOG (pg_xact): Спеціальний кільцевий буфер і файли на диску, де на кожну транзакцію виділяється рівно 2 біти:
   • 00 — TRANSACTION_STATUS_IN_PROGRESS
   • 01 — TRANSACTION_STATUS_COMMITTED
   • 10 — TRANSACTION_STATUS_ABORTED
   • 11 — TRANSACTION_STATUS_SUB_COMMITTED
2. Hint Bits у заголовку кортежу (t_infomask):
   • Під час першої перевірки кортежу транзакція звертається до pg_xact, визначає статус творця (xmin), а потім безпосередньо мутує заголовок кортежу на сторінці пам’яті, виставляючи біти:
     • HEAP_XMIN_COMMITTED
     • HEAP_XMIN_INVALID (транзакція відкотилася)
     • HEAP_XMAX_COMMITTED
   • Усі наступні читання іншими транзакціями просто перевіряють ці біти без звернення до pg_xact і без захоплення спінлоків!

Структура заголовка HeapTuple (HeapTupleHeaderData):
+-------------------------------------------------------------------------------+
| t_xmin (32 bit) | t_xmax (32 bit) | t_cid / t_xvac (32 bit) | t_ctid (6 bytes) |
| t_infomask2     | t_infomask (16 bit: Hint Bits!)          | t_hoff (offset)   |
+-------------------------------------------------------------------------------+

Вплив Read Committed на VACUUM, Bloat та Wraparound

Хоча на рівні Read Committed кожен запит отримує свій знімок, відкрита та бездіяльна транзакція (idle in transaction) або довгий SELECT може завдати критичної шкоди здоров’ю кластера:

1. Утримання OldestXmin: Поки виконується довгий аналітичний запит, його знімок утримує горизонт очищення OldestXmin.
2. Блокування VACUUM: Процес autovacuum не має права видаляти мертві кортежі (dead tuples), чий xmax >= OldestXmin.
3. Table & Index Bloat: Усі паралельні UPDATE і DELETE накопичують мертві версії рядків на диску. Таблиця розміром 5 ГБ за пару годин може роздутися до 50 ГБ.
4. Transaction ID Wraparound: Якщо горизонт транзакцій наблизиться до $2^{31}$ транзакцій через завислу сесію, PostgreSQL перейде в аварійний режим read-only для запобігання втраті даних.

-- Моніторинг небезпечних довгих транзакцій, що блокують autovacuum:
SELECT pid, 
       usename, 
       state, 
       now() - xact_start AS xact_duration, 
       now() - state_change AS state_duration, 
       query 
FROM pg_stat_activity 
WHERE state IN ('active', 'idle in transaction') 
  AND xact_start < now() - INTERVAL '5 minutes'
ORDER BY xact_start ASC;

Захист від Lost Update на рівні Read Committed в Highload

Оскільки Read Committed не захищає від втрати оновлень при читанні-модифікації-записі в коді застосунку, Senior Engineer обирає одну з трьох стратегій:

package com.example.service;

import com.example.entity.Wallet;
import com.example.repository.WalletRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;

@Service
@RequiredArgsConstructor
public class HighloadWalletService {

    private final WalletRepository walletRepository;

    // Стратегія 1: Атомарний UPDATE в БД (Zero Application Contention)
    // Наймасштабованіший підхід: лягає на механізм блокування рядків у самому PG
    @Transactional
    public void creditBalanceAtomic(Long walletId, BigDecimal amount) {
        int updatedRows = walletRepository.incrementBalanceAtomic(walletId, amount);
        if (updatedRows == 0) {
            throw new IllegalStateException("Гаманець не знайдено або заблоковано");
        }
    }

    // Стратегія 2: Песимістичне блокування SELECT ... FOR UPDATE
    // Надійно для складних бізнес-інваріантів, але створює черги при високому RPS
    @Transactional
    public void debitBalancePessimistic(Long walletId, BigDecimal amount) {
        // Виконує: SELECT * FROM wallets WHERE id = ? FOR UPDATE
        Wallet wallet = walletRepository.findByIdForUpdate(walletId)
                .orElseThrow(() -> new IllegalArgumentException("Wallet not found"));

        if (wallet.getBalance().compareTo(amount) < 0) {
            throw new IllegalStateException("Недостатньо коштів");
        }
        wallet.setBalance(wallet.getBalance().subtract(amount));
        // При коміті генерується UPDATE, лок звільняється
    }
}

Репозиторій з анотаціями Spring Data:

package com.example.repository;

import com.example.entity.Wallet;
import jakarta.persistence.LockModeType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.math.BigDecimal;
import java.util.Optional;

public interface WalletRepository extends JpaRepository<Wallet, Long> {

    @Modifying
    @Query("UPDATE Wallet w SET w.balance = w.balance + :amount WHERE w.id = :id")
    int incrementBalanceAtomic(@Param("id") Long id, @Param("amount") BigDecimal amount);

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT w FROM Wallet w WHERE w.id = :id")
    Optional<Wallet> findByIdForUpdate(@Param("id") Long id);
}

---

4 Tricky Questions

1. Що станеться, якщо в PostgreSQL виконати команду SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?

Відповідь: Запит виконається успішно без синтаксичних помилок, проте рівень ізоляції буде автоматично і неявно підвищено до Read Committed! В архітектурі ядра PostgreSQL механізм читання «брудних» (незафіксованих) даних фізично не реалізований:

• Алгоритми видимості кортежів (HeapTupleSatisfiesVisibility) перевіряють статус xmin через статус транзакції. Якщо транзакція не завершена (TRANSACTION_STATUS_IN_PROGRESS), кортеж вважається невидимим для всіх інших транзакцій.
• Щоб зробити Dirty Read можливим, розробникам довелося б навмисно послаблювати перевірку коміту, що зруйнувало б гарантії цілісності сторінок, індексів та TOAST-сховища. Тому PostgreSQL підтримує синтаксис READ UNCOMMITTED виключно заради сумісності зі стандартом ANSI SQL, маплячи його 1-в-1 на READ COMMITTED.

---

2. Як працює механізм EvalPlanQual (EPQ) при конкурентному UPDATE ... WHERE, і чи може рядок «зникнути» з оновлення?

Відповідь: Так, рядок може бути пропущений без оновлення. Коли транзакція T2 намагається оновити рядок, заблокований транзакцією T1:

1. T2 засинає в очікуванні звільнення блокування рядка (RowExclusiveLock у T1).
2. Після коміту T1 транзакція T2 прокидається і отримує найсвіжішу версію рядка за вказівником t_ctid.
3. Вмикається механізм ядра EvalPlanQual: T2 підставляє поля оновленого рядка у вихідний вираз WHERE.
4. Якщо умова WHERE все ще істинна — T2 застосовує свій UPDATE поверх нової версії.
5. Якщо умови WHERE стали хибними (наприклад, T1 змінив статус рядка WHERE status = 'PENDING' на status = 'PROCESSING'), T2 бачить, що кортеж більше не відповідає фільтру, і пропускає його. Кількість оновлених рядків executeUpdate() дорівнюватиме 0.

---

3. Чому в PostgreSQL на рівні Read Committed запит SELECT ... FOR UPDATE може повернути рядок, що не відповідає початковій WHERE-умові у складних JOIN?

Відповідь: Це відома тонкість реалізації EvalPlanQual під час з’єднань (JOIN). Якщо запит з’єднує таблиці:

SELECT * FROM parent p JOIN child c ON p.id = c.parent_id 
WHERE c.status = 'ACTIVE' FOR UPDATE OF p;

1. Оптимізатор формує знімок на момент початку виконання оператора.
2. Рядок таблиці child на момент знімка був 'ACTIVE', і PostgreSQL знаходить відповідну пару рядків.
3. Під час спроби заблокувати рядок таблиці parent з’ясовується, що паралельна транзакція щойно оновила parent і вже зафіксувалася.
4. Механізм EPQ перезапитує свіжу версію parent. Але в старих версіях PostgreSQL переперевірка з’єднання для всього плану запиту могла повернути кортеж батька, навіть якщо паралельна транзакція паралельно деактивувала дочірній запис child. Починаючи з версії PostgreSQL 12+, алгоритм EvalPlanQual був суттєво доопрацьований, проте в розподілених або складних складених планах розробникам наполегливо рекомендується блокувати рядки обох таблиць (FOR UPDATE OF p, c).

---

4. У чому різниця між Read Committed у PostgreSQL та Read Committed в Oracle або MS SQL Server?

Відповідь:

• PostgreSQL vs Oracle: Архітектурно дуже близькі (обидві СУБД використовують MVCC без блокувань читання). Проте Oracle зберігає старі версії рядків не в табличному просторі (heap pages), а в спеціалізованих сегментах відкату (Undo Tablespace). У Postgres старі версії лежать прямо в сторінках таблиці, створюючи необхідність процесу VACUUM для очищення dead tuples. В Oracle замість table bloat виникає помилка ORA-01555: snapshot too old, якщо сегменти Undo переповнилися.
• PostgreSQL vs MS SQL Server: У класичному MS SQL Server рівень Read Committed заснований на короткочасних розділюваних блокуваннях читання (Shared S-locks): читачі короткочасно блокують письменників, а пишуча транзакція утримує X-блокування до кінця транзакції, наглухо блокуючи читачів. Щоб отримати поведінку, аналогічну PostgreSQL, в MS SQL Server необхідно явно вмикати режим READ_COMMITTED_SNAPSHOT (RCSI), який використовує базу даних tempdb для версіонування рядків.

---

🎯 Шпаргалка для інтерв’ю

30-секундна відповідь

«У PostgreSQL рівнем ізоляції за замовчуванням є Read Committed. Він реалізований через механізм MVCC: читачі не блокують письменників, а письменники не блокують читачів. Головна особливість: кожен окремий SQL-запит отримує новий знімок даних (per-statement snapshot). Він захищає від Dirty Read, але допускає Non-Repeatable Read та Phantom Read. Конкурентні оновлення одного рядка вирішуються через механізм EvalPlanQual (EPQ): транзакція чекає коміту конкурента, після чого повторно перевіряє умову WHERE на свіжій версії рядка».

Чек-лист ключових понять

1. Дефолт: Read Committed.
2. Знімок (Snapshot): Створюється заново для кожного оператора (GetTransactionSnapshot()).
3. Будова кортежу: xmin (транзакція-творець), xmax (транзакція-удалитель), t_infomask (Hint Bits: COMMITTED/INVALID).
4. EvalPlanQual (EPQ): Механізм повторної перевірки предиката WHERE після очікування блокування рядка.
5. READ UNCOMMITTED: У PostgreSQL синтаксично приймається, але неявно підвищується до READ COMMITTED.
6. Небезпека: Завислі транзакції (idle in transaction) утримують OldestXmin, блокуючи роботу autovacuum і призводячи до розпухання таблиць (table bloat).

Червоні прапорці (чого ні в якому разі не можна говорити)

• ❌ «У PostgreSQL дефолтний рівень Repeatable Read» (Це дефолт у MySQL InnoDB, у PostgreSQL дефолт — Read Committed).
• ❌ «PostgreSQL підтримує Read Uncommitted і дозволяє читати брудні дані» (Брудне читання в Postgres неможливе в принципі, рівень підвищується до Read Committed).
• ❌ «Якщо поставити @Transactional, то два однакових SELECT гарантовано повернуть одне й те саме» (На Read Committed кожен SELECT бачить зміни, зафіксовані між запитами, якщо тільки результат не взятий із L1-кешу Hibernate).
• ❌ «Read Committed захищає від втрати оновлень (Lost Update)» (Не захищає при логіці Read-Modify-Write на рівні сервісу, потрібен атомарний SQL або блокування).

Пов’язані теми

• Що таке Read Committed
• Що таке Repeatable Read
• Який рівень ізоляції за замовчуванням в MySQL
• Що таке втрачене оновлення (Lost Update)