Какие виды ошибок решают уровни изоляции?

Какие виды ошибок решают уровни изоляции?

Уровни изоляции транзакций (isolation levels) решают проблемы, которые возникают, когда несколько транзакций одновременно работают с одной базой данных. Это КРИТИЧНАЯ часть понимания БД для любого серьёзного разработчика.

Основные проблемы параллелизма

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

Проблема: Одна транзакция читает данные, которые изменила другая транзакция, но та ещё не закоммитилась.

# Транзакция A            # Транзакция B
BEGIN;                    BEGIN;
                          UPDATE accounts SET balance = 100
                          WHERE id = 1;
SELECT balance FROM       # Видит balance = 100 (грязные данные!)
accounts WHERE id = 1;    # Может быть ROLLBACK
COMMIT;                   ROLLBACK; # Отмена! balance остался 50

Решение: Уровень READ COMMITTED или выше

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

Проблема: Одна транзакция читает данные дважды, и между чтениями другая транзакция их изменила.

# Транзакция A            # Транзакция B
BEGIN;                    BEGIN;
SELECT balance FROM       
accounts WHERE id = 1;    
# Результат: 100          UPDATE accounts SET balance = 200
                          WHERE id = 1;
                          COMMIT;
SELECT balance FROM       
accounts WHERE id = 1;    
# Результат: 200 (РАЗНЫЕ!) — inconsistent read
COMMIT;

Проблема: Одна функция получила 100, потом через 5 мс — 200. Логика сломана.

Решение: Уровень REPEATABLE READ или выше

3. Phantom Read (Призрачное чтение)

Проблема: Один запрос возвращает разное количество строк при повторном выполнении внутри одной транзакции.

# Транзакция A                # Транзакция B
BEGIN;                        BEGIN;
SELECT COUNT(*) FROM users
WHERE age > 18;               
# Результат: 100 пользователей
                              INSERT INTO users (age) VALUES (25);
                              COMMIT;
SELECT COUNT(*) FROM users
WHERE age > 18;               
# Результат: 101 (ПРИЗРАК!)
COMMIT;

Решение: Уровень SERIALIZABLE

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

Проблема: Две транзакции обновляют один и тот же показатель, и одно обновление теряется.

# Транзакция A            # Транзакция B
BEGIN;                    BEGIN;
SELECT balance FROM       SELECT balance FROM
accounts WHERE id = 1;    accounts WHERE id = 1;
# balance = 100           # balance = 100

UPDATE accounts SET       UPDATE accounts SET
balance = 100 + 50 = 150  balance = 100 - 30 = 70
WHERE id = 1;             WHERE id = 1;
COMMIT;                   COMMIT;
# Финальный balance = 70 (потеряли +50!)

Решение: Выбор стратегии оптимистичной/пессимистичной блокировки или SERIALIZABLE

4 уровня изоляции (ANSI SQL)

1. READ UNCOMMITTED (самый низкий)

Решает: ничего!

Проблемы:

• Dirty Read ❌
• Non-Repeatable Read ❌
• Phantom Read ❌
• Lost Update ❌

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Использование: Никогда для критичных данных (разве что аналитика, где точность не критична)

2. READ COMMITTED (стандартный)

Решает: Dirty Read ✓

Проблемы:

• Non-Repeatable Read ❌
• Phantom Read ❌
• Lost Update ❌ (в некоторых системах ✓)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

# Транзакция видит только закоммиченные данные
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  # Видит только committed
COMMIT;

Использование: 90% приложений, веб-сервисы, обычные CRUD

3. REPEATABLE READ (PostgreSQL по умолчанию)

Решает: Dirty Read ✓, Non-Repeatable Read ✓

Проблемы:

• Phantom Read ❌
• Lost Update ❌ (иногда ✓)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# Получает снимок БД на момент начала транзакции
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  # Видит снимок
# Даже если другая транзакция обновит — мы видим старое
SELECT balance FROM accounts WHERE id = 1;  # Одно и то же
COMMIT;

Использование: Финансовые системы, критичные операции

4. SERIALIZABLE (самый высокий)

Решает: Всё! Dirty Read ✓, Non-Repeatable Read ✓, Phantom Read ✓, Lost Update ✓

Эффект: Как будто транзакции выполняются одна за другой (серийно)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

