Какие знаешь уровни изоляции в SQL?

Уровни изоляции в SQL: ACID и транзакции

Уровни изоляции определяют степень визуальной изоляции параллельных транзакций в базе данных. Они регулируют, какие изменения одной транзакции видны другим и защищают от различных аномалий.

Четыре стандартных уровня (ISO/IEC 9075)

1. READ UNCOMMITTED (Грязное чтение)

Наименьшая изоляция, максимальная производительность.

• Транзакция может читать незакоммиченные изменения других транзакций
• Максимально быстро, но небезопасно
• Возможны аномалии: грязное чтение, phantom reads

# Пример проблемы
# Транзакция A: BEGIN; UPDATE balance SET amount = 100 WHERE id = 1;
# Транзакция B (READ UNCOMMITTED): SELECT amount FROM balance WHERE id = 1; -- вернёт 100
# Транзакция A: ROLLBACK; -- изменения откачены!
# Теперь B видит данные, которых не существует

2. READ COMMITTED (Чтение закоммиченных данных)

По умолчанию в большинстве СУБД.

• Видны только закоммиченные данные
• Защита от грязного чтения
• Возможны: non-repeatable read, phantom reads

# Пример проблемы (non-repeatable read)
# Транзакция A: SELECT balance FROM account WHERE id = 1; -- 100
# Транзакция B: UPDATE account SET balance = 200 WHERE id = 1; COMMIT;
# Транзакция A: SELECT balance FROM account WHERE id = 1; -- 200 (разные значения!)

3. REPEATABLE READ (Повторяемое чтение)

Стандарт MySQL InnoDB.

• Одна транзакция видит согласованный снимок данных на момент начала
• Новые строки, вставленные другими транзакциями, могут быть видны (phantom reads)
• Защита от: грязного чтения, non-repeatable read

# Пример (repeatable read работает корректно)
# Транзакция A: SELECT balance FROM account WHERE id = 1; -- 100
# Транзакция B: UPDATE account SET balance = 200 WHERE id = 1; COMMIT;
# Транзакция A: SELECT balance FROM account WHERE id = 1; -- всё ещё 100 (MVCC)

# Но phantom read может быть
# Транзакция A: SELECT COUNT(*) FROM orders WHERE status = "active"; -- 5
# Транзакция B: INSERT INTO orders VALUES (...); COMMIT;
# Транзакция A: SELECT COUNT(*) FROM orders WHERE status = "active"; -- 6 (новая строка!)

4. SERIALIZABLE (Сериализуемость)

Максимальная изоляция, минимальная производительность.

• Транзакции выполняются как последовательно одна за другой
• Полная защита от всех аномалий
• Используется редко из-за блокировок

# На уровне SERIALIZABLE весь конфликт разрешается в пользу одной транзакции
# Одна транзакция будет заблокирована/откачена

Таблица аномалий

*MySQL InnoDB может предотвратить phantom reads благодаря gap locks

Практический пример на SQLAlchemy/PostgreSQL

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

engine = create_engine("postgresql://user:pass@localhost/db")

# READ COMMITTED (по умолчанию)
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL READ COMMITTED"))
    result = conn.execute(text("SELECT balance FROM account WHERE id = 1"))

# REPEATABLE READ
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"))
    result = conn.execute(text("SELECT balance FROM account WHERE id = 1"))

# SERIALIZABLE
with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"))
    result = conn.execute(text("SELECT balance FROM account WHERE id = 1"))

Выбор уровня в зависимости от сценария

• READ UNCOMMITTED — критически быстрые чтения, где неточность приемлема (очень редко)
• READ COMMITTED — стандартный выбор для большинства приложений
• REPEATABLE READ — когда нужно несколько согласованных чтений в одной транзакции
• SERIALIZABLE — финансовые операции, где невозможны никакие аномалии

В production-среде выбор уровня критичен для баланса между безопасностью данных и производительностью.