Кратко — определения, конкретные SQL‑сценарии с шагами и как уровни изоляции влияют на появление аномалий плюс влияние на производительность.
Определения
- Dirty read — транзакция читает данные, изменённые другой транзакцией, но ещё не зафиксированные; если та откатит, прочитанные данные неверны.
- Non‑repeatable read — транзакция читает одни и те же строки дважды и получает разные значения из‑за коммита другой транзакции между чтениями.
- Phantom — транзакция выполняет один запрос (например, COUNT или SELECT WHERE), затем другая транзакция вставляет/удаляет строки, и повторный запрос в первой транзакции возвращает другой набор строк (появились «фантомы»).
Примеры (таблица и начальные данные)
```
CREATE TABLE accounts(id INT PRIMARY KEY, balance INT);
INSERT INTO accounts VALUES (1, 1000);
```
Для удобства: обозначим транзакции $T_1$ и $T_2$. Номера шагов — $1$, $2$, ...
1) Dirty read
Шаги:
- $T_1$:
BEGIN;
UPDATE accounts SET balance = $900$ WHERE id = 1; -- ещё не COMMIT
- $T_2$ (с уровнем READ UNCOMMITTED):
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1; -- читает $900$ (грязное чтение)
- $T_1$ делает ROLLBACK (откат) → теперь значение снова $1000$. $T_2$ прочитал неверное значение.
Вывод: dirty read возможен при READ UNCOMMITTED. При READ COMMITTED, REPEATABLE READ, SERIALIZABLE грязные чтения запрещены (чтение увидит только закоммиченные значения или снапшот).
2) Non‑repeatable read
Шаги:
- Изначально balance = $1000$.
- $T_1$:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- читает $1000$ - $T_2$:
BEGIN;
UPDATE accounts SET balance = $800$ WHERE id = 1;
COMMIT;
- $T_1$:
SELECT balance FROM accounts WHERE id = 1; -- теперь читает $800$ — ненаповторимое чтение
COMMIT;
Вывод: non‑repeatable read возможен в READ UNCOMMITTED и READ COMMITTED. В REPEATABLE READ и SERIALIZABLE (стандартно) первая транзакция увидит неизменный снимок и второй SELECT вернёт то же значение, что и первый (недопустимо изменение для $T_1$).
3) Phantom
Создадим таблицу orders:
```
CREATE TABLE orders(id SERIAL PRIMARY KEY, price INT);
INSERT INTO orders (price) VALUES (150), (200), (250); -- 3 строки >100
```
Шаги:
- $T_1$:
BEGIN;
SELECT COUNT(*) FROM orders WHERE price > 100; -- возвращает $3$ - $T_2$:
BEGIN;
INSERT INTO orders(price) VALUES (300);
COMMIT;
- $T_1$:
SELECT COUNT(*) FROM orders WHERE price > 100; -- возвращает $4$: появился фантом
COMMIT;
Вывод: стандартный REPEATABLE READ предотвращает non‑repeatable reads, но может допускать фантомы (в стандарте). SERIALIZABLE предотвращает и фантомы (гарантирует вид как будто транзакции выполнялись последовательно).
Сводка по уровням (стандарт SQL, общая картинка)
- READ UNCOMMITTED:
- dirty: да
- non‑repeatable: да
- phantom: да
- производительность: максимальная параллельность, минимальные блокировки, наихудшая согласованность.
- READ COMMITTED:
- dirty: нет
- non‑repeatable: да
- phantom: да
- производительность: хорошая, частые короткие блокировки/снапшоты на оператор, баланс скорость/консистентность.
- REPEATABLE READ (стандарт):
- dirty: нет
- non‑repeatable: нет
- phantom: да (в стандарте)
- производительность: больше блокировок/контенция, меньшая пропускная способность по сравнению с READ COMMITTED.
- SERIALIZABLE:
- dirty: нет
- non‑repeatable: нет
- phantom: нет
- производительность: наихудшая при высокой конкуренции — возможны конфликты и откаты транзакций, требует сериализации или блокировок.
Важно про реализации (кратко):
- Реальные СУБД используют разные механизмы: MVCC (PostgreSQL, Oracle, InnoDB) даёт «снапшотную» видимость. Это меняет поведение:
- В PostgreSQL REPEATABLE READ реализован как снапшот транзакции (предотвращает non‑repeatable reads и часто фантомы на уровне видимости, хотя полная сериализация достигается уровнем SERIALIZABLE с SSI).
- InnoDB (MySQL) использует MVCC + gap/next‑key locks: REPEATABLE READ часто предотвращает фантомы практически, но детали зависят от запросов и индексов.
Поэтому точное поведение лучше проверять в документации конкретной СУБД.
Производительность — практические замечания
- Чем выше уровень изоляции, тем больше блокировок или конфликтов версий → меньше параллелизма и пропускная способность.
- MVCC снижает блокировки для чтения, но увеличивает использование диска/памяти (версионирование), возможны «write‑write» конфликты.
- SERIALIZABLE часто требует повторного исполнения транзакций при конфликте (cost of retries).
Рекомендация
- Выбирать уровень по требованию приложения: для OLTP часто READ COMMITTED; если нужна стабильность чтений внутри транзакции — REPEATABLE READ; для строгой корреляции и отсутствия фантомов — SERIALIZABLE (соглашаясь на снижение производительности). Учитывайте особенности СУБД (MVCC vs блочные блокировки).
Если нужно, могу привести те же примеры для конкретной СУБД (PostgreSQL, MySQL, SQL Server) и показать точное поведение на практике.