Для чего нужен MVCC в PostgreSQL?

# MVCC в PostgreSQL

MVCC (Multi-Version Concurrency Control) — это механизм, который позволяет PostgreSQL обеспечивать параллельный доступ к данным без блокировок читателей и писателей друг на друга. Это одна из ключевых особенностей PostgreSQL.

Проблема, которую решает MVCC

Без MVCC при одновременном чтении и записи происходит следующее:

• Транзакция 1 читает строку (удерживает READ LOCK)
• Транзакция 2 пытается обновить эту строку (нужен WRITE LOCK)
• Транзакция 2 ждёт, пока Транзакция 1 закончит читать

С MVCC этого не происходит: читатели видят старую версию, писатель создаёт новую версию.

Как работает MVCC

Каждая строка в PostgreSQL имеет две системные колонки:

xmin — идентификатор транзакции, которая создала версию
xmax — идентификатор транзакции, которая удалила версию

Пример:

TID | ID | Name  | xmin | xmax
----|----|---------|----|------
1   | 1  | Alice | 100 | 105
2   | 1  | Alice | 105 | NULL   <- новая версия
3   | 2  | Bob   | 101 | NULL

Когда Транзакция 106 читает таблицу:

• Видит строку с ID=1, Name=Alice (xmin=105, xmax=NULL) — текущая версия
• Не видит старую версию (xmin=100, xmax=105) — удалена в т-ции 105

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

MVCC позволяет реализовать разные уровни:

1. READ UNCOMMITTED (не поддерживается в PostgreSQL)

Видит грязные чтения (uncommitted changes)

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

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:pass@localhost/db",
    isolation_level="READ_COMMITTED"  # по умолчанию
)

with engine.connect() as conn:
    # Видит changes других транзакций после commit
    result = conn.execute("SELECT * FROM users")

Визуальный пример:

Время | Транзакция A (READ COMMITTED) | Транзакция B
-----|--------------------------------|----------------
T1   | BEGIN                          | 
T2   |                                | UPDATE users SET balance=100
T3   | SELECT balance FROM users      | (видит 100)
     | WHERE id=1                     |
T4   |                                | ROLLBACK
T5   | SELECT balance FROM users      |
     | WHERE id=1                     | (видит старое значение)

3. REPEATABLE READ

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

Возвращает одинаковый снимок данных на весь duration транзакции:

Время | Транзакция A              | Транзакция B
-----|--------------------------|----------------
T1   | BEGIN                    | 
T2   | SELECT * FROM users      | (видит 5 пользователей)
T3   |                          | INSERT INTO users...
T4   | SELECT * FROM users      |
     | (всё ещё видит 5)        | (B добавила 6-го)
T5   | COMMIT                   |

4. SERIALIZABLE

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

Phantom Reads и MVCC

phantom read — это когда две транзакции видят разные наборы строк:

# Транзакция A: REPEATABLE READ
with engine.begin() as conn:
    # Снимок T1
    count = conn.execute(
        "SELECT COUNT(*) FROM orders WHERE status='pending'"
    ).scalar()
    # count = 5
    
    # Тем временем Транзакция B добавила новый заказ
    # Снимок T1 — всё ещё видит 5
    
    # Но этот запрос вернёт 6:
    new_count = conn.execute(
        "SELECT COUNT(*) FROM orders WHERE status='pending'"
    ).scalar()

Для защиты от phantom reads нужен SERIALIZABLE.

Практический пример: Race Condition

Без правильного уровня изоляции:

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

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

# Сценарий: два процесса пытаются купить последний товар

def buy_item(user_id: int, item_id: int):
    with Session(engine) as session:
        # READ COMMITTED (по умолчанию)
        
        # Оба процесса видят stock=1
        item = session.query(Item).filter(
            Item.id == item_id
        ).first()
        
        if item.stock > 0:
            item.stock -= 1  # оба уменьшают на 1
            session.commit()
        else:
            raise ValueError("Out of stock")

# Результат: stock=0 (вместо -1), но оба заказа приняты!

Решение — REPEATABLE READ или SERIALIZABLE:

from sqlalchemy import text

def buy_item_safe(user_id: int, item_id: int):
    with Session(engine) as session:
        session.execute(
            text("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
        )
        
        item = session.query(Item).filter(
            Item.id == item_id
        ).first()
        
        if item.stock > 0:
            item.stock -= 1
            session.commit()  # commit в конце снимка
        else:
            raise ValueError("Out of stock")

Или с SELECT FOR UPDATE:

def buy_item_locked(user_id: int, item_id: int):
    with Session(engine) as session:
        item = session.query(Item).filter(
            Item.id == item_id
        ).with_for_update().first()  # LOCK эту строку
        
        if item.stock > 0:
            item.stock -= 1
            session.commit()

Влияние на производительность

MVCC требует хранения старых версий строк, что занимает место:

-- Проверить размер таблицы
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- 2500 MB (включает старые версии)

-- VACUUM очищает мёртвые версии
VACUUM users;  -- пересчитает space

-- VACUUM ANALYZE + обновляет статистику
VACUUM ANALYZE users;

Автоматическая чистка (autovacuum) в PostgreSQL:

-- Настройка в postgresql.conf
autovacuum = on
autovacuum_naptime = '10s'         -- проверяет каждые 10 сек
autovacuum_vacuum_threshold = 50   -- после 50 изменений

Ключевые выводы

• MVCC даёт параллелизм без блокировок читателей и писателей
• READ COMMITTED — стандарт, хорош для большинства приложений
• REPEATABLE READ — защищает от phantom reads в одной транзакции
• SERIALIZABLE — полная изоляция, но медленнее
• SELECT FOR UPDATE — явная блокировка для critical sections
• VACUUM — необходим для очистки старых версий и производительности