Вопрос по transactions, sql, mysql, sql-server, select-for-update – Когда использовать SELECT… ДЛЯ ОБНОВЛЕНИЯ?

90

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

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

Дано:

rooms[id] tags[id, name] room_tags[room_id, tag_id] room_id and tag_id are foreign keys

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

Initially: rooms contains [id = 1] tags contains [id = 1, name = 'cats'] room_tags contains [room_id = 1, tag_id = 1] Thread 1: SELECT id FROM rooms; returns [id = 1] Thread 2: DELETE FROM room_tags WHERE room_id = 1; Thread 2: DELETE FROM rooms WHERE id = 1; Thread 2: [commits the transaction] Thread 1: SELECT tags.name FROM room_tags, tags WHERE room_tags.tag_id = 1 AND tags.id = room_tags.tag_id; returns an empty list

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

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

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

Какую СУБД вы используете? Quassnoi
@Quassnoi, как уже упоминалось в нижней части вопроса, я ищу портативное (не специфичное для базы данных) решение. Gili
select ... for update наrooms все еще позволитroom_tags должны быть удалены, потому что они являются отдельными таблицами. Вы хотели спросить,for update пункт будет препятствовать удалению изrooms? Chris Saxon
@BillyONeal: обратите внимание, что режимы изоляции гарантируют, что вы не видите причуд, которые они не разрешают, но ничего не говорите о причудах, которые они допускают. Это означает, что установка, скажем,READ COMMITTED Режим не определяет, будете ли вы на самом деле видеть записи, совершенные другой транзакцией: он только гарантирует, что вы никогда не увидите незафиксированные записи. Quassnoi
Есть вариантыREPEATABLE_READ а такжеREAD_COMMITTED даже портативные варианты? Единственные результаты, которые я получаю для тех, это для сервера MSSQL Billy ONeal

Ваш Ответ

2   ответа
20

Q1: да.

Q2: не имеет значения, какой вы используете.

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

select ... for update будет (как это подразумевается) выбирать определенные строки, а также блокировать их, как если бы они уже были обновлены текущей транзакцией (или как если бы обновление идентификатора было выполнено). Это позволяет вам обновить их снова в текущей транзакции, а затем зафиксировать,without another transaction being able to modify these rows in any way.

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

select * from my_table where my_condition;

update my_table set my_column = my_column where my_condition;

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

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

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

Я думаюWhat transaction isolation levels do guarantee [...] is the consistency of data once transactions are completed. неправильно подразумевает, что уровни изоляции не влияют на то, что происходитduring сделка Я рекомендую пересмотреть этот раздел и предоставить более подробную информацию о том, как они влияют на то, что вы видите (или не видите) во время транзакции. Gili
Блокировка и изоляция взаимозаменяемо сложны. Так есть ли книги, чтобы получить знания об этом?
Я считаю, что ваш пост отвечает на мои конкретные вопросы лучше, чемQuassnoi's но я ценю все ссылки, которые он предоставил. Я приму ответ, который лучше всего сочетает эти два вопроса (конкретные ответы сверху, вспомогательные ссылки ниже). Gili
64

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

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

To solve this problem, Thread 1 should SELECT id FROM rooms FOR UPDATE, thereby preventing Thread 2 from deleting from rooms until Thread 1 is done. Is that correct?

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

MyISAM in MySQL (and several other old systems) does lock the whole table for the duration of a query.

In SQL Server, SELECT queries place shared locks on the records / pages / tables they have examined, while DML queries place update locks (which later get promoted to exclusive or demoted to shared locks). Exclusive locks are incompatible with shared locks, so either SELECT or DELETE query will lock until another session commits.

In databases which use MVCC (like Oracle, PostgreSQL, MySQL with InnoDB), a DML query creates a copy of the record (in one or another way) and generally readers do not block writers and vice versa. For these databases, a SELECT FOR UPDATE would come handy: it would lock either SELECT or the DELETE query until another session commits, just as SQL Server does.

When should one use REPEATABLE_READ transaction isolation versus READ_COMMITTED with SELECT ... FOR UPDATE?

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

In Oracle and earlier PostgreSQL versions, REPEATABLE READ is actually a synonym for SERIALIZABLE. Basically, this means that the transaction does not see changes made after it has started. So in this setup, the last Thread 1 query will return the room as if it has never been deleted (which may or may not be what you wanted). If you don't want to show the rooms after they have been deleted, you should lock the rows with SELECT FOR UPDATE

In InnoDB, REPEATABLE READ and SERIALIZABLE are different things: readers in SERIALIZABLE mode set next-key locks on the records they evaluate, effectively preventing the concurrent DML on them. So you don't need a SELECT FOR UPDATE in serializable mode, but do need them in REPEATABLE READ or READ COMMITED.

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

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

This depends on the concurrency control your database system is usingЯ думаю, что вы раскалываете волосы. Все перечисленные ниже случаи говорят о том, что комната не была удалена междуSELECT до конца сделки. Таким образом, ответ не должен быть простоYes с подтверждающими ссылками ниже? Gili
Вы правы. Второй вопрос должен был задать, когдаSERIALIZABLE следует использовать противREAD_COMMITTED сSELECT ... FOR UPDATE, Можете ли вы обновить свой ответ, чтобы отразить этот обновленный вопрос? Gili
Последний пункт - суть вопроса, я думаю: «вам не нужен SELECT FOR UPDATE в сериализуемом режиме, но они нужны в REPEATABLE READ или READ COMMITED».
@Gili: "Вы не нуждаетесь вSELECT FOR UPDATE в сериализуемом режиме & quot ;, сInnoDB, С другойMVCC системы, это синонимы, и вам нужноSELECT FOR UPDATE.
Я нахожу Колинаpost отвечает на мои конкретные вопросы лучше, чем ваш ответ, но я ценю все предоставленные вами ссылки. Я приму ответ, который лучше всего сочетает эти два вопроса (конкретные ответы сверху, вспомогательные ссылки ниже). Gili

Похожие вопросы