Коротко — сначала что и где возникает, потом примеры и способы исправить.
Когда возникает lost update и неповторяемое чтение
- Lost update (потеря обновления) возможна при уровнях изоляции, где чтение и последующее обновление не блокируют строку для других писателей: например Read Uncommitted и Read Committed (и даже Repeatable Read в MVCC без явных блокировок). Serializable и/или использование блокировок при чтении (SELECT ... FOR UPDATE) предотвращают lost update.
- Non‑repeatable read (неповторяемое чтение) происходит при Read Uncommitted и Read Committed: транзакция T1 дважды читает одну строку и получает разные значения из‑за коммита T2 между чтениями. Repeatable Read и Serializable предотвращают неповторяемость чтений.
Пример последовательности, приводящей к lost update
1) T1: BEGIN;
2) T1: SELECT balance FROM accounts WHERE id=$1$; -- прочитал $1000$
3) T2: BEGIN;
4) T2: SELECT balance FROM accounts WHERE id=$1$; -- прочитал $1000$
5) T1: UPDATE accounts SET balance=$900$ WHERE id=$1$; COMMIT; -- уменьшил на $100$
6) T2: UPDATE accounts SET balance=$800$ WHERE id=$1$; COMMIT; -- уменьшил на $200$, итог $800$ — изменение T1 потеряно
(Этот interleaving возможен при Read Committed / MVCC без FOR UPDATE.)
Пример неповторяемого чтения
1) T1: BEGIN;
2) T1: SELECT balance FROM accounts WHERE id=$1$; -- прочитал $1000$
3) T2: BEGIN; UPDATE accounts SET balance=$900$ WHERE id=$1$; COMMIT;
4) T1: SELECT balance FROM accounts WHERE id=$1$; -- теперь прочитал $900$ — неповторяемое чтение
Как гарантировать корректность и приёмлемую производительность
1) Делать изменение атомарно в СУБД (лучше всего):
- Выполнить один SQL: UPDATE accounts SET balance = balance - $100$ WHERE id = $1$;
Это устраняет стадию «read → compute → write» в клиенте и предотвращает lost update.
2) Пессимистические блокировки (если нужна логика «прочитал — решил — потом обновил»):
- В транзакции использовать SELECT ... FOR UPDATE (или SELECT ... FOR SHARE/LOCK IN SHARE MODE в зависимости от СУБД) при Read Committed или выше. Это захватит эксклюзивную (или разделяемую) блокировку на строку и не даст другому писателю читать/писать конфликтно. Комбинируйте с индексом по id (обычно PK) для локальных блокировок.
3) Оптимистическая конкуренция (для высокой конкуренции и производительности):
- Ввести столбец version или использовать контроль по old_balance:
UPDATE accounts SET balance = $new\_balance$, version = version + $1$ WHERE id = $1$ AND version = $old\_version$;
Проверять affected_rows; при $0$ — откат/повтор попытки. Это предотвращает lost update без блокировок, но требует логики повторных попыток.
4) Уровни изоляции:
- Для минимизации проблем при обычных операциях по одному ряду: Read Committed + atomic UPDATE (или SELECT FOR UPDATE) — хороший компромисс.
- Repeatable Read защищает от неповторяемых чтений, но не обязателен, если вы используете атомарные UPDATE или FOR UPDATE.
- Serializable даёт максимальную гарантию, но значительно дороже по производительности; использовать лишь при необходимости строгой корректности для сложных транзакций.
5) Индексирование и блокировки:
- Наличие индекса по id (PK) гарантирует, что поиск/блокировка затронут минимальный набор строк (row locks вместо table locks).
- В InnoDB/MySQL использовать кластерный индекс/PK — блокировки будут по нужной записи; при сканах диапазонов смотреть на gap locks/next‑key locks (влияют на фантомы).
Рекомендация практическая
- Для операции "снять N с баланса": делать UPDATE accounts SET balance = balance - $N$ WHERE id = $1$ AND balance >= $N$; проверить affected_rows и, при необходимости, вернуть новое значение через RETURNING/SELECT. Это атомарно, быстро и безопасно при высокой конкуренции. При сложной бизнес‑логике (много проверок) — внутри транзакции делать SELECT ... FOR UPDATE, затем обновление, либо использовать оптимистическую схему с version и retry.