SELECT * FROM jobs WHERE status='PENDING'; UPDATE jobs SET status='RUNNING' WHERE status='PENDING';
所以得到所有待处理的作业,然后将它们设置为“运行”.
我不想在两个语句中一个接一个地这样做的原因是,作业可以在SELECT之后添加到作业表中为“PENDING”,但在UPDATE之前,所以我最终将作业设置为RUNNING,即使当它处于PENDING状态时,我没有抓住它.
有没有这样做在一个？所以我想要从SELECT和UPDATE的结果即时发生.
谢谢.
假设您使用的是Read Committed的默认设置,这里是这样说的：
Read Committed is the default isolation level in Postgresql. When a
transaction runs on this isolation level,a SELECT query sees only
data committed before the query began;
关于更新：
UPDATE,DELETE,SELECT FOR UPDATE,and SELECT FOR SHARE commands
behave the same as SELECT in terms of searching for target rows: they
will only find target rows that were committed as of the command start
time. However,such a target row may have already been updated (or
deleted or locked) by another concurrent transaction by the time it is
found. In this case,the would-be updater will wait for the first
updating transaction to commit or roll back (if it is still in
progress). If the first updater rolls back,then its effects are
negated and the second updater can proceed with updating the
originally found row. If the first updater commits,the second updater
will ignore the row if the first updater deleted it,otherwise it will
attempt to apply its operation to the updated version of the row. The
search condition of the command (the WHERE clause) is re-evaluated to
see if the updated version of the row still matches the search
condition. If so,the second updater proceeds with its operation,
starting from the updated version of the row. (In the case of SELECT
FOR UPDATE and SELECT FOR SHARE,that means it is the updated version
of the row that is locked and returned to the client.)
所以在你的情况下,一个UPDATE应该是正常的.
请记住,有一个所谓的SELECT FOR UPDATE语句,这将锁定您选择的行.你可以阅读这个here.
您需要使用此功能的情况将在预订系统中.考虑这个例子：
>执行SELECT,找出XYZ是否可以在日期X进行预约.
>房间可用.执行UPDATE查询来预订房间.
你在这里看到潜在的问题吗？如果在步骤1和2之间的房间被另一个交易预订,那么当我们到达步骤2时,我们正在运行一个不再有效的假设,即房间可用.
但是,如果在步骤1中,我们使用SELECT FOR UPDATE语句,我们确保没有其他事务可以锁定该行,所以当我们去UPDATE行时,我们知道这样做是安全的.
但是再次,在您的方案中,不需要此SELECT FOR UPDATE,因为您正在一个语句中执行所有操作,并且不会提前检查任何内容.
