Question

我确实有一个非行表格,它是执行任务的集合体:

| id | status | owner | param1 |
+----+--------+-------+--------+
|  1 | used   | user1 | AAA1   |
|  2 | free   | user2 | AAA2   |
|  3 | free   | user1 | AAA3   |
|  4 | free   | user1 | AAA4   |
|  5 | free   | user3 | AAA2   |

这份表格是许多平行过程的查阅,是保证表上的每一行将仅以单一程序“使用”,同时又按表格(以<代码>id栏值表示)的顺序排列?

My first idea was to simply mark always next row in queue with simple update:

UPDATE table 
    SET status = "used" 
WHERE owner = "userX" 
    AND status <> "used" 
ORDER BY id 
LIMIT 1

而后,则指标注的行号。

这根本就没有做到——有某些数据(如3 000 000 000行)和较大负荷处理清单是全方位的通用说明,而我方位数则因“某种记忆”错误而坠毁。

因此,我下一个想法是:

进入第一个未使用行的行:

SELECT id 
FROM table 
WHERE owner = "userX"
    AND status = "free" 
ORDER BY id 
LIMIT 1

<><0>

• 努力标出它仍可自由使用的标记:

UPDATE table 
    SET status = "used" 
WHERE id = <id from SELECT above> 
    AND status = "free"

<><0>

go to <0> if row was updated

<><0>

do the required work with successfully found row

不利之处在于,在许多同时进行的程序中,在每一过程“拥有”浏览量之前,将总是在1.和2.之间跳跃。因此,确保系统能够稳定运作——我需要限制每个进程的数量,并冒着程序可能达到极限的风险,在表格中仍然有条目。

或许有更好的办法解决这一问题?

P.S. everything is done at the moment with PHP+MySQL

Answer 1

只是建议,而不是划分和限定为1,可能只是贪 gr(id):

SELECT MIN(id) 
FROM table 
WHERE owner = "userX"
    AND status = "free"

Answer 2

I am also using a MySQL database to choose rows that need to be enqueued for lengthy processing, preferring to do them in the order of the primary index ID column, also using optimistic concurrency control as shown above (no transactions needed). Thank you to @sleeperson for the answer using min(id), that is far superior to order by / limit 1.

我在提出另一项建议,允许体面地重新启动。我只是在启动时执行以下措施:

step0

a. 接驳管道:

SELECT id 
FROM table 
WHERE owner = "userX"
    AND status = "used"

call step4

Ec. 在坠毁或其他不受欢迎的事件之后,该事件将分发,用于处理以前本来应该做的行文,而不是留下在数据库中使用的标记,然后去我。

友情链接