Вопрос по mysql, sql, postgresql, database – Повлияет ли обновление SQL на его подзапрос во время запуска обновления?

8

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

<code>update table join
    (select y, min(x) as MinX 
     from table
     group by y) as t1
    using (y)
set x = x - MinX
</code>

Это означает, что переменнаяx обновляется на основе подзапроса, который также обрабатывает переменнуюx - ноcouldn't this x already be modified by the running update command? Разве это не проблема? Я имею в виду, что в обычном программировании вы обычно должны обрабатывать это явно, то есть сохранять новое значение в каком-то другом месте из старого значения, а после завершения работы заменить старое значение новым ... ноhow will SQL database do this?

Меня не интересует ни одно наблюдение или эксперимент. Я хотел бы иметь фрагмент из стандарта docs или sql, который скажет, каково определенное поведение в этом случае. Я использую MySQL, но ответы действительны и для других PostgresQL, Oracle и т. Д., И особенноfor SQL standard in general ценятся. Спасибо!

@MarcusAdams, из той же таблицы - считайте это последним SQL-запросом. Использование InnoDB, но чем больше общего ответа (engine -> mysql -> SQL), тем лучше. TMS
Какой механизм базы данных вы выбираете из той же таблицы, что и обновляемый? Marcus Adams

Ваш Ответ

4   ответа
3

** Edited **

Selecting from the target table

От13.2.9.8. Подзапросы в предложении FROM:

Subqueries in the FROM clause can return a scalar, column, row, or table. Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON clause of a JOIN operation.

Итак, да, вы можете выполнить вышеуказанный запрос.

The problem

Здесь действительно две проблемы. Это параллелизм или гарантия того, что никто не изменит данные из-под наших ног. Это обрабатывается с блокировкой. Работа с фактическим изменением новых и старых значений обрабатывается с помощью производных таблиц.

Locking

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

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

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

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

блокировка записи не позволяет другим запросам получить блокировку на чтение или запись.

Ты можешь использоватьInnotop чтобы увидеть это, запустив его в режиме блокировки, запустите транзакцию, выполните запрос (но не фиксируйте его), и вы увидите блокировки в Innotop. Кроме того, вы можете просмотреть детали без Innotop сSHOW ENGINE INNODB STATUS.

Deadlocks

Ваш запрос уязвим для тупика, если два экземпляра были запущены одновременно. Если запрос A получил блокировки чтения, тогда запрос B получил блокировки чтения, запрос A должен был бы ждать, пока блокировки запроса B освободятся, прежде чем он сможет получить блокировки записи. Однако запрос B не собирается освобождать блокировки чтения до тех пор, пока он не завершится, и он не завершит работу, если не сможет получить блокировки записи. Запрос A и запрос B находятся в тупике, а значит, в тупике.

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

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

Derived tables

Для внутреннего SELECT MySQL создаетпроизводная временная таблица, Производная таблица представляет собой фактическую неиндексированную копию данных, которые хранятся во временной таблице, которая автоматически создается MySQL (в отличие от временной таблицы, которую вы явно создаете и можете добавлять индексы).

Поскольку MySQL использует производную таблицу, это временное старое значение, на которое вы ссылаетесь в своем вопросе. Другими словами, здесь нет никакой магии. MySQL делает это так же, как вы делаете это где-то еще, с временным значением.

Вы можете увидеть производную таблицу, выполнив EXPLAIN для вашего оператора UPDATE (поддерживается в MySQL 5.6+).

Error: User Rate Limit Exceededwhat is the defined behaviour for subqueries of the update statement? TMS
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit ExceededdefinedError: User Rate Limit Exceeded TMS
"MySQL doesn't let you select from the same table that you're trying to update"Error: User Rate Limit Exceeded TMS
2

statement level read consistency, который гарантирует, что оператор видит (выбирает) данные такими, какими они были на момент начала оператора. Таким образом, сценарий, которого вы боитесь, не произойдет.

С Уважением,
Роб.

0

Хотя уже отмечалось, что вы ДОЛЖНЫ иметь возможность обновлять таблицу на основе ее собственных данных, вы должны иметь возможность настроить синтаксис MySQL, чтобы разрешить его через

update Table1, 
       (select T2.y, MIN( T2.x ) as MinX from Table1 T2 group by T2.y ) PreQuery
  set Table1.x = Table1.x - PreQuery.MinX
  where Table1.y = PreQuery.y

Я не знаю, идет ли синтаксис другим путем, используя JOIN по сравнению с версией списка запятых, но по завершенному предварительному запросу вы должны были бы сначала применить его к завершенному результату ONCE, и объединить (через WHERE), чтобы фактически выполнить Обновить.

1) "Although its been noted you SHOULDN'T be able to do an update to a table based on its own data"Error: User Rate Limit Exceeded TMS
Error: User Rate Limit Exceeded TMS
Error: User Rate Limit Exceeded
1

Документация

A consistent result set is provided for every query, guaranteeing data consistency, with no action by the user. An implicit query, such as a query implied by a WHERE clause in an UPDATE statement, is guaranteed a consistent set of results. However, each statement in an implicit query does not see the changes made by the DML statement itself, but sees the data as it existed before changes were made.

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