从 MySQL 数据库租用作业(原子更新和获取) [英] Leasing jobs (atomic update and get) from a MySQL database

问题描述

我有一个 MySQL 表，用于管理工作客户端可以租用进行处理的作业.除了描述工作的列之外，该表还有一个唯一的主键列id、一个时间戳列lease、一个布尔列complete 和一个整数列 priority.

I have a MySQL table that manages jobs that worker-clients can lease for processing. Apart from the columns that describe the job, the table has a unique primary key column id, a time-stamp-column lease, a boolean-column complete, and an int-column priority.

我正在尝试编写一个(一组)SQL 语句来管理租赁流程.我目前的计划是找到第一个租用日期至少是过去 8 小时的不完整工作(任何工作都不应超过一个小时，因此那个旧的不完整租约可能意味着客户端死亡并且需要重新启动作业)，将其租用日期设置为当前时间戳，并返回其信息.当然，所有这些都需要原子地发生.

I'm trying to write a (set of) SQL statement(s) that will manage the leasing-process. My current plan is to find the first incomplete job that has a lease-date that is at least 8 hours in the past (no job should take more than one hour, so an incomplete lease that is that old probably means that the client died and the job needs to be restarted), set its lease-date to the current time-stamp, and return its info. All of this, of course, needs to happen atomically.

我发现了一个巧妙的技巧 这里是 SO 以及 MySQL 文档讨论中的变体(参见 7-29-04 此处的帖子)，它使用用户定义的变量从 UPDATE 语句返回租用的作业.

I found a neat trick here on SO and a variation of it in the discussion of the MySQL documentation (see post on 7-29-04 here) that uses user-defined variables to return the leased job from an UPDATE statement.

而且，确实，这很好用:

And, indeed, this works fine:

UPDATE jobs SET lease=NOW() WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 AND NOT complete AND @id:=id LIMIT 1;
SELECT * FROM jobs WHERE id=@id;

当我尝试为作业添加优先级并在 LIMIT 之前将 ORDER BY priority 添加到 UPDATE 语句中时，问题就出现了.UPDATE 仍然按预期工作，但 SELECT 总是返回相同的行(第一行或最后一行，但不是实际更新的行).我对此有点困惑，因为 LIMIT 1 应该确保实际发生的第一次更新将终止 UPDATE 进程，留下 @id 设置为该更新行的正确值，不是吗?出于某种原因，它似乎一直在评估所有行的条件 @id:=id ，即使在完成更新之后(或者它可能首先对所有行评估它，然后才弄清楚是哪一个要更新，我不知道...)

The problem comes in when I try to add priorities to the jobs and add ORDER BY priority into the UPDATE statement right before LIMIT. The UPDATE still works as expected, but the SELECT always returns the same row back (either the first or the last, but not the one that was actually updated). I'm a little confused by this, since LIMIT 1 should make sure that the first update that actually happens will terminate the UPDATE process, leaving @id set to the correct value of that updated row, no? For some reason it seems to keep evaluating the condition @id:=id for all rows anyways, even after it's done with its update (or maybe it evaluates it first for all rows before even figuring out which one to update, I don't know...).

为了解决这个问题，我尝试重写语句以确保变量真的只为匹配的行设置:

To fix this, I tried rewriting the statement to make sure the variable really only gets set for the matching row:

UPDATE jobs SET lease=NOW(),@id:=id WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 AND NOT complete ORDER BY priority LIMIT 1;

但由于某种原因，这给了我以下错误:

But for some reason, this gives me the following error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near
'@id:=id WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 AND NOT complete ORDER BY prior'
at line 1

所以，似乎我无法在 SET-part UPDATE 中分配变量(尽管这是 SO-上面链接的答案).

So, it seems that I can't assign the variable in the SET-part of the UPDATE (although this was the way it was suggested in the SO-answer linked above).

这种方法能否以某种方式挽救，或者有更好的方法吗?

Can this approach be salvaged somehow or is there a better one altogether?

PS:我使用的是 MySQL 服务器 v5.5.44-0+deb8u1

推荐答案

我能够通过以下 hack 解决问题:

I was able to fix things with the following hack:

UPDATE jobs SET lease=IF(@id:=id,NOW(),0) WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 AND NOT complete ORDER BY priority LIMIT 1;

似乎根本不允许在 UPDATE 的 SET 部分中设置局部变量.

Seems like it's simply not allowed to set a local variable within the SET section of UPDATE.

注意:
由于id 列是一个自增主键，它永远不会是0 或NULL.因此，IF 语句中的赋值 @id:=id 应始终评估为 TRUE，因此 lease应该正确设置(如果我错了，请纠正我！).

Note:
Since the id column is an auto-increment primary key, it is never 0 or NULL. Thus, the assignment @id:=id inside the IF-statement should always evaluate to TRUE and therefore lease should be set correctly (correct me if I'm wrong on this, please!).

要记住的一件事:
默认情况下，变量 @id 的范围是 MySQL 连接(不是任何 Java Statement-object，例如，或类似)，所以如果一个连接用于多个工作租约，需要确保不同的 UPDATE/SELECT-pairs 永远不会交错.或者可以在变量名中添加一个递增的数字(@id1、@id2、@id3、...)以保证正确结果，但我不知道这会对 MySQL 服务器产生什么性能(或内存使用)影响.或者，可以将整个过程打包成一个存储过程，并将变量声明为本地变量.

One thing to keep in mind:
The variable @id by default is scoped to the MySQL connection (not any Java Statement-object, for example, or similar), so if one connection is to be used for multiple job-leases, one needs to ensure that the different UPDATE/SELECT-pairs never get interleaved. Or one could add an increasing number to the variable-name (@id1, @id2, @id3, ...) to guarantee correct results, but I don't know what performance (or memory-use) impact this will have on the MySQL-server. Or, the whole thing could be packaged up into a stored procedure and the variable declared as local.

这篇关于从 MySQL 数据库租用作业(原子更新和获取)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！