Що таке Repeatable Read

На відміну від Read Committed, де знімок видимості даних формується заново перед кожним SQL-виразом, на рівні Repeatable Read:

🟢 Junior Level

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

Ключові властивості:

Захист від брудного читання (Dirty Read): Незафіксовані дані інших транзакцій ніколи не видно.
Захист від неповторюваного читання (Non-Repeatable Read): Значення одного разу прочитаних рядків «заморожені» для поточної транзакції.
Фантомне читання (Phantom Read): За стандартом ANSI SQL може допускатися, проте в MySQL (InnoDB) йому запобігають завдяки спеціальним блокуванням діапазонів.

Час    Транзакція А (Repeatable Read)          Транзакція Б (Зовнішнє оновлення)
  |    ------------------------------          ---------------------------------
  |  T1: BEGIN;
  |  T2: SELECT balance FROM accounts          -- Баланс: 1 000 грн.
  |      WHERE id = 1;
  |  T3:                                       BEGIN;
  |  T4:                                       UPDATE accounts SET balance = 500
  |                                            WHERE id = 1;
  |                                            COMMIT; -- Успішно зафіксовано!
  |  T5: SELECT balance FROM accounts          -- Баланс: 1 000 грн.!
  |      WHERE id = 1;                         -- Т1 бачить колишнє значення зі знімка!
  v  T6: COMMIT;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

@Service
public class BillingReportService {

    // Гарантує цілісність фінансового звіту під час тривалих розрахунків
    @Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
    public MonthlyReport generateReport(Long companyId) {
        // Усі наступні SELECT бачать знімок бази на момент старту першого запиту
        BigDecimal totalDebit = ledgerRepository.sumDebit(companyId);
        BigDecimal totalCredit = ledgerRepository.sumCredit(companyId);
        return new MonthlyReport(totalDebit, totalCredit);
    }
}

Аналогія: Repeatable Read нагадує миттєвий знімок (фотографію) документа на телефон у момент входу в архів. Як би співробітники архіву не переписували оригінал документа на полиці, на вашій фотографії текст залишиться незмінним.

🟡 Middle Level

Механізм реалізації через MVCC Snapshot (Знімок транзакції)

На відміну від Read Committed, де знімок видимості даних формується заново перед кожним SQL-виразом, на рівні Repeatable Read:

Знімок створюється рівно один раз — у момент виконання першого запиту на читання в транзакції (Transaction-Level Snapshot).
Знімок містить метадані:
- xmin: ID найстарішої транзакції, яка ще була активна на момент створення знімка.
- xmax: ID першої ще не призначеної транзакції.
- xip_list: список ID транзакцій, що виконувалися паралельно в момент старту.
Усі наступні SELECT використовують один і той самий знімок, відсікаючи будь-які зміни, закомічені пізніше моменту фіксації знімка.

Кардинальні відмінності: MySQL (InnoDB) проти PostgreSQL

Критерій	MySQL (InnoDB)	PostgreSQL
Статус рівня	Дефолтний рівень за замовчуванням	Опціональний (дефолт — Read Committed)
Фантомні читання (Phantom Reads)	Захищені для `SELECT` та `SELECT FOR UPDATE`	Захищені для звичайних `SELECT`
Механізм захисту від фантомів	Next-Key Locks (Record Lock + Gap Lock)	MVCC Snapshot (без блокувань діапазонів)
Конфлікт паралельних `UPDATE`	Блокується до комміту першої транзакції	Викидає виняток серіалізації 40001
Deadlock ймовірність	Висока (через Gap Locks)	Низька

Захист від Lost Update у PostgreSQL

У PostgreSQL на рівні Repeatable Read база даних на рівні рушія захищає від аномалії втрати оновлення (Lost Update):

-- Транзакція T1:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 100

-- Транзакція T2 (паралельно):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = 80 WHERE id = 1;
COMMIT;

-- Транзакція T1 намагається оновити той самий рядок:
UPDATE accounts SET balance = 90 WHERE id = 1;
-- ПОМИЛКА: ERROR: could not serialize access due to concurrent update (SQLSTATE 40001)

