Что знаешь про паттерны изоляции БД?

Паттерны изоляции базы данных: ACID и уровни изоляции

Основная концепция

Изоляция (Isolation) — четвёртый компонент ACID свойств. Это гарантия того, что параллельные транзакции не мешают друг другу и не читают несогласованные данные. Разные системы предлагают разные уровни изоляции в зависимости от их стоимости и требований.

ACID свойства и роль изоляции

# ACID — четыре основных свойства:
acid_properties = {
    "atomicity": "Всё или ничего — транзакция либо полностью выполняется, либо откатывается",
    "consistency": "От одного согласованного состояния к другому",
    "isolation": "Параллельные транзакции не мешают друг другу",
    "durability": "После commit данные сохранены на диск и не потеряны"
}

# Мы фокусимся на Isolation

Проблемы, которые решает изоляция

1. Dirty Read (грязное чтение)

# Транзакция A видит незафиксированные изменения от B

# Транзакция 1 (A)        Транзакция 2 (B)
                          BEGIN
                          UPDATE users SET balance = 900
                          WHERE id = 1
BEGIN
SELECT balance          # Видим 900 (dirty!)
FROM users WHERE id = 1
                          ROLLBACK  # Откатили!
                          
# Проблема: Мы прочитали данные, которые никогда не будут зафиксированы!

2. Non-Repeatable Read (неповторяемое чтение)

# Одна и та же SELECT в одной транзакции дает разные результаты

# Транзакция 1 (A)        Транзакция 2 (B)
BEGIN
SELECT balance = 1000    # Читаем 1000
FROM users WHERE id = 1
                          BEGIN
                          UPDATE users SET balance = 500
                          WHERE id = 1
                          COMMIT
SELECT balance = 500     # Читаем 500 (другой результат!)
FROM users WHERE id = 1
COMMIT

3. Phantom Read (фантомное чтение)

# SELECT возвращает разное количество строк в одной транзакции

# Транзакция 1 (A)        Транзакция 2 (B)
BEGIN
SELECT COUNT(*) = 3      # Читаем 3 записи
FROM users WHERE age > 18
                          INSERT INTO users VALUES (4, 25)
                          COMMIT
SELECT COUNT(*) = 4      # Читаем 4 записи (phantom!)
FROM users WHERE age > 18
COMMIT

4. Lost Update (потерянное обновление)

# Два обновления: второе перезаписывает первое

# Транзакция 1 (A)        Транзакция 2 (B)
BEGIN
READ balance = 1000
                          BEGIN
                          READ balance = 1000
UPDATE SET balance = 1100 # +100
                          UPDATE SET balance = 1050 # +50
COMMIT
                          COMMIT
# Результат: 1050 (потеряли +100 от A)

Четыре уровня изоляции (SQL Standard)

1. READ UNCOMMITTED (самый слабый)

# Позволяет Dirty Reads!
class IsolationLevel:
    READ_UNCOMMITTED = "READ UNCOMMITTED"
    
    # Проблемы:
    problems = {
        "dirty_read": True,              # Можешь читать незафиксированные
        "non_repeatable_read": True,
        "phantom_read": True
    }
    
    # Когда использовать:
    use_cases = [
        "Статистика, которая не обязана быть на 100% точной",
        "Очень высокая нагрузка, где нужна максимальная скорость"
    ]

# SQL:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2. READ COMMITTED (по умолчанию в PostgreSQL)

# Не позволяет Dirty Reads, но позволяет остальные
class ReadCommitted:
    read_uncommitted_possible = False  # ✓ Защита от грязного чтения
    non_repeatable_read_possible = True  # ✗ Можно дважды читать разные значения
    phantom_read_possible = True         # ✗ Можно получить новые строки
    
    benefits = [
        "Хороший баланс между безопасностью и производительностью",
        "По умолчанию в PostgreSQL",
        "Достаточно для большинства приложений"
    ]

# Пример проблемы:
# В одной транзакции: читаем баланс 1000, потом 500 (другой пользователь обновил)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. REPEATABLE READ

