Какие знаешь виды уровней изоляции в БД?

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

Уровни изоляции (Isolation Levels) в SQL определяют как транзакции взаимодействуют друг с другом при одновременном доступе к одним и тем же данным. Это критично для поддержания целостности данных и производительности.

Проблемы конкурентности

Когда несколько транзакций выполняются одновременно, могут возникнуть следующие проблемы:

1. Dirty Read (грязное чтение) Транзакция читает данные, которые были изменены другой транзакцией, но ещё не были зафиксированы (commited).

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Еще не COMMIT

-- Transaction B (одновременно)
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Читает промежуточное значение!
COMMIT;

-- Если Transaction A выполнит ROLLBACK, Transaction B работает с несуществующими данными

2. Non-Repeatable Read (нестабильное чтение) Транзакция читает одни и те же данные дважды, но получает разные результаты, потому что другая транзакция изменила данные между чтениями.

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Result: 1000
-- ... some processing ...
SELECT balance FROM accounts WHERE id = 1; -- Result: 900 (changed!)
COMMIT;

-- Transaction B (между чтениями в A)
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

3. Phantom Read (фантомное чтение) Транзакция выполняет query дважды и получает разное количество строк, потому что другая транзакция вставила или удалила строки.

-- Transaction A
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- Result: 5
-- ... some processing ...
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- Result: 6 (phantom row!)
COMMIT;

-- Transaction B (между чтениями в A)
BEGIN;
INSERT INTO orders VALUES (new_order_for_user_1);
COMMIT;

4. Lost Update (потеря обновления) Две транзакции читают одно значение и пытаются его обновить, что приводит к потере одного из обновлений.

-- Transaction A             -- Transaction B
BEGIN;                        BEGIN;
SELECT balance = 100;         SELECT balance = 100;
balance = balance - 50;       balance = balance + 50;
UPDATE ... (150);             UPDATE ... (50);
-- Потеря -50 обновления

Уровни изоляции (по возрастанию строгости)

1. Read Uncommitted (Чтение незафиксированных данных)

Позволяет:

• Dirty Read — ✅ возможны
• Non-Repeatable Read — ✅ возможны
• Phantom Read — ✅ возможны

Характеристики:

• Самый низкий уровень изоляции
• Наивысшая производительность
• Очень редко используется в production

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2. Read Committed (Чтение зафиксированных данных)

Позволяет:

• Dirty Read — ❌ нет
• Non-Repeatable Read — ✅ возможны
• Phantom Read — ✅ возможны

Характеристики:

• Дефолтный уровень в PostgreSQL и многих других БД
• Хороший баланс между производительностью и безопасностью
• Использует row-level locks при изменении данных

// В PostgreSQL (дефолт)
const result = await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
const data = await client.query('SELECT * FROM accounts WHERE id = 1');
await client.query('COMMIT');

3. Repeatable Read (Повторяемое чтение)

Позволяет:

• Dirty Read — ❌ нет
• Non-Repeatable Read — ❌ нет
• Phantom Read — ✅ возможны (в большинстве БД)

Характеристики:

• Использует snapshot изоляцию
• Гарантирует что данные, прочитанные в транзакции, не изменяются
• Phantom reads все еще возможны

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT * FROM accounts WHERE user_id = 1; -- Snapshot создан
-- ...
SELECT * FROM accounts WHERE user_id = 1; -- Тот же результат
COMMIT;

4. Serializable (Сериализуемость)

Позволяет:

• Dirty Read — ❌ нет
• Non-Repeatable Read — ❌ нет
• Phantom Read — ❌ нет

Характеристики:

• Самый высокий уровень изоляции
• Гарантирует полную сериализацию транзакций
• Может значительно снизить производительность из-за блокировок
• Используется для критичных операций

// PostgreSQL: Serializable isolation
const result = await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

try {
  // Critical operations
  await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
  await client.query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
  await client.query('COMMIT');
} catch (error) {
  if (error.code === '40P01') { // Serialization conflict
    // Retry the transaction
    console.log('Serialization conflict, retrying...');
    await client.query('ROLLBACK');
  }
}

Различия между БД

PostgreSQL:

• Дефолт: Read Committed
• Phantom reads НЕ возможны в Repeatable Read благодаря MVCC (Multi-Version Concurrency Control)

MySQL (InnoDB):

• Дефолт: Repeatable Read
• Использует gap locks для предотвращения phantom reads

Oracle:

• Дефолт: Read Committed
• Не имеет Repeatable Read уровня

Практические рекомендации

Используй Read Committed когда:

• Стандартная web-приложение с CRUD операциями
• Дефолт для большинства приложений
• Хороший баланс производительность vs безопасность

// Node.js с PostgreSQL
const pool = new Pool({
  isolationLevel: 'READ_COMMITTED' // дефолт
});

Используй Repeatable Read когда:

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

const client = await pool.connect();
await client.query('BEGIN ISOLATION LEVEL REPEATABLE READ');
try {
  const balance = await client.query('SELECT balance FROM accounts WHERE id = 1');
  // Гарантия что balance не изменится до COMMIT
  await client.query('UPDATE accounts SET balance = ...');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
}

Используй Serializable когда:

• Финансовые операции (переводы денег)
• Бронирование уникальных ресурсов
• Любые критичные операции где data integrity абсолютна важна

// Пример: перевод денег между счётами
await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
try {
  const fromBalance = await client.query(
    'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
    [fromAccountId]
  );
  
  if (fromBalance.rows[0].balance < amount) {
    throw new Error('Insufficient funds');
  }
  
  await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
    [amount, fromAccountId]
  );
  
  await client.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
    [amount, toAccountId]
  );
  
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  if (error.code === '40P01') {
    // Retry logic
  }
}

Вывод

Выбор уровня изоляции — это компромисс между:- Безопасностью данных (защита от аномалий)

• Производительностью (меньше блокировок = выше throughput)
• Сложностью кода (обработка ошибок конкуренции)

Для большинства веб-приложений Read Committed достаточно, но критичные операции (финансы, бронирования) требуют Serializable с повторными попытками при конфликтах.