Кратко — примерные последовательности и рекомендации.
Неповторяемое чтение (READ COMMITTED допускает):
Изначально поле balance = $100$.
$1$ T1: BEGIN; SELECT balance FROM accounts WHERE id=1; — читает $100$
$2$ T2: BEGIN; UPDATE accounts SET balance = balance - 30 WHERE id=1; COMMIT; — теперь $70$
$3$ T1: SELECT balance FROM accounts WHERE id=1; — читает $70$ (отличается от первого чтения)
$4$ T1: COMMIT.
Потеря обновления (lost update):
Изначально balance = $100$, оба транзакции вычитают по $10$.
$1$ T1: BEGIN; SELECT balance FROM accounts WHERE id=1; — читает $100$
$2$ T2: BEGIN; SELECT balance FROM accounts WHERE id=1; — читает $100$
$3$ T1: UPDATE accounts SET balance = $100-10=90$ WHERE id=1; COMMIT; — пишет $90$
$4$ T2: UPDATE accounts SET balance = $100-10=90$ WHERE id=1; COMMIT; — перезаписывает $90$ (эффект: только $-10$, а не $-20$)
Коротко про уровни изоляции и механизмы — что лучше для OLTP и для аналитики:
- OLTP (много коротких конкурентных транзакций):
- Предпочтение: MVCC или блокировки на уровне строк плюс короткие транзакции. MVCC (snapshot reads) даёт невысокую задержку для чтений; для защищённых изменений использовать явные блокировки (SELECT ... FOR UPDATE) или оптимистичную проверку (compare-and-swap / проверка версии).
- Изоляция: часто Read Committed или Snapshot Isolation для производительности; для критической корректности — Repeatable Read или Serializable (или SSI в PostgreSQL), но сериализация дороже по производительности.
- Механизмы: row-level locks при высокой конкуренции для предотвращения lost updates; MVCC + retry для высокой конкурентности без блокировки чтений.
- Аналитика (долгие читательские запросы, OLAP):
- Предпочтение: MVCC с консистентным снимком (snapshot) либо отдельные читающие реплики/OLAP-репозитории. Это позволяет долгим запросам не блокировать писателей.
- Изоляция: snapshot / repeatable-read для консистентного вида данных; read-only serializable при необходимости строгой корректности.
- Механизмы: избегать блокировок писателей, использовать копии данных/ETL, колонковые хранилища и MPP.
Короткое резюме: для OLTP — MVCC + явные блокировки или оптимистичная проверка, иногда Serializable для критичных операций; для аналитики — MVCC snapshot / read replicas (чтобы не блокировать писателей). Serializability полно предотвращает все аномалии, но дорого; Snapshot Isolation предотвращает dirty/non-repeatable reads, но может позволить write skew.