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

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

Транзакции

Транзакция — это логическая единица работы с данными, которая обладает свойствами ACID:

• Atomicity (Атомарность) — все операции транзакции выполняются как единое целое. При сбое изменения откатываются полностью.
• Consistency (Согласованность) — транзакция переводит базу из одного согласованного состояния в другое.
• Isolation (Изоляция) — параллельные транзакции не должны влиять друг на друга.
• Durability (Долговечность) — после фиксации изменения сохраняются даже при сбое.

Проблемы параллельного доступа

При одновременном выполнении транзакций возникают классические аномалии:

1. Dirty Read (Грязное чтение) — чтение данных, изменённых незафиксированной транзакцией.
2. Non-Repeatable Read (Неповторяющееся чтение) — повторное чтение тех же данных даёт другой результат из-за изменений другой транзакции.
3. Phantom Read (Фантомное чтение) — при повторном выполнении запроса появляются новые строки, добавленные другой транзакцией.
4. Lost Update (Потерянное обновление) — результат одной транзакции перезаписывается другой.

Уровни изоляции (SQL стандарт)

1. Read Uncommitted

• Разрешены все аномалии
• Транзакция видит незафиксированные изменения других транзакций
• Практически не используется из-за риска грязного чтения

2. Read Committed (дефолт в PostgreSQL, SQL Server)

• Защита от dirty read
• Non-repeatable read и phantom read возможны
• Каждый запрос видит только зафиксированные данные на момент его начала

3. Repeatable Read (дефолт в MySQL/InnoDB)

• Защита от dirty read и non-repeatable read
• Phantom read возможны (но не во всех СУБД)
• Гарантирует, что повторное чтение тех же строк даст тот же результат

4. Serializable

• Полная изоляция
• Все аномалии исключены
• Транзакции выполняются так, как если бы они шли последовательно
• Максимальные накладные расходы и риск deadlock'ов

Реализация в популярных СУБД

PostgreSQL:

• Read Uncommitted работает как Read Committed
• Repeatable Read защищает от phantom read через snapshot isolation
• Serializable использует Serializable Snapshot Isolation (SSI)

MySQL (InnoDB):

• Repeatable Read — дефолтный уровень
• Использует MVCC + gap locks для защиты от phantom read
• Read Committed не использует gap locks (меньше блокировок)

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

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

   • Read Committed — хороший баланс для большинства приложений
   • Repeatable Read — когда важна консистентность чтения в рамках транзакции
   • Serializable — для финансовых операций, где критична точность

2. В Go:

// Пример установки уровня изоляции в Go с database/sql
tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
})

3. Типичные ошибки:

   • Использование Repeatable Read без понимания, что он не решает все проблемы конкурентности
   • Ожидание, что Read Committed защитит от неповторяющегося чтения
   • Игнорирование deadlock'ов при высоком уровне изоляции
   • Слишком длинные транзакции — блокируют ресурсы и снижают производительность

4. Оптимизация:

   • Держите транзакции короткими
   • Используйте оптимистичные блокировки (версионирование) вместо пессимистичных
   • При высоком уровне изоляции следите за временем выполнения запросов
   • В PostgreSQL используйте SELECT ... FOR UPDATE для явной блокировки строк