Mysql — Как заморозить доступ к строке чтобы другие потоки не работали с ней?

Question

[Степан]

Есть Mysql база данных пользователей и многопоточная с# программа.
Структура таблицы:
ID (bigint)
Text (varchar)
Verified (boolean)
Thread (boolean) false по умолчанию / true если поток работает с данной строкой (для блокировки строки)
Текуший сценарий выполнения:
1 - Запрос на получение строки Verified = False
2 - Устанавливаем на время работы Thread = True
3 - Выполняем ритуал Вуду с полученными данными
4 - Устанавливаем Verified = True
5 - Устанавливаем Thread = False, и освобождаем для других многопоточных сценариев.

Проблема первая, пока установится Thread = True на втором шаге, другой поток успевает получить туже строку, поскольку втиснулся поток между шагом один и два, аккурат до блокировки строки.

Первая идея, проблема в слишком быстром выполнения сценария. При 10 потоках 50% обрабатываю туже строку, на 90 потоках 80% работают с той-же строкой.
Решение добавить рандомные задержки перед запросом. Чуть помогло, только 20% потоков обрабатывают туже строку при 10 потоках и 10% при 90 потоках.

Как с этим бороться?
Как сделать так чтобы несколько потоков не работали над одной и той-же строкой?

Comments

[eRKa]

Может я не в теме, но тег стоит C# и у меня возникает вопрос: почему нет блокировки запроса на уровне кода? Что-то вроде очереди запросов. Потоки просто ставят запросы в очередь и ожидают их ответов. Решается это не долго.

Answer 1

[bkosun]

Используйте транзакции и блокировки

Answer 2

[Rsa97]

Сейчас не вспомню точный вариант, но для синхронизации выборки по одной строке я пользовался назначением переменной в команде UPDATE, примерно так:

UPDATE `table` 
  SET `onWork` = 1, `timestamp` = NOW() 
  WHERE `onWork` = 0 AND (@id := `id`) > 0
  ORDER BY `timestamp`
  LIMIT 1

А затем уже выбирал эту строку

SELECT * 
  FROM `table` 
  WHERE `id` = @id

Ну и после обработки

UPDATE `table`
  SET `onWork` = 0, `timestamp` = NOW(), ...
  WHERE `id` = @id

Answer 3

[eRKa]

Можно попробовать эти проверочные поля вынести в код, сделать этот объект доступным для всех потоков. Поток, прежде, чем сделать запрос, сначала в список заносит id строки, ответ получил - из списка этот id удалил. Другие потоки, прежде чем сделать запрос, проверяют список, если id есть в списке, то в ожидание.
Тут может быть та же проблема, но с меньшей вероятностью, поэтому проверка должна быть двойная: сначала проверил, что id нет такого, добавил, потом проверил, что их не два в списке и только тогда запрос. Если два, то удалил и с таймаутом пытается опять добавить.
Я не знаю Ваших объемов бд, но как второй вариант рассмотреть нормальные очереди запросов. Перед запросом проверить есть ли очередь с именем айдишника, если нет, то создать и последующие запросы просто будут добавляться в свою очередь.

Answer 4

[Ambrosian]

https://habr.com/post/46542/
А вообще - если нужна блокировка на уровне строк, то это признак, что пора переходить к PostgreSQL.