Параллелизм транзакций для предотвращения чтения старой версии записи

Question

Допустим, у меня есть таблица под названием tasks, Каждое задание имеет status, Я беру одну из задач, которые находятся в To Manage статус, поместите его в In Management статус и запустите процедуру, для которой была создана задача (что может занять несколько секунд).

В конце выполнения задача может вернуться к To Manage или же Completed статус, в зависимости от того, должна ли процедура быть запущена снова или нет.

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

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

$db->beginTransaction(); /* transaction A */

/* Reads one task from the database (SELECT query with LIMIT 1) which is in the `To Manage` status and returns it */
$task = $tasks->getNextTask(); /* operation 1 */

/* Changes the status into the `In Management` status (UPDATE query) */
$task->changeStatusToManage(); /* operation 2 */

$db->commit();

$task->execute(); /* operation 3 */

Я использую базу данных MySql, и таблица InnoDB, с уровнем изоляции READ COMMITTED: https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

Мы говорим, что в To Manage статус. Если два процесса (P1 и P2) выполняются одновременно, и это transaction A не существует, может произойти следующее:

Instant 1: (operation 1) P1 reads the task id 100 in `To Manage` status
Instant 2: (operation 1) P2 reads the task id 100 in `To Manage` status
Instant 3: (operation 2) P1 puts the task id 100 in the `In Management` status
Instant 4: (operation 2) P2 puts the task id 100 in the `In Management` status
Instant 5: (operation 3) P1 performs the task id 100
Instant 6: (operation 3) P2 performs the task id 100

Однако тот факт, что операции 1-2-3 фактически выполняются внутри транзакции, этот сценарий должен быть невозможным.

• Можете ли вы подтвердить, что это действительно так?
• Нужно ли выполнять явное LOCK для чтения таблицы задач перед выполнением операции 1 и освобождения ее после завершения операции 2?
• Что еще я должен сделать, чтобы предотвратить неожиданные результаты?

Структура БД намного сложнее, чем описанная выше. Когда я меняю статус задачи, я записываю журнал и в другую таблицу. Это делается самим кодом (классы моделей). У меня есть таблица задач, таблица task_status с внешним ключом для задачи и task_status_change (которая является таблицей журналов). Каждый TXN выполняет 1 чтение (получить задачу), 2 записи (изменение статуса и запись журнала). Поэтому мне нужно выполнить что-то похожее на это (псевдокод):

BEGIN;
$id = SELECT task_id FROM task WHERE task_status_id = 1 LIMIT 1;
UPDATE task SET task_status_id = 2 WHERE task_id = $id;
INSERT INTO task_status_change SET task_id = $id, task_status_id = 2;
COMMIT;

Как я уже упоминал выше, я использую уровень изоляции READ COMMITED. Я попытался запустить два процесса одновременно, работая вместе в одном пуле задач.

Идентификаторы задач, выбранные первым процессом (идентификатор и метка времени):

55 1496925510
274 1496925512
384 1496925512
589 1496925513
648 1496925513
1088 1496925513
1990 1496925513

Идентификаторы задач, выбранные вторым процессом (идентификатор и метка времени):

55 1496925510
274 1496925512
589 1496925512
648 1496925513
810 1496925513
1088 1496925513
2049 1496925514

Спасибо

Answer 1

getNextTask следует изменить status и получите идентификатор задачи, которую он изменил в одной транзакции. Один из способов (в псевдокоде):

BEGIN;
$id = SELECT id ... 
        WHERE status = 'idle'
        LIMIT 1 ... FOR UPDATE;
UPDATE ...  SET status = 'management' WHERE id = $id
COMMIT;

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

Сделайте что-то подобное для каждого перехода статуса.

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

Это звучит как механизм "очередей". У меня есть мантра: "Не ставь это в очередь, просто делай это". Это означает, что может быть проще / быстрее / проще порождать рабочий процесс всякий раз, когда у вас есть задача, вместо того, чтобы ставить ее в очередь и т. Д.