Объясните концепции транзакций и уровней изоляции в SQL.

Транзакции и уровни изоляции в SQL

Транзакция — это группа SQL операций, которые выполняются как единое целое: либо все выполняются успешно, либо все откатываются. Это гарантирует целостность данных при сбое.

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

Atomicity (Атомарность) — либо все операции выполнены, либо ни одна

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- счёт 1 минус 100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- счёт 2 плюс 100
COMMIT;  -- либо обе, либо ни одна

-- Если ошибка между UPDATE:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
INVALID SQL QUERY;  -- ошибка!
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- не выполнится
ROLLBACK;  -- отменяет всё

Consistency (Консистентность) — данные переходят из одного валидного состояния в другое

-- Ограничение: баланс не может быть отрицательный
ALTER TABLE accounts ADD CONSTRAINT check_balance CHECK (balance >= 0);

BEGIN TRANSACTION;
UPDATE accounts SET balance = -50 WHERE id = 1;  -- нарушает ограничение
COMMIT;  -- откатится (попытка нарушить check constraint)

Isolation (Изоляция) — одновременные транзакции не мешают друг другу

-- Транзакция 1
BEGIN;
UPDATE balance SET amount = 1000 WHERE user_id = 1;
-- Транзакция 2 не видит эту строку, пока Транзакция 1 не завершится

-- Транзакция 2
SELECT amount FROM balance WHERE user_id = 1;  -- старое значение

Durability (Постоянство) — после COMMIT данные сохранены на диск и не будут потеряны при сбое

BEGIN;
INSERT INTO orders VALUES (123, 'item', 100);
COMMIT;  -- данные записаны на диск
SERVER CRASHES!  -- данные всё равно будут в БД

Уровни изоляции

Чем выше изоляция, тем безопаснее, но медленнее (больше блокировок).

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

Транзакция видит незавершённые изменения других транзакций (Dirty Read).

-- Транзакция 1
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
UPDATE balance SET amount = 50 WHERE user_id = 1;
-- НЕ COMMIT ещё!

-- Транзакция 2
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SELECT amount FROM balance WHERE user_id = 1;  -- видит 50!
COMMIT;

-- Транзакция 1
ROLLBACK;  -- отменяем 50, но Транзакция 2 уже её видела!

-- Результат: Dirty Read (прочитали несуществующие данные)

Проблемы:

• Dirty Read (прочитали неcommitted данные)
• Non-repeatable Read (повторный SELECT вернёт другое значение)
• Phantom Read (повторный SELECT покажет новые строки)

Когда использовать: НИКОГДА! Только для отчётов, которые неточные

2. READ COMMITTED (по умолчанию в большинстве СУБД)

Транзакция видит только committed данные, но может быть Non-repeatable Read.

-- Транзакция 1
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE balance SET amount = 50 WHERE user_id = 1;
COMMIT;  -- теперь видно

-- Транзакция 2
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT amount FROM balance WHERE user_id = 1;  -- прочитала 50
COMMIT;

Проблемы:

• Non-repeatable Read (повторный SELECT может вернуть другое значение)
• Phantom Read (новые строки могут появиться)

Пример Non-repeatable Read:

-- Транзакция 1
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT amount FROM balance WHERE user_id = 1;  -- 100

-- Транзакция 2
BEGIN;
UPDATE balance SET amount = 200 WHERE user_id = 1;
COMMIT;

-- Транзакция 1
SELECT amount FROM balance WHERE user_id = 1;  -- 200 (!)
-- одна и та же строка, разные значения → Non-repeatable Read
COMMIT;

3. REPEATABLE READ (MySQL default)

Транзакция видит снимок данных на момент начала. Нет Non-repeatable Read, но может быть Phantom Read.

-- Транзакция 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT amount FROM balance WHERE user_id = 1;  -- 100

-- Транзакция 2
BEGIN;
UPDATE balance SET amount = 200 WHERE user_id = 1;
COMMIT;

-- Транзакция 1
SELECT amount FROM balance WHERE user_id = 1;  -- всё ещё 100!
-- Видит снимок на момент BEGIN
COMMIT;

Phantom Read пример:

-- Транзакция 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders WHERE status='pending';  -- 5

-- Транзакция 2
BEGIN;
INSERT INTO orders VALUES (6, 'pending');
COMMIT;

-- Транзакция 1
SELECT COUNT(*) FROM orders WHERE status='pending';  -- 6 (!)
-- новая строка появилась → Phantom Read
COMMIT;

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

