Как защититься от повторных чтений в базе данных

Что проверяют

Понимаете ли вы аномалию неповторяемого чтения и знаете ли, какими средствами базы и ORM её убирают. Следом обычно спрашивают, чем она отличается от фантомного чтения и что делать с ошибкой сериализации.

Как отвечать

Сначала определение: транзакция дважды читает одну строку и получает разные значения, потому что между чтениями другая транзакция изменила строку и закоммитила. На уровне Read Committed, который по умолчанию стоит в PostgreSQL, это нормальное поведение. В MySQL InnoDB по умолчанию Repeatable Read.

Способы защиты:

• уровень Repeatable Read: в PostgreSQL транзакция видит один снимок, взятый при первом запросе, и повторное чтение вернёт то же самое. Если она попробует изменить строку, которую уже поменял кто-то другой, получит ошибку сериализации (SQLSTATE 40001), и приложение обязано повторить транзакцию целиком;
• пессимистичная блокировка: SELECT ... FOR UPDATE или FOR SHARE держит прочитанные строки до конца транзакции. В Django это select_for_update() внутри transaction.atomic(), в SQLAlchemy with_for_update();
• оптимистичная блокировка: колонка version и UPDATE ... SET version = version + 1 WHERE id = %s AND version = %s. Ноль обновлённых строк значит, что данные успели поменять. В SQLAlchemy это встроено через version_id_col и StaleDataError;
• прочитать один раз: иногда проще сохранить значение в переменной, чем читать повторно.

Уровень изоляции в Python задаётся на соединении: create_engine(url, isolation_level="REPEATABLE READ") в SQLAlchemy, conn.isolation_level в psycopg 3, OPTIONS в DATABASES у Django.

Где спотыкаются

Пишут SET TRANSACTION ISOLATION LEVEL до BEGIN. В PostgreSQL команда действует только внутри транзакции, до неё она ничего не меняет, и запрос выполнится на Read Committed.

Ставят Serializable «на всякий случай» и не пишут повтор транзакций. Под нагрузкой начинают сыпаться ошибки 40001, и пользователи видят 500.

Путают аномалии. Неповторяемое чтение это изменение уже прочитанной строки, фантом это появление новых строк под тем же условием WHERE, грязное чтение это чтение незакоммиченных данных, которого в PostgreSQL не бывает вовсе.