Уровни изоляции транзакций: Read Uncommitted → Serializable, аномалии?

#Аномалии

#Уровни и допускаемые аномалии

* зависит от реализации.

#Как реализовано в популярных СУБД

#PostgreSQL (MVCC)

• Read Committed — каждый оператор видит снимок на момент своего начала.
• Repeatable Read — вся транзакция видит снимок на момент первого запроса. Фантомов нет. Если пытаешься изменить строку, изменённую другой зафиксированной транзакцией, — ошибка could not serialize access due to concurrent update.
• Serializable — Serializable Snapshot Isolation (SSI): отслеживает зависимости чтения/записи и откатывает транзакции, которые могли бы дать несериализуемый результат (в том числе write skew). Без блокировок чтения, но с ошибками 40001.
• Read Uncommitted ведёт себя как Read Committed — грязного чтения нет вообще.

#SQL Server

• По умолчанию Read Committed на блокировках: читатели ставят разделяемые блокировки, писатели — эксклюзивные → чтение ждёт незавершённую запись.
• READ_COMMITTED_SNAPSHOT (RCSI) — Read Committed на версиях строк (tempdb): читатели не блокируются. Включено по умолчанию в Azure SQL, в on-premise рекомендуется включать.
• SNAPSHOT — отдельный уровень, аналог Repeatable Read в PostgreSQL.
• Serializable — диапазонные блокировки (key-range locks): блокирует и «пустоты», чтобы не появились фантомы. Больше блокировок и deadlock-ов.
• WITH (NOLOCK) = Read Uncommitted для таблицы — может вернуть незафиксированные, дублированные и пропущенные строки. Не используйте «для скорости».

#Задание уровня

-- PostgreSQL
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
-- ...
COMMIT;

var strategy = db.Database.CreateExecutionStrategy();       // Npgsql: EnableRetryOnFailure повторяет 40001
await strategy.ExecuteAsync(async () =>
{
    db.ChangeTracker.Clear();
    await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable, ct);

    var onCall = await db.Doctors.CountAsync(d => d.ShiftId == shiftId && d.OnCall, ct);
    if (onCall < 2) throw new BusinessRuleException("Нельзя уйти: останется меньше одного дежурного");

    var me = await db.Doctors.SingleAsync(d => d.Id == doctorId, ct);
    me.OnCall = false;
    await db.SaveChangesAsync(ct);
    await tx.CommitAsync(ct);
});

#Как выбирать

• Большинство приложений живут на Read Committed + optimistic concurrency для сущностей.
• Для инвариантов на несколько строк — Serializable с повторами, или материализация конфликта: блокировка «родительской» строки (SELECT ... FROM shifts WHERE id = @id FOR UPDATE), уникальный индекс/исключающее ограничение (EXCLUDE в PostgreSQL для пересечений интервалов).

#Вопросы на засыпку

#Итог

Уровни изоляции — компромисс между корректностью и параллелизмом. Знайте аномалии (dirty, non-repeatable, phantom, lost update, write skew), знайте уровень по умолчанию своей СУБД и то, как он реализован (блокировки или MVCC). Большинству задач хватает Read Committed с optimistic concurrency, а многострочные инварианты требуют Serializable с повторами или явных блокировок.