SELECT * FROM jobs WHERE status='PENDING';
UPDATE jobs SET status='RUNNING' WHERE status='PENDING';

一般来说,您应该使用一个UPDATE语句. UPDATE通常不会受到在UPDATE语句运行时可能发生变化的行的影响,但是在事务隔离级别 here上读取很好. @H_502_3@假设您使用的是Read Committed的默认设置,这里是这样说的：

@H_502_3@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;

@H_502_3@关于更新：

@H_502_3@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.)

@H_502_3@所以在你的情况下,一个UPDATE应该是正常的.

@H_502_3@请记住,有一个所谓的SELECT FOR UPDATE语句,这将锁定您选择的行.你可以阅读这个here.
您需要使用此功能的情况将在预订系统中.考虑这个例子：

@H_502_3@>执行SELECT,找出XYZ是否可以在日期X进行预约.
>房间可用.执行UPDATE查询来预订房间.

@H_502_3@你在这里看到潜在的问题吗？如果在步骤1和2之间的房间被另一个交易预订,那么当我们到达步骤2时,我们正在运行一个不再有效的假设,即房间可用.

@H_502_3@但是,如果在步骤1中,我们使用SELECT FOR UPDATE语句,我们确保没有其他事务可以锁定该行,所以当我们去UPDATE行时,我们知道这样做是安全的.

@H_502_3@但是再次,在您的方案中,不需要此SELECT FOR UPDATE,因为您正在一个语句中执行所有操作,并且不会提前检查任何内容.