Почему при повторном чтении может происходить перекос записи?

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

Многие базы данных, такие как Postgres, SQLServer с уровнем изоляции повторяемого чтения, могут обнаруживать потерянное обновление ( особый случай перекоса записи), но другие этого не делают. (т.е. движок InnoDB в MySQL)

Мы вернулись к написанию проблемы явлений перекоса. Существуют ситуации, которые большинство движков баз данных не могут обнаружить в изоляции повторяемого чтения. Один случай - когда 2 одновременные транзакции изменяют 2 разных объекта и создают условия гонки.

Я беру пример из книги Разработка приложений с интенсивным использованием данных. Вот сценарий:

Вы пишете заявление для врачей, чтобы они могли работать дежурными по вызову в больнице. В больнице обычно стараются вызвать сразу нескольких врачей, но в ней обязательно должен быть хотя бы один дежурный врач. Врачи могут отказаться от смены (например, если они сами заболели), при условии, что в этой смене дежурит хотя бы один коллега.

Следующий интересный вопрос - как это реализовать в базах данных. Вот псевдокод SQL-кода:

BEGIN TRANSACTION;
    SELECT * FROM doctors
        WHERE on_call = true
        AND shift_id = 1234;
    if (current_on_call >= 2) {
        UPDATE doctors
        SET on_call = false WHERE name = 'Alice' AND shift_id = 1234;
    }
COMMIT;  

Вот иллюстрация:

Как показано на приведенной выше иллюстрации, Боб и Алиса одновременно работают над кодом SQL. Однако Боб и Алиса изменяли разные данные, Боб изменил запись Боба, а Алиса изменила запись Алисы. Базы данных на уровне изоляции повторяемого чтения никоим образом не могут узнать и проверить, что условие (общее число врачей> = 2) было нарушено. Произошло явление перекоса записи.

Для решения этой проблемы предлагается 2 метода:

1. блокирует все записи, которые вызываются вручную. Таким образом, либо Боб, либо Алиса будут ждать, пока другой закончит транзакцию.

Вот псевдокод с использованием SELECT .. FOR UPDATE запроса.

BEGIN TRANSACTION;
    SELECT * FROM doctors
        WHERE on_call = true
        AND shift_id = 1234 FOR UPDATE; // important here: locks all records that satisfied requirements.

    if (current_on_call >= 2) {
        UPDATE doctors
        SET on_call = false WHERE name = 'Alice' AND shift_id = 1234;
    }
  COMMIT;  

2. Использование более строгого уровня изоляции. Оба MySQL, Postgres T-SQL обеспечивает уровень изоляции сериализации .