Какие знаешь уровни изолированности PostgreSQL?

Уровни изолированности транзакций в PostgreSQL

Постгрес поддерживает четыре стандартных уровня, определённых SQL стандартом. Каждый решает разные проблемы параллелизма.

1. Read Uncommitted

В PostgreSQL это READ COMMITTED (не true read uncommitted!).

Проблемы, которые НЕ решает:

• ✅ Dirty reads — не бывает (данные коммитятся)
• ❌ Non-repeatable reads — бывают
• ❌ Phantom reads — бывают

# Пример: Non-repeatable read
# Транзакция T1          | Транзакция T2
# SELECT age FROM user   |
#                        | UPDATE user SET age = 30
#                        | COMMIT
# SELECT age FROM user   | <- другой результат!

from sqlalchemy.orm import Session
from sqlalchemy import text

# Первый запрос
age1 = session.execute(
    text("SELECT age FROM users WHERE id = 1")
).scalar()

# Кто-то другой обновил 
# UPDATE users SET age = 30 WHERE id = 1

# Второй запрос видит изменение
age2 = session.execute(
    text("SELECT age FROM users WHERE id = 1")
).scalar()

print(age1, age2)  # Разные значения!

Когда использовать: быстрые OLTP транзакции, когда небольшие расхождения неважны.

2. Read Committed (DEFAULT)

Это уровень по умолчанию в PostgreSQL.

Гарантирует:

• ✅ Никаких Dirty reads
• ✅ Читаешь только коммиченные данные
• ❌ Non-repeatable reads — возможны
• ❌ Phantom reads — возможны

# Проблема: Non-repeatable read
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

engine = create_engine("postgresql://...")
Session = sessionmaker(bind=engine)

session = Session()

# SELECT ... FOR UPDATE помогает избежать проблемы
user = session.execute(
    text("SELECT * FROM users WHERE id = 1 FOR UPDATE")
).first()

print(user.age)  # Заблокирована для других транзакций

Когда использовать: большинство приложений, стандартный выбор.

3. Repeatable Read

Более строгий уровень.

Гарантирует:

• ✅ Никаких Dirty reads
• ✅ Никаких Non-repeatable reads (в пределах одной транзакции читаешь один снимок)
• ❌ Phantom reads — ВСЕ ЕЩЕ возможны (в PostgreSQL)

# Пример: Phantom read
# Транзакция T1                  | Транзакция T2
# SELECT * FROM users WHERE age > 30; (0 строк) |
#                                | INSERT INTO users VALUES (31)
#                                | COMMIT
# SELECT * FROM users WHERE age > 30; (1 строка) | <- Фантом!

# В PostgreSQL это в REPEATABLE READ:
from sqlalchemy import create_engine, text, event

engine = create_engine(
    "postgresql://...",
    isolation_level="REPEATABLE READ"  # Важно!
)

with engine.connect() as conn:
    # Установить уровень
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"))
    
    # Первый запрос
    result1 = conn.execute(
        text("SELECT * FROM users WHERE age > 30")
    ).fetchall()
    
    # Другой процесс INSERT новый user
    # ...
    
    # Второй запрос видит то же состояние (из начала транзакции)
    result2 = conn.execute(
        text("SELECT * FROM users WHERE age > 30")
    ).fetchall()
    
    print(len(result1) == len(result2))  # True в REPEATABLE READ

Проблемы в PostgreSQL:

• Serialization conflicts — транзакция может откатиться
• Нужна обработка ошибок

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

4. Serializable

Самый строгий уровень.

Гарантирует:

• ✅ Никаких Dirty reads
• ✅ Никаких Non-repeatable reads
• ✅ Никаких Phantom reads
• ✅ Полная изоляция, как если бы транзакции выполнялись последовательно

# Самый безопасный, но самый медленный
from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql://...",
    isolation_level="SERIALIZABLE"
)

with engine.connect() as conn:
    conn.execute(text("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"))
    
    # Любые конфликты будут откачены
    try:
        result = conn.execute(
            text("SELECT * FROM users WHERE id = 1")
        ).first()
        
        # Если была конкуренция, может быть ошибка
        conn.execute(
            text("UPDATE users SET balance = balance - 100 WHERE id = 1")
        )
        conn.commit()
    except Exception as e:
        # Serialization conflict!
        print(f"Retry: {e}")
        conn.rollback()

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

Сравнение уровней

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

Проблема: Race condition в счётчиках

# ❌ Проблема: с Read Committed счётчик может быть неправильный
# T1: SELECT count FROM products; -- count = 10
# T2: SELECT count FROM products; -- count = 10
# T1: UPDATE products SET count = 11
# T2: UPDATE products SET count = 11  <- потеря обновления!

# ✅ Решение 1: SELECT FOR UPDATE
with session.begin():
    product = session.execute(
        text("SELECT * FROM products WHERE id = 1 FOR UPDATE")
    ).first()
    product.count += 1
    session.commit()

# ✅ Решение 2: Atomic update
from sqlalchemy import update

with session.begin():
    session.execute(
        update(Product)
        .where(Product.id == 1)
        .values(count=Product.count + 1)
    )
    session.commit()

# ✅ Решение 3: REPEATABLE READ
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://...",
    isolation_level="REPEATABLE READ"
)

Проблема: Переводы денег между счетами

# ❌ Без блокировки может быть inconsistency
def transfer_money(from_account_id, to_account_id, amount):
    from_account = session.query(Account).filter(
        Account.id == from_account_id
    ).first()
    
    to_account = session.query(Account).filter(
        Account.id == to_account_id
    ).first()
    
    if from_account.balance >= amount:
        from_account.balance -= amount
        to_account.balance += amount
        session.commit()

# Проблема: между чтением и обновлением счет может измениться!

# ✅ Правильный подход: SERIALIZABLE или явные блокировки
from sqlalchemy import text

def transfer_money_safe(from_account_id, to_account_id, amount):
    with session.begin():
        # Блокируем обе строки
        from_account = session.execute(
            text("""
                SELECT * FROM accounts WHERE id = :id FOR UPDATE
            """),
            {"id": from_account_id}
        ).first()
        
        to_account = session.execute(
            text("""
                SELECT * FROM accounts WHERE id = :id FOR UPDATE
            """),
            {"id": to_account_id}
        ).first()
        
        if from_account.balance >= amount:
            session.execute(
                text("""
                    UPDATE accounts 
                    SET balance = balance - :amount
                    WHERE id = :id
                """),
                {"amount": amount, "id": from_account_id}
            )
            
            session.execute(
                text("""
                    UPDATE accounts
                    SET balance = balance + :amount
                    WHERE id = :id
                """),
                {"amount": amount, "id": to_account_id}
            )

Рекомендации

1. По умолчанию — Read Committed (стандарт)
2. Для отчетов — Repeatable Read
3. Для финансов — Serializable или SELECT FOR UPDATE
4. Не полагайся на SERIALIZABLE как на лекарство — лучше используй правильную архитектуру

В Python с SQLAlchemy

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# По умолчанию READ COMMITTED
engine = create_engine("postgresql://...")

# Изменить для всей сессии
engine = create_engine(
    "postgresql://...",
    isolation_level="REPEATABLE READ"
)

# Или для отдельной транзакции
session = sessionmaker(bind=engine)()
with session.begin(
    nested=False,
    isolation_level="SERIALIZABLE"
):
    # Код в SERIALIZABLE
    pass

Ключевой вывод: выбирай правильный уровень для задачи, не закрывай глаза на race condition'ы!