我正在使用PHP和MySQL 5.6.17(InnoDB)实现一个队列,我想选择前N个匹配的行,然后将它们标记为正在处理。
我需要将行标记为正在处理,因为查询由并行运行的多个脚本执行(因此我需要防止脚本选择相同的行)。
我写了以下查询:
START TRANSACTION;
SELECT id, col2, col3
FROM table
WHERE col4 = 1 AND date_update_started < UTC_TIMESTAMP() - INTERVAL 12 HOUR
ORDER BY col5 DESC, col6 ASC
LIMIT 100 FOR UPDATE;
#update the above selected rows to mark them as being processed
UPDATE table SET date_update_started = UTC_TIMESTAMP() WHERE id IN (
SELECT id, col2, col3 #same query as above
FROM table
WHERE col4 = 1 AND date_update_started < UTC_TIMESTAMP() - INTERVAL 12 HOUR
ORDER BY col5 DESC, col6 ASC
LIMIT 100
);
COMMIT;
但是,在测试查询的更新部分时,我收到以下错误:
[错误] 1235 - 此版本的MySQL尚不支持"LIMIT & IN/ALL/ANY/SOME 子查询"
如何修改此查询,以便它选择前 N 个匹配行并更新这些行上的date_update_started
列,以便并行执行此查询的脚本不会选择它们?
确保脚本的每个实例都有一个唯一的 ID。您可以在运行时将其作为命令行参数传递。
向队列表添加列:
-
scriptId INT DEFAULT NULL
- 使用它来锁定某些行;保留锁定它们的脚本的 ID。
此代码锁定某些行:
UPDATE `table`
SET lockId = 123 # Replace '123' (in PHP) with the ID of the script that runs the query
WHERE lockId IS NULL
AND ... # put your own conditions here to select the entries you want to process
LIMIT 100 # change '100' with the number of entries you want to lock in a batch
然后运行:
SELECT *
FROM `table`
WHERE lockId = 123 # The same value as above
以获取锁定的行。
处理完每一行后,您可以将其从表中删除,也可以将状态字段设置为"已处理",并使用它来在上面的锁定查询中将其过滤掉。
备注:如果您绝对确定处理脚本在处理过程中永远不会崩溃,则此方法非常有效。如果它崩溃,它会使行保持锁定状态。如果在下次运行时使用相同的script ID
它将尝试处理锁定的行。可以通过在脚本退出时解锁行来解决此问题。