Какие аномалии возможны на READ COMMITTED в PostgreSQL?

Какие аномалии возможны на READ…: ответ для собеседования Java

Сначала ответь вслух

Скажи ответ вслух хотя бы пару минут, потом открой эталон и сравни. Эталон открыт всем и не требует входа.

Показать ответ

Эталонный ответ

На READ COMMITTED каждый оператор видит данные, закоммиченные до его начала. Два одинаковых SELECT в одной транзакции могут вернуть разное: между ними закоммитилась чужая транзакция.

Самая дорогая на практике аномалия — потерянное обновление: приложение читает остаток, считает новое значение и записывает его. Две такие транзакции перезапишут друг друга.

-- Обе транзакции прочитали 100 и записали 50: одно списание потеряно
SELECT balance FROM account WHERE id = 1;
UPDATE account SET balance = 50 WHERE id = 1;

-- Атомарное изменение: база пересчитает от свежей версии строки
UPDATE account SET balance = balance - 50
WHERE id = 1 AND balance >= 50;

Кроме атомарного UPDATE помогают SELECT ... FOR UPDATE и более строгие уровни изоляции с повтором транзакции.

Как у тебя получилось?

Уточняющие вопросы

Так обычно копают глубже. Раскрой строку, когда ответишь вслух.

Уточнение 1-го уровня: Что будет на REPEATABLE READ при конкурентном UPDATE той же строки?
Ответ уровня Middle
Вторая транзакция дождётся первой и получит ошибку could not serialize access due to concurrent update (SQLSTATE 40001). Транзакцию нужно повторить целиком.
1. Уточнение 2-го уровня: Что такое перекос записи и спасает ли от него REPEATABLE READ?
  Ответ уровня Senior
  Две транзакции читают пересекающиеся данные и меняют разные строки, нарушая общее правило: например, оба дежурных врача снимаются с дежурства. REPEATABLE READ в PostgreSQL этого не ловит, SERIALIZABLE ловит.
  1. Уточнение 3-го уровня: Почему SERIALIZABLE требует повторов в приложении?
    Ответ уровня Senior
    PostgreSQL не блокирует заранее, а отслеживает опасные зависимости и отменяет одну из транзакций с ошибкой 40001. Повторить её может только приложение.

Типичные ошибки

Говорить, что на READ COMMITTED возможно грязное чтение в PostgreSQL.
Считать остаток в приложении и записывать готовое значение.

Первоисточники

PostgreSQL 16: изоляция транзакций, откроется в новой вкладке

Быстрые задания по теме

Для темы «Транзакции и изоляция» быстрых заданий пока нет

Можно проверить всю зону «SQL и транзакции» тестом на 10 минут.

Проверить себя за 10 минут

Эталонный ответ

-- Обе транзакции прочитали 100 и записали 50: одно списание потеряно
SELECT balance FROM account WHERE id = 1;
UPDATE account SET balance = 50 WHERE id = 1;

-- Атомарное изменение: база пересчитает от свежей версии строки
UPDATE account SET balance = balance - 50
WHERE id = 1 AND balance >= 50;

Кроме атомарного UPDATE помогают SELECT ... FOR UPDATE и более строгие уровни изоляции с повтором транзакции.

Как у тебя получилось?

Уточняющие вопросы

Так обычно копают глубже. Раскрой строку, когда ответишь вслух.

Уточнение 1-го уровня: Что будет на REPEATABLE READ при конкурентном UPDATE той же строки?

Ответ уровня Middle

Вторая транзакция дождётся первой и получит ошибку could not serialize access due to concurrent update (SQLSTATE 40001). Транзакцию нужно повторить целиком.

Уточнение 2-го уровня: Что такое перекос записи и спасает ли от него REPEATABLE READ?
Ответ уровня Senior
Две транзакции читают пересекающиеся данные и меняют разные строки, нарушая общее правило: например, оба дежурных врача снимаются с дежурства. REPEATABLE READ в PostgreSQL этого не ловит, SERIALIZABLE ловит.
1. Уточнение 3-го уровня: Почему SERIALIZABLE требует повторов в приложении?
  Ответ уровня Senior
  PostgreSQL не блокирует заранее, а отслеживает опасные зависимости и отменяет одну из транзакций с ошибкой 40001. Повторить её может только приложение.