# Полная блокировка/snapshot isolation
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  # Блокирует на всё время
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;
# Никакая другая транзакция не может одновременно работать с этими данными

Использование: Атомарные финансовые операции, когда консистентность > производительность

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

Практический пример на Python + PostgreSQL

import psycopg2
from psycopg2 import sql
import concurrent.futures
import time

def transfer_money_dirty_read():
    """Демонстрирует dirty read на READ UNCOMMITTED"""
    conn = psycopg2.connect("dbname=test user=postgres")
    
    # Транзакция 1 (READ UNCOMMITTED)
    def txn1():
        conn.set_isolation_level(0)  # READ UNCOMMITTED
        cur = conn.cursor()
        cur.execute("BEGIN")
        cur.execute("SELECT balance FROM accounts WHERE id=1")
        balance1 = cur.fetchone()[0]
        print(f"TXN1: Первое чтение: {balance1}")
        
        time.sleep(0.5)  # Ждём второй транзакции
        
        cur.execute("SELECT balance FROM accounts WHERE id=1")
        balance2 = cur.fetchone()[0]
        print(f"TXN1: Второе чтение: {balance2}")
        print(f"TXN1: DIRTY READ! {balance1} -> {balance2}")
        cur.execute("COMMIT")
    
    # Транзакция 2
    def txn2():
        time.sleep(0.2)  # Даём txn1 прочитать первый раз
        conn2 = psycopg2.connect("dbname=test user=postgres")
        cur2 = conn2.cursor()
        cur2.execute("BEGIN")
        cur2.execute("UPDATE accounts SET balance = 9999 WHERE id=1")
        print(f"TXN2: Обновили balance на 9999")
        cur2.execute("COMMIT")
        conn2.close()
    
    with concurrent.futures.ThreadPoolExecutor() as executor:
        executor.submit(txn1)
        executor.submit(txn2)

def transfer_money_repeatable_read():
    """Демонстрирует защиту на REPEATABLE READ"""
    conn = psycopg2.connect("dbname=test user=postgres")
    
    # Транзакция 1 (REPEATABLE READ)
    def txn1():
        conn.set_isolation_level(2)  # REPEATABLE READ
        cur = conn.cursor()
        cur.execute("BEGIN")
        cur.execute("SELECT balance FROM accounts WHERE id=1")
        balance1 = cur.fetchone()[0]
        print(f"TXN1: Первое чтение: {balance1}")
        
        time.sleep(0.5)  # Ждём второй транзакции
        
        cur.execute("SELECT balance FROM accounts WHERE id=1")
        balance2 = cur.fetchone()[0]
        print(f"TXN1: Второе чтение: {balance2}")
        print(f"TXN1: ОДИНАКОВО: {balance1} == {balance2}")
        cur.execute("COMMIT")
    
    # Транзакция 2 (пытается обновить)
    def txn2():
        time.sleep(0.2)
        conn2 = psycopg2.connect("dbname=test user=postgres")
        cur2 = conn2.cursor()
        try:
            cur2.execute("BEGIN")
            cur2.execute("UPDATE accounts SET balance = 9999 WHERE id=1")
            print(f"TXN2: Обновили balance на 9999")
            cur2.execute("COMMIT")
        except Exception as e:
            print(f"TXN2: Ошибка при обновлении: {e}")
        conn2.close()
    
    with concurrent.futures.ThreadPoolExecutor() as executor:
        executor.submit(txn1)
        executor.submit(txn2)

if __name__ == "__main__":
    print("=== Dirty Read (READ UNCOMMITTED) ===")
    transfer_money_dirty_read()
    
    print("\n=== Repeatable Read Protection ===")
    transfer_money_repeatable_read()

Рекомендации по выбору

# Веб-приложение (обычные операции)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  # ✓ Стандарт

# Финансовые операции (переводы денег)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; # ✓ Безопасно

# Очень критичные операции (распределение ресурсов)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;    # ⚠️ Только если нужно

# Аналитика (читаем данные)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  # ✓ Быстро

Резюме

• READ UNCOMMITTED — не используй
• READ COMMITTED — для большинства приложений
• REPEATABLE READ — для финансов и критичной консистентности
• SERIALIZABLE — редко, очень дорого
• PostgreSQL REPEATABLE READ использует snapshot isolation, что сильнее ANSI standard
• Помни: Выше уровень = выше безопасность, но ниже производительность