Оскільки рядок був змінений транзакцією $T_2$ уже після створення знімка $T_1$, PostgreSQL забороняє неузгоджене оновлення і вимагає повного відкату та повтору $T_1$.

🔴 Senior Level

Аномалія Write Skew: чому Repeatable Read — це не Serializable

Рівень Repeatable Read захищає від колізій під час модифікації одного й того самого рядка, але безсилий проти аномалії Write Skew (Перекіс запису), коли інваріант пов’язує кілька різних рядків.

Практичний приклад:

Правило рахунку: Сумарний баланс двох гаманців клієнта (Основний $A$ і Резервний $B$) не повинен бути від’ємним ($A + B \ge 0$). Початково: $A = 100$, $B = 100$ (Сума = 200).

Транзакція T1 (Списання з рахунку A на 150 грн.):
  1. Читає A (100) і B (100). Перевіряє: 100 + 100 >= 150 (Успішно).
  2. UPDATE accounts SET balance = balance - 150 WHERE id = 'A'; (Баланс A = -50)

Транзакція T2 (Паралельне списання з рахунку B на 150 грн.):
  1. Читає A (100) і B (100) за своїм знімком. Перевіряє: 100 + 100 >= 150 (Успішно).
  2. UPDATE accounts SET balance = balance - 150 WHERE id = 'B'; (Баланс B = -50)

Обидві транзакції успішно фіксуються (T1 змінила рядок A, T2 змінила рядок B — фізичного конфлікту рядків немає)!
ПІДСУМОК: A = -50, B = -50. Сумарний баланс = -100! Інваріант зруйновано!

Рішення: перехід на рівень SERIALIZABLE або явне блокування через SELECT ... FOR UPDATE.

Проблема Table Bloat та заморожування VACUUM у PostgreSQL

Якщо транзакція на рівні Repeatable Read залишається відкритою тривалий час (наприклад, фоновий аналітичний звіт виконується 40 хвилин):

Поле xmin цієї транзакції утримує мінімальний горизонт видимості (xmin horizon).
Процес autovacuum не має права очищати або перевикористовувати «мертві» рядки (dead tuples), видалені будь-якими іншими швидкими транзакціями, якщо ці рядки були видалені після старту довгої RR-транзакції.
Результат: Таблиці та індекси в СУБД починають стрімко роздуватися (Table Bloat), переповнюючи дисковий простір і сповільнюючи кеш оперативної пам’яті (shared_buffers).

4 Tricky Questions

1. Чи може в MySQL InnoDB на рівні `Repeatable Read` виникнути фантомне читання?

Відповідь: Так, існує тонкий граничний сценарій! У MySQL InnoDB чистий SELECT використовує MVCC-знімок і фантомів не бачить. Однак якщо транзакція оновлює рядок, вставлений паралельною транзакцією, виникає фантом:

-- Транзакція T1 (Repeatable Read):
SELECT * FROM users WHERE id = 10; -- Порожньо (рядка немає)

-- Транзакція T2:
INSERT INTO users (id, name) VALUES (10, 'Phantom');
COMMIT;

-- Транзакція T1:
SELECT * FROM users WHERE id = 10; -- Усе ще порожньо (знімок захищає)
-- Але тепер T1 виконує UPDATE для цього рядка:
UPDATE users SET name = 'Caught' WHERE id = 10; -- 1 row affected!
-- І повторний SELECT:
SELECT * FROM users WHERE id = 10; -- РЯДОК З'ЯВИВСЯ! (Фантомне читання)

Чому це сталося: Оператор UPDATE в InnoDB працює як Locking Current Read (поточне блокувальне читання). Він виявляє рядок, оновлює його та проставляє в заголовок рядка ID поточної транзакції $T_1$. А за правилами MVCC транзакція завжди бачить рядки, створені або оновлені нею самою! У результаті наступний SELECT повертає рядок, що щойно «виник з нізвідки».

