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

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

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

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

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

• 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 не запрещает фантомные строки (строки, которые появились или исчезли в другой транзакции, а не были изменены)

• В 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», мне действительно следовало добавить «из-за побочных эффектов определенной реализации механизма базы данных».

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

Q1: Да.

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

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

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

С другой стороны, следующие два оператора выполняются атомарно:

select * from my_table where my_condition;

update my_table set my_column = my_column where my_condition;

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

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

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