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

Уровни изоляции транзакций в базах данных

Уровни изоляции (Transaction Isolation Levels) — это критически важная концепция при работе с БД, особенно в systems с высокой параллельностью. Они определяют как транзакции взаимодействуют и какие аномалии могут возникнуть.

ACID свойства транзакций

Прежде всего, транзакция должна удовлетворять ACID:

• Atomicity — всё или ничего
• Consistency — данные остаются в консистентном состоянии
• Isolation — транзакции не мешают друг другу
• Durability — сохранённые данные не потеряются

Иерархия уровней изоляции

От наименьшей изоляции (больше параллелизма, меньше гарантий) к наибольшей:

1. READ UNCOMMITTED (Грязное чтение)

Транзакция может читать UNCOMMITTED данные других транзакций

Проблемы:
- Dirty reads: читаем данные, которые потом откатятся
- Non-repeatable reads: одно значение читается по-разному
- Phantom reads: набор строк изменяется между чтениями

Использование: очень редко, только когда скорость важнее точности

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
  SELECT balance FROM accounts WHERE id = 1;  -- Может быть грязное значение
COMMIT;

2. READ COMMITTED (Прочитанные строки не заблокированы)

Транзакция может читать только COMMITTED данные

Проблемы:
- Non-repeatable reads: можем увидеть обновление между двумя SELECT'ами
- Phantom reads: количество строк может измениться

Преимущества:
- Нет dirty reads
- Хороший баланс между скоростью и безопасностью

Использование: default уровень в большинстве БД (PostgreSQL, SQL Server)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
  SELECT balance FROM accounts WHERE id = 1;  -- balance = 1000
  -- Другая транзакция обновляет balance на 2000
  SELECT balance FROM accounts WHERE id = 1;  -- balance = 2000 (отличается!)
COMMIT;

3. REPEATABLE READ

Фотография (snapshot) данных в начале транзакции

Гарантирует:
- Нет dirty reads
- Нет non-repeatable reads
- Повторные SELECT'ы вернут одинаковые результаты

Проблемы:
- Phantom reads: новые строки могут появиться при втором SELECT

Использование: когда нужна консистентность в одной транзакции

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  SELECT * FROM orders WHERE status = 'pending';  -- 5 строк
  -- Другая транзакция добавляет новую pending order
  SELECT * FROM orders WHERE status = 'pending';  -- 6 строк (phantom read!)
COMMIT;

4. SERIALIZABLE (Максимальная изоляция)

Транзакции выполняются так, как если бы они были последовательны

Гарантирует:
- Нет dirty reads
- Нет non-repeatable reads
- Нет phantom reads
- Полная изоляция

Недостатки:
- Медленно (много блокировок и конфликтов)
- Может привести к deadlock'ам

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  SELECT COUNT(*) FROM accounts WHERE balance > 1000;
  -- Гарантированно никто не добавит/не изменит счета в этом диапазоне
COMMIT;

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

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

Проблема: Race condition в банке

# Два пользователя одновременно снимают со счёта
# Начальный баланс: 100

# Transaction 1: READ COMMITTED
BEGIN;
balance = SELECT balance FROM accounts WHERE id = 1;  # 100
balance -= 50;
UPDATE accounts SET balance = balance WHERE id = 1;
COMMIT;

# Transaction 2: READ COMMITTED
BEGIN;
balance = SELECT balance FROM accounts WHERE id = 1;  # 100 (видит старое!)
balance -= 40;
UPDATE accounts SET balance = balance WHERE id = 1;
COMMIT;

# Результат: баланс = 60 (должен быть 10!)
# Решение: использовать SERIALIZABLE или SELECT FOR UPDATE

Правильное решение с блокировкой:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- SELECT FOR UPDATE блокирует строку для других транзакций
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

PostgreSQL особенности

PostgreSQL использует MVCC (Multi-Version Concurrency Control)

# PostgreSQL уровни:
# 1. READ UNCOMMITTED (на деле работает как READ COMMITTED)
# 2. READ COMMITTED (default)
# 3. REPEATABLE READ
# 4. SERIALIZABLE

# Пример REPEATABLE READ в PostgreSQL
import psycopg2

conn = psycopg2.connect("dbname=mydb")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ)

cursor = conn.cursor()
cursor.execute("BEGIN")
cursor.execute("SELECT * FROM accounts")
first_read = cursor.fetchall()

# Другая транзакция обновляет данные...

cursor.execute("SELECT * FROM accounts")
second_read = cursor.fetchall()

# first_read == second_read (MVCC гарантирует!)
conn.commit()

Явное управление блокировками

SELECT FOR UPDATE

BEGIN;
  -- Блокируем строку для других транзакций
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

SELECT FOR SHARE

BEGIN;
  -- Shared lock (другие могут читать, но не писать)
  SELECT * FROM accounts WHERE id = 1 FOR SHARE;
COMMIT;

Dead locks

Основная проблема при использовании SERIALIZABLE:

# Transaction 1: UPDATE account 1, потом account 2
# Transaction 2: UPDATE account 2, потом account 1
# Результат: DEADLOCK!

# Решение: всегда SELECT/UPDATE в одном порядке
BEGIN;
  SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
  UPDATE accounts SET balance = ... WHERE id = 1;
  UPDATE accounts SET balance = ... WHERE id = 2;
COMMIT;

Выбор уровня изоляции

READ COMMITTED

• Default выбор
• Большинство приложений работают отлично
• Хороший баланс

set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)

REPEATABLE READ

• Когда нужна консистентность внутри одной транзакции
• Например, аналитические запросы

set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ)

SERIALIZABLE

• Только когда действительно критична полная изоляция
• Платежи, инвентарь, финансовые операции
• Будь готов к deadlock'ам!

set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE)

Best Practices

1. Знай default уровень БД (PostgreSQL = READ COMMITTED)
2. Минимизируй время транзакции — чем быстрее, тем меньше конфликтов
3. Используй индексы — быстрее блокировки
4. Обрабатывай deadlock'и

from time import sleep
max_retries = 3

for attempt in range(max_retries):
    try:
        execute_transaction()
        break
    except psycopg2.extensions.TransactionRollbackError:
        if attempt < max_retries - 1:
            sleep(2 ** attempt)  # Exponential backoff
        else:
            raise

1. Тестируй конкурентность — используй инструменты профилирования
2. Документируй уровень изоляции в кодовых комментариях

Выводы

Уровни изоляции — это компромисс между параллелизмом и точностью. READ COMMITTED достаточно для большинства случаев, но важно понимать когда нужны более строгие уровни. Всегда выбирай уровень в зависимости от требований, а не используй SERIALIZABLE везде — это приведёт к медленности и deadlock'ам.