2. Чому в PostgreSQL під час конфлікту `UPDATE` на рівні `Repeatable Read` викидається виняток, а на `Read Committed` запит виконується штатно?

Відповідь:

На Read Committed: PostgreSQL використовує механізм EvalPlanQual (EPQ). Під час комміту першої транзакції друга транзакція зчитує свіжу версію рядка, заново обчислює умову WHERE та оновлює нову версію.
На Repeatable Read: Знімок даних є незмінним (Immutable Snapshot). Оновлення рядка, закоміченого паралельною транзакцією після старту знімка, означало б порушення інваріанта повторюваності читання (ми модифікуємо те, чого за нашим знімком не існує або що виглядає інакше). Щоб не порушувати цілісність знімка, PostgreSQL забороняє застосування EPQ і примусово перериває операцію з помилкою 40001 (serialization failure).

3. Чому в MySQL на рівні `Repeatable Read` значно частіше виникають взаємні блокування (Deadlocks), ніж на `Read Committed`?

Відповідь: Причина — використання Next-Key Locks (комбінація Record Lock та Gap Lock). На рівні Repeatable Read під час виконання запитів з умовами діапазону або пошуком за непервинним індексом InnoDB блокує не лише самі рядки, а й інтервали (проміжки) між ними (Gap Locks), щоб запобігти вставці фантомів іншими транзакціями. Коли дві паралельні транзакції вставляють дані в сусідні проміжки або перехресно захоплюють діапазони, що перетинаються, їхні блокування проміжків несумісні, що регулярно призводить до Deadlock found when trying to get lock; try restarting transaction.

4. Як правильно налаштувати Spring-застосунок для роботи з методом `@Transactional(isolation = Isolation.REPEATABLE_READ)`?

Відповідь: Оскільки на рівні Repeatable Read у СУБД (PostgreSQL, CockroachDB) конкурентні конфлікти запису штатно призводять до помилок серіалізації (CannotSerializeTransactionException), метод у Spring зобов’язаний бути ідемпотентним і супроводжуватися механізмом повтору (Retry):

@Retryable(
    retryFor = { CannotSerializeTransactionException.class },
    maxAttempts = 3,
    backoff = @Backoff(delay = 50, multiplier = 2.0)
)
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void safeAccountUpdate(Long accountId, BigDecimal amount) {
    Account acc = accountRepository.findById(accountId).orElseThrow();
    acc.setBalance(acc.getBalance().add(amount));
    accountRepository.save(acc);
}

Без @Retryable будь-який конкурентний запис на рівні Repeatable Read викликатиме збої HTTP-запитів користувачів із помилками 500.

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

Сутність Repeatable Read: Гарантує однаковий результат повторних читань одних і тих самих рядків усередині транзакції.
Snapshot-механіка: Знімок видимості даних створюється один раз на початку транзакції (Transaction-level snapshot).
Аномалії:
- Брудне читання (Dirty Read) — захищено.
- Неповторюване читання (Non-Repeatable Read) — захищено.
- Фантоми (Phantom Read) — за стандартом можливі, але в MySQL блокуються через Next-Key Locks.
- Перекіс запису (Write Skew) — не захищений (потрібен Serializable).
Поведінка при конфлікті UPDATE (PostgreSQL): Помилка could not serialize access due to concurrent update (40001). Потрібен обов’язковий механізм @Retryable.
Небезпека довгих транзакцій: Блокують очищення dead tuples у PostgreSQL, призводячи до розростання бази даних (Table Bloat).

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

Твердження, що Repeatable Read захищає від усіх аномалій і дорівнює Serializable (він не захищає від Write Skew).
Використання Repeatable Read для довгих batch-завдань або тривалих аналітичних звітів у PostgreSQL (це заморожує autovacuum і веде до Table Bloat).
Відсутність логіки повтору (Retry) у коді при роботі на Repeatable Read у PostgreSQL.
Твердження, що в MySQL InnoDB на Repeatable Read за жодних умов неможливі фантоми (фантом виникає при оновленні вставленого паралельною транзакцією рядка).