# Гарантирует, что повторное чтение вернёт те же значения
class RepeatableRead:
    dirty_read_possible = False
    non_repeatable_read_possible = False  # ✓ Одно значение на всю транзакцию
    phantom_read_possible = True           # ✗ Новые строки могут появиться
    
    how_it_works = """
    PostgreSQL использует MVCC (Multi-Version Concurrency Control).
    Каждая транзакция видит "снимок" данных на момент начала.
    """
    
    benefits = [
        "Хорошая изоляция для операций UPDATE/DELETE",
        "Стандартный уровень в MySQL InnoDB",
        "Предотвращает потерянные обновления"
    ]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. SERIALIZABLE (самый строгий)

# Полная изоляция: как будто транзакции выполняются по одной
class Serializable:
    dirty_read_possible = False
    non_repeatable_read_possible = False
    phantom_read_possible = False  # ✓ Полная защита
    
    how_it_works = """
    PostgreSQL использует Serialization Anomaly Detection (SSI).
    MySQL InnoDB использует lock-based подход.
    Результат: медленнее, но абсолютная безопасность.
    """
    
    tradeoff = {
        "safety": "Максимальная",
        "performance": "Низкая (много блокировок или откатов)",
        "when_to_use": "Критичные финансовые операции"
    }

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Таблица сравнения

isolation_comparison = """
┌──────────────────┬─────────┬──────────────┬────────────┐
│ Уровень          │ Dirty R │ Non-Repeat R │ Phantom R  │
├──────────────────┼─────────┼──────────────┼────────────┤
│ READ UNCOMMITTED │ Да      │ Да           │ Да         │
│ READ COMMITTED   │ Нет     │ Да           │ Да         │
│ REPEATABLE READ  │ Нет     │ Нет          │ Да (MySQL) │
│ SERIALIZABLE     │ Нет     │ Нет          │ Нет        │
└──────────────────┴─────────┴──────────────┴────────────┘
"""

Практические примеры на Python

PostgreSQL (по умолчанию READ COMMITTED):

import psycopg2
from psycopg2 import sql

def transfer_money(from_id, to_id, amount):
    """Безопасный перевод денег"""
    conn = psycopg2.connect("dbname=mydb")
    
    try:
        # Явно устанавливаем уровень изоляции
        conn.set_isolation_level(
            psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
        )
        
        cur = conn.cursor()
        
        # Читаем баланс (внутри транзакции он не изменится)
        cur.execute(
            "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
            (from_id,)
        )
        balance = cur.fetchone()[0]
        
        if balance >= amount:
            # Вычитаем
            cur.execute(
                "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                (amount, from_id)
            )
            # Добавляем
            cur.execute(
                "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                (amount, to_id)
            )
        
        conn.commit()
    except psycopg2.extensions.TransactionRollbackError:
        # Serialization conflict — откатили
        conn.rollback()
        print("Conflict! Retry the transaction")
    finally:
        conn.close()

SELECT FOR UPDATE — явная блокировка:

# Предотвращает Lost Updates
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
# Теперь никто другой не может изменить эту строку до commit

Рекомендации для разработчиков

class BestPractices:
    recommendations = {
        "1. Зависит от задачи": {
            "финансовые_транзакции": "SERIALIZABLE или REPEATABLE READ",
            "статистика": "READ COMMITTED",
            "критичные_операции": "SELECT FOR UPDATE + SERIALIZABLE"
        },
        "2. Оптимизация": {
            "избегай": "Не устанавливай SERIALIZABLE для всего",
            "лучше": "Используй READ COMMITTED + SELECT FOR UPDATE где нужно",
            "масштабирование": "Row-level locks лучше, чем table-level"
        },
        "3. Мониторинг": {
            "PostgreSQL": "pg_stat_activity — смотри долгие транзакции",
            "MySQL": "SHOW PROCESSLIST",
            "обрати_внимание": "На блокировки и deadlocks"
        }
    }

Выводы

1. Изоляция критична для данных, особенно в многопользовательских системах
2. READ COMMITTED — хороший default для большинства приложений
3. REPEATABLE READ/SERIALIZABLE — для критичных операций
4. SELECT FOR UPDATE — явное управление конкурентностью
5. Deadlocks — нужно обрабатывать и retry-ить
6. MVCC (PostgreSQL) — лучше для read-heavy нагрузок
7. Lock-based (MySQL) — проще для понимания, но медленнее