Какие аномалии бывают при параллельных транзакциях и от чего защищает каждый уровень изоляции?

Аномалии:

• грязное чтение — видим неподтверждённые изменения другой транзакции, которая потом откатится;
• неповторяемое чтение — прочитали строку дважды и получили разные значения: между чтениями кто-то закоммитил изменение;
• фантом — повторный запрос с условием вернул другой набор строк: кто-то вставил подходящую строку;
• потерянное обновление — обе транзакции прочитали баланс 100, обе записали своё «прочитанное плюс 10», итог 110 вместо 120;
• асимметрия записи (write skew) — обе транзакции проверили условие по общим данным и изменили разные строки, а вместе нарушили инвариант. Классика: «в смене должен остаться хотя бы один дежурный врач» — двое одновременно видят, что дежурят двое, и оба снимаются.

Уровни и что запрещают:

* в PostgreSQL Read Uncommitted работает как Read Committed. ** стандарт допускает фантомы на Repeatable Read, но снимок PostgreSQL их не показывает; в InnoDB обычный SELECT тоже читает снимок, а блокирующие чтения используют gap-блокировки. *** PostgreSQL на Repeatable Read откатит вторую транзакцию с ошибкой сериализации; InnoDB — нет, обновление потеряется.

Как защищаться на практике (часто без повышения уровня):

• атомарный UPDATE ... SET x = x + 10 вместо «прочитать — записать»;
• SELECT ... FOR UPDATE — блокировка строк, которые будем менять;
• оптимистическая блокировка по версии;
• от write skew — блокировать строки, по которым проверяется условие (FOR UPDATE на всех дежурных), материализовать конфликт отдельной строкой или уровень Serializable;
• на Serializable и Repeatable Read в PostgreSQL — повторять транзакцию при ошибке could not serialize access.

-- write skew на Repeatable Read: обе транзакции успешно закоммитятся
-- T1                                        -- T2
BEGIN ISOLATION LEVEL REPEATABLE READ;       BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors                 SELECT count(*) FROM doctors
 WHERE on_call;            -- 2               WHERE on_call;            -- 2
UPDATE doctors SET on_call = false           UPDATE doctors SET on_call = false
 WHERE name = 'Анна';                         WHERE name = 'Борис';
COMMIT;                                      COMMIT;     -- дежурных 0
-- на SERIALIZABLE одна из транзакций получит ошибку сериализации