Какой уровень изоляции транзакций будешь использовать, если нужно сделать отчет со множеством запросов?

Уровни изоляции транзакций для создания отчётов

При создании отчётов со множеством запросов важно выбрать правильный уровень изоляции транзакций. Это влияет на консистентность данных, производительность и вероятность блокировок. Разберёмся во всех уровнях и когда какой использовать.

Уровни изоляции транзакций (ACID)

SQL стандарт определяет 4 уровня изоляции, от низшего к высшему:

1. Read Uncommitted (READ UNCOMMITTED)

Самый слабый уровень. Транзакция может читать незафиксированные (грязные) данные другой транзакции.

# Проблема: Dirty Read
# Транзакция A начинается и изменяет значение
# Транзакция B читает это незафиксированное значение
# Транзакция A откатывается
# Транзакция B имеет неверные данные

from sqlalchemy import create_engine, text

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

with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"))
    result = conn.execute(text("SELECT balance FROM accounts WHERE id = 1"))
    print(result.fetchone())

Когда использовать: Почти никогда. Только для очень некритичных данных с максимальной производительностью.

2. Read Committed (READ COMMITTED)

Транзакция может читать только зафиксированные данные. Это уровень по умолчанию в большинстве СУБД.

with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL READ COMMITTED"))
    # Проблема: Non-Repeatable Read
    # Транзакция A читает строку
    # Транзакция B изменяет и фиксирует эту строку
    # Транзакция A читает ту же строку ещё раз и получает другое значение
    result1 = conn.execute(text("SELECT balance FROM accounts WHERE id = 1")).fetchone()
    # <- Другая транзакция может изменить данные
    result2 = conn.execute(text("SELECT balance FROM accounts WHERE id = 1")).fetchone()
    # result1 и result2 могут быть разными

Когда использовать: Для большинства OLTP операций, когда нужна баланс между консистентностью и производительностью.

3. Repeatable Read (REPEATABLE READ)

Транзакция видит только те данные, которые были зафиксированы до её начала. Защищает от Non-Repeatable Read.

with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"))
    # В одной транзакции вы всегда видите одинаковые данные
    result1 = conn.execute(text("SELECT balance FROM accounts WHERE id = 1")).fetchone()
    # <- Другая транзакция изменяет и фиксирует данные
    result2 = conn.execute(text("SELECT balance FROM accounts WHERE id = 1")).fetchone()
    # result1 == result2 (гарантирует PostgreSQL и MySQL InnoDB)
    
    # Проблема: Phantom Read
    count1 = conn.execute(text("SELECT COUNT(*) FROM orders WHERE status='pending'")).fetchone()
    # <- Другая транзакция добавляет новую строку и фиксирует
    count2 = conn.execute(text("SELECT COUNT(*) FROM orders WHERE status='pending'")).fetchone()
    # count1 и count2 могут быть разными!

Когда использовать: Когда нужна консистентность прочитанных данных, но новые строки могут появиться.

4. Serializable (SERIALIZABLE)

Самый сильный уровень. Гарантирует полную изоляцию, как если бы транзакции выполнялись последовательно.

with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"))
    # Полная защита от всех аномалий: Dirty Read, Non-Repeatable Read, Phantom Read
    result = conn.execute(text("
        SELECT o.id, o.amount, c.balance 
        FROM orders o 
        JOIN customers c ON o.customer_id = c.id 
        WHERE c.country = 'USA'
    ")).fetchall()

Когда использовать: Для критичных финансовых операций, когда абсолютно необходима консистентность.

Рекомендация для отчётов

Используй READ COMMITTED или REPEATABLE READ для отчётов.

from sqlalchemy import create_engine, text
from contextlib import contextmanager

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

@contextmanager
def report_transaction(isolation_level="READ COMMITTED"):
    """Контекст-менеджер для генерации отчётов"""
    with engine.connect() as conn:
        conn.execute(text(f"SET TRANSACTION ISOLATION LEVEL {isolation_level}"))
        try:
            yield conn
            conn.commit()
        except Exception as e:
            conn.rollback()
            raise

# Использование
with report_transaction() as conn:
    # Получаем общую статистику
    total = conn.execute(text("
        SELECT COUNT(*) as count FROM orders
    ")).fetchone()[0]
    
    # Получаем детали по категориям
    categories = conn.execute(text("
        SELECT category, SUM(amount) as total 
        FROM orders 
        GROUP BY category
    ")).fetchall()
    
    # Получаем топ продавцов
    top_sellers = conn.execute(text("
        SELECT seller_id, COUNT(*) as sales_count 
        FROM orders 
        GROUP BY seller_id 
        ORDER BY sales_count DESC 
        LIMIT 10
    ")).fetchall()

Сравнительная таблица

Уровень              | Dirty | Non-Rep | Phantom | Производительность
                     | Read  | Read    | Read    | 
—————————————————————|———————|—————————|—————————|———————————————————
READ UNCOMMITTED    |  ✓    |    ✓    |    ✓    | Высокая ✓✓✓
READ COMMITTED      |  ✗    |    ✓    |    ✓    | Средняя ✓✓
REPEATABLE READ     |  ✗    |    ✗    |    ✓    | Низкая ✓
SERIALIZABLE        |  ✗    |    ✗    |    ✗    | Очень низкая ✗✗✗

Практический пример для отчёта

import logging
from datetime import datetime, timedelta
from sqlalchemy import create_engine, text

logger = logging.getLogger(__name__)

class ReportGenerator:
    def __init__(self, database_url):
        self.engine = create_engine(database_url)
    
    def generate_daily_report(self, date: datetime):
        """Генерирует дневной отчёт с повторяемыми читаниями"""
        with self.engine.begin() as conn:
            # REPEATABLE READ — так как нужна консистентность данных внутри отчёта
            conn.execute(text("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"))
            
            # Запрос 1: Общая статистика
            logger.info("Получение общей статистики...")
            stats = conn.execute(text("
                SELECT 
                    COUNT(*) as total_orders,
                    SUM(amount) as total_revenue,
                    AVG(amount) as avg_order
                FROM orders
                WHERE DATE(created_at) = :date
            "), {"date": date}).fetchone()
            
            # Запрос 2: Статистика по категориям
            logger.info("Получение статистики по категориям...")
            by_category = conn.execute(text("
                SELECT category, COUNT(*) as count, SUM(amount) as revenue
                FROM orders
                WHERE DATE(created_at) = :date
                GROUP BY category
            "), {"date": date}).fetchall()
            
            # Запрос 3: Лучшие продукты
            logger.info("Получение данных о лучших продуктах...")
            top_products = conn.execute(text("
                SELECT product_id, product_name, COUNT(*) as sales
                FROM orders
                WHERE DATE(created_at) = :date
                GROUP BY product_id, product_name
                ORDER BY sales DESC
                LIMIT 20
            "), {"date": date}).fetchall()
            
            return {
                "date": date,
                "stats": stats,
                "by_category": by_category,
                "top_products": top_products
            }

Вывод

Для отчётов выбирай READ COMMITTED (значение по умолчанию) или REPEATABLE READ если нужна консистентность данных в сложных многотабличных отчётах. SERIALIZABLE слишком медленный для отчётов, а READ UNCOMMITTED опасен для данных, которые влияют на бизнес-решения.