ВЫБРАТЬ... ДЛЯ ОБНОВЛЕНИЯ из одной таблицы в нескольких потоках

Мне нужна небольшая помощь с SELECT FOR UPDATE (соотв. LOCK IN SHARE MODE )

У меня есть таблица с 400 000 записей, и мне нужно запустить две разные функции обработки в каждой строке.

Структура таблицы соответственно такая:

data (
    `id`,
    `mtime`,  -- When was data1 set last
    `data1`,
    `data2` DEFAULT NULL,
    `priority1`,
    `priority2`,
    PRIMARY KEY `id`,
    INDEX (`mtime`),
    FOREIGN KEY ON `data2`
)

Функции немного разные:

  • первая функция - должна запускаться в цикле на всех записях (довольно быстро), должна выбирать записи на основе priority1; наборы data1 а также mtime
  • вторая функция - должна запускаться только один раз для каждой записи (довольно медленно), должна выбирать записи на основе priority2; наборы data1 а также mtime

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

Я выбираю данные на основе следующих запросов:

-- For the first function - not processed first, then the oldest,
-- the same age goes based on priority
SELECT id FROM data ORDER BY mtime IS NULL DESC, mtime, priority1 LIMIT 250 FOR UPDATE;

-- For the second function - only processed not processed order by priority
SELECT if FROM data ORDER BY priority2 WHERE data2 IS NULL LIMIT 50 FOR UPDATE;

Но что я испытываю, так это то, что каждый раз возвращается только один запрос.

Итак, мои вопросы:

  • Можно ли получить две отдельные блокировки в двух отдельных транзакциях в отдельной группе строк (в одной таблице)?
  • У меня так много коллизий между первым и вторым запросом (у меня проблемы с отладкой, любой намек на то, как отлаживать SELECT ... FROM (SELECT ...) WHERE ... IN (SELECT) будет оценено)?
  • Можно ORDER BY ... LIMIT ... вызвать какие-либо проблемы?
  • Могут ли индексы и ключи вызывать какие-либо проблемы?
Источник

2 ответа

Решение

Ключевые вещи, чтобы проверить, прежде чем идти дальше:

  • Убедитесь, что механизмом таблицы является InnoDB, иначе "для обновления" не будет блокировать строку, так как транзакций не будет.
  • Убедитесь, что вы используете функцию "для обновления" правильно. Если вы выберете что-то для обновления, он будет заблокирован для этой транзакции. Хотя другие транзакции могут читать строку, она не может быть выбрана для обновления, обновлена ​​или удалена любой другой транзакцией, пока блокировка не будет снята исходной транзакцией блокировки.
  • Чтобы сохранить чистоту, попробуйте явно запустить транзакцию с помощью "START TRANSACTION", запустите команду select "для обновления", сделайте все, что вы собираетесь делать с возвращаемыми записями, и завершите, явно выполнив "COMMIT" для закрыть сделку.

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

Чтобы ответить на ваши вопросы:

  1. Можно ли получить две отдельные блокировки в двух отдельных транзакциях в отдельной группе строк (в одной таблице)?
    Да, но не в тех же рядах. Блокировки могут существовать только на уровне строк в одной транзакции за раз.
  2. Есть ли у меня так много коллизий между первым и вторым запросом (у меня возникают проблемы с отладкой этого, любой совет о том, как отлаживать SELECT ... FROM (SELECT ...) WHERE ... IN (SELECT) будет приветствоваться)?
    Может быть короткий период, когда вычисляется блокировка строки, что приведет к задержке второго запроса, однако, если вы не запускаете много сотен таких программ для обновлений одновременно, это не должно вызывать каких-либо значительных или заметных задержек.
  3. Может ли ORDER BY ... LIMIT ... вызвать какие-либо проблемы? Не в моем опыте. Они должны работать так же, как и при обычном операторе выбора.
  4. Могут ли индексы и ключи вызывать какие-либо проблемы?
    Индексы должны существовать, как всегда, для обеспечения достаточной производительности, но они не должны вызывать проблем с получением блокировки.
Источник

Все точки в принятом ответе кажутся хорошими, за исключением ниже 2 баллов: "все, что должно было быть возвращено Select, будет заблокированными строками" & "Могут ли индексы и ключи вызывать какие-либо проблемы? Но они не должны вызывать проблем с получением блокировки".

Вместо этого все строки, которые внутренне читаются БД во время принятия решения, какие строки выбрать и вернуть, будут заблокированы. Например, приведенный ниже запрос заблокирует все строки таблицы, но может выбрать и вернуть только несколько строк: выберите * из таблицы, где non_primary_non_indexed_column =? для обновления Поскольку индекса нет, БД должна будет прочитать всю таблицу, чтобы найти нужную вам строку и, следовательно, заблокировать всю таблицу.

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

Это хорошая ссылка - https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

Источник
Другие вопросы по тегам