Полная изоляция, как если бы транзакции выполнялись одна за другой. Нет никаких проблем.

-- Транзакция 1
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT amount FROM balance WHERE user_id = 1;  -- 100

-- Транзакция 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE balance SET amount = 200 WHERE user_id = 1;  -- ЖДЁТ! Блокирована
COMMIT;  -- теперь может продолжить

-- Транзакция 1
SELECT amount FROM balance WHERE user_id = 1;  -- 100 (снимок)
COMMIT;

Таблица проблем и уровней

SQL примеры по СУБД

PostgreSQL (по умолчанию: READ COMMITTED)

BEGIN ISOLATION LEVEL READ COMMITTED;
-- операции
COMMIT;

-- Или:
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- операции
COMMIT;

MySQL (по умолчанию: REPEATABLE READ)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- операции
COMMIT;

SQL Server (по умолчанию: READ COMMITTED)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- операции
COMMIT;

Практический пример: Перевод денег

Без транзакций (ОПАСНО):

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()

# ❌ Плохо: если сбой между двумя UPDATE
c.execute("UPDATE balance SET amount = amount - 100 WHERE user_id = 1")
if server_crashes():  # сервер падает здесь!
    pass
c.execute("UPDATE balance SET amount = amount + 100 WHERE user_id = 2")
conn.commit()

# Результат: money_lost = 100

С транзакциями (БЕЗОПАСНО):

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()

# ✅ Хорошо: либо обе UPDATE, либо ни одна
try:
    c.execute("BEGIN")
    c.execute("UPDATE balance SET amount = amount - 100 WHERE user_id = 1")
    if server_crashes():  # даже если упадём, ничего не потеряется
        raise Exception()
    c.execute("UPDATE balance SET amount = amount + 100 WHERE user_id = 2")
    c.execute("COMMIT")
except Exception as e:
    c.execute("ROLLBACK")  # откатываем всё
    print(f"Transaction failed: {e}")

С контролем изоляции:

import sqlite3

conn = sqlite3.connect(':memory:')

# Используем SERIALIZABLE для критичной операции
conn.execute("PRAGMA read_uncommitted = 0")
conn.isolation_level = "DEFERRED"  # DEFERRED, IMMEDIATE, EXCLUSIVE

c = conn.cursor()

# DEFERRED: блокировка происходит при первой операции
begin_deferredexec()

try:
    c.execute("UPDATE balance SET amount = amount - 100 WHERE user_id = 1")
    # ... другие операции ...
    c.execute("UPDATE balance SET amount = amount + 100 WHERE user_id = 2")
    conn.commit()
except:
    conn.rollback()

Deadlock и как избежать

-- Транзакция 1 и 2 взаимно блокируют друг друга

-- Транзакция 1
BEGIN;
UPDATE balance SET amount = 100 WHERE user_id = 1;  -- блокирует row 1
-- Ждёт row 2
UPDATE balance SET amount = 100 WHERE user_id = 2;  -- DEADLOCK!

-- Транзакция 2
BEGIN;
UPDATE balance SET amount = 200 WHERE user_id = 2;  -- блокирует row 2
-- Ждёт row 1
UPDATE balance SET amount = 200 WHERE user_id = 1;  -- DEADLOCK!

-- Решение: упорядочить доступ
-- ОБЕИМ транзакциям: сначала row 1, потом row 2

Best Practices

1. Минимизируй время транзакции

   • Вычисления ДО BEGIN
   • Только DB операции в транзакции

2. Используй нужный уровень изоляции

   • Отчёты: READ COMMITTED
   • Критичные операции: SERIALIZABLE
   • Баланс: REPEATABLE READ

3. Обрабатывай deadlock

   • Retry с exponential backoff
   • Логирование

4. Используй SAVEPOINT для контроля

   BEGIN;
   INSERT INTO table1 VALUES (1);
   SAVEPOINT sp1;
   INSERT INTO table2 VALUES (2);  -- может провалиться
   ROLLBACK TO sp1;  -- откатываем только table2
   INSERT INTO table2 VALUES (3);  -- пробуем другое
   COMMIT;  -- table1 и table3 успешно
   

Заключение

• Транзакции = ACID гарантии
• Уровни изоляции = баланс между безопасностью и скоростью
• READ COMMITTED подходит для большинства случаев
• SERIALIZABLE для критичных операций (денежные переводы)
• Всегда обрабатывай ошибки и deadlock