Когда использовать SELECT... FOR UPDATE?

Пожалуйста, помогите мне понять прецедент за SELECT ... FOR UPDATE.

Вопрос 1: Является ли следующий пример хорошим, когда SELECT ... FOR UPDATE следует использовать?

Дано:

  • номера [ID]
  • теги [id, name]
  • room_tags [room_id, tag_id]
    • room_id и tag_id являются внешними ключами

Приложение хочет перечислить все номера и их теги, но необходимо различать номера без тегов и удаленных номеров. Если SELECT... FOR UPDATE не используется, может произойти следующее:

  • Первоначально:
    • номера содержат [id = 1]
    • содержит [id = 1, name = 'cats']
    • room_tags содержит [room_id = 1, tag_id = 1]
  • Тема 1: SELECT id FROM rooms;
    • returns [id = 1]
  • Тема 2: DELETE FROM room_tags WHERE room_id = 1;
  • Тема 2: DELETE FROM rooms WHERE id = 1;
  • Тема 2: [совершает транзакцию]
  • Тема 1: SELECT tags.name FROM room_tags, tags WHERE room_tags.tag_id = 1 AND tags.id = room_tags.tag_id;
    • возвращает пустой список

Теперь Thread 1 считает, что в комнате 1 нет тегов, но на самом деле комната была удалена. Чтобы решить эту проблему, Thread 1 должен SELECT id FROM rooms FOR UPDATE, тем самым предотвращая удаление Thread 2 с rooms до тех пор, пока не будет завершен Thread 1. Это правильно?

Вопрос 2. Когда следует использовать транзакционную изоляцию SERIALIZABLE по сравнению с READ_COMMITTED с SELECT ... FOR UPDATE?

Ожидается, что ответы будут переносимыми (не специфичными для конкретной базы данных). Если это невозможно, объясните, почему.

Ответы

Ответ 1

Единственный переносимый способ добиться согласованности между комнатами и тегами и убедиться, что комнаты никогда не возвращаются после того, как они были удалены, блокирует их с помощью SELECT FOR UPDATE.

Однако в некоторых системах блокировка является побочным эффектом управления concurrency, и вы достигаете тех же результатов, не указав явно FOR UPDATE.

Чтобы решить эту проблему, Thread 1 должен SELECT id FROM rooms FOR UPDATE, тем самым предотвращая удаление Thread 2 с rooms до тех пор, пока не будет завершен Thread 1. Это правильно?

Это зависит от управления concurrency, используемого вашей базой данных.

  • MyISAM в MySQL (и нескольких других старых системах) блокирует всю таблицу в течение всего времени запроса.

  • В SQL Server запросы SELECT размещают разделяемые блокировки в записях/страницах/таблицах, которые они исследовали, в то время как DML запросы помещают блокировки обновлений (которые впоследствии получают повышение до исключительных или пониженных до общих блокировок), Исключительные блокировки несовместимы с общими блокировками, поэтому запрос SELECT или DELETE блокируется до тех пор, пока не завершится другой сеанс.

  • В базах данных, которые используют MVCC (например, Oracle, PostgreSQL, MySQL с InnoDB), запрос DML создает копию записи (тем или иным способом) и в целом читатели не блокируют писателей и наоборот. Для этих баз данных пригодится SELECT FOR UPDATE: он блокирует либо запрос SELECT, либо DELETE до тех пор, пока не завершится другой сеанс, как это делает SQL Server.

Когда следует использовать REPEATABLE_READ изоляцию транзакции по сравнению с READ_COMMITTED с помощью SELECT ... FOR UPDATE?

Как правило, REPEATABLE READ не запрещает строки phantom (строки, которые появились или исчезли в другой транзакции, а не были изменены)

  • В версиях Oracle и более ранних PostgreSQL REPEATABLE READ на самом деле является синонимом SERIALIZABLE. В основном это означает, что транзакция не видит изменений, сделанных после ее запуска. Поэтому в этой настройке последний Thread 1 запрос возвращает комнату, как если бы она никогда не удалялась (что может быть или не быть тем, что вы хотели). Если вы не хотите показывать комнаты после их удаления, вы должны заблокировать строки с помощью SELECT FOR UPDATE

  • В InnoDB, REPEATABLE READ и SERIALIZABLE - разные вещи: читатели в режиме SERIALIZABLE устанавливают блокировки следующих клавиш в записях, которые они оценивают, эффективно предотвращая параллельное DML на них. Таким образом, вам не нужен SELECT FOR UPDATE в сериализуемом режиме, но они нуждаются в них в REPEATABLE READ или READ COMMITED.

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

Когда я говорю "вам не нужно SELECT FOR UPDATE" я действительно должен был добавить "из-за побочных эффектов определенной реализации ядра базы данных".

Ответ 2

Краткие ответы:

Q1: Да.

Q2: Не важно, что вы используете.

Длинный ответ:

A select ... for update будет (как это подразумевается) выбирать определенные строки, но также блокировать их, как если бы они уже были обновлены текущей транзакцией (или, как если бы обновление было выполнено). Это позволяет вам обновлять их снова в текущей транзакции и затем совершать, без использования другой транзакции, чтобы каким-либо образом изменить эти строки.

Другой способ взглянуть на это, это как если бы следующие два утверждения выполнялись атомарно:

select * from my_table where my_condition;

update my_table set my_column = my_column where my_condition;

Поскольку строки, затронутые my_condition, заблокированы, никакая другая транзакция не может каким-либо образом их модифицировать, и, следовательно, уровень изоляции транзакций здесь не имеет значения.

Обратите внимание, что уровень изоляции транзакции не зависит от блокировки: установка другого уровня изоляции не позволяет обойти блокировку и обновлять строки в другой транзакции, заблокированной вашей транзакцией.

Какие уровни изоляции транзакций гарантируют (на разных уровнях) согласованность данных во время транзакций.