PostgreSQL选择for update锁定，新行

Question

我有一个并发使用案例：可以随时调用端点，并且应该执行操作。 操作如下所示（当前隔离级别为READ COMMITTED）：

SELECT * FROM TABLE_A WHERE IS_LATEST=true FOR UPDATE
// DO SOME APP LOGIC TO TEST VALIDITY
// ALL GOES WELL => INSERT OR UPDATE NEW ROW WITH IS_LATEST=TRUE => COMMIT
// OTHERWISE => ROLLBACK (all good not interesting)

现在，如果两个操作在更新方面同时开始，使用SELECT FOR UPDATE的方法是可以的。因为两个事务看到的行数相同，一个会更新这些行，第二个事务将等待其轮到SELECT FOR UPDATE并且状态是有效的。

我的问题是当第一个事务中有插入时。例如，当第一个事务锁定SELECT FOR UPDATE时，有两行，然后事务继续，在事务进行过程中，第二个事务进来想要选择SELECT FOR UPDATE（latest），并等待第一个事务完成。第一个事务完成后，在数据库中实际上有一个新的第三个项目，但是第二个事务在等待行锁被释放时仅获取了两行。（这是因为调用SELECT FOR UPDATE时的快照不同，只有两行符合IS_LATEST=true）。

是否有一种方法使此事务在等待后选择最新的快照？

- Božidar Vulićević

---

是否总是只有一行具有“IS_LATEST=true”？还是可能有多个？ - The Impaler

在进行并发操作时，我会坚持使用极端的方法，根据具体情况使用SERIALIZABLE或SKIP LOCKED。 - Mike Organek

Answer 1

使用您的方法后，当锁定消失后，第二个事务中的查询将返回空结果，因为它在相关行上看到了is_latest = FALSE，而新行尚不可见。因此，在这种情况下，您需要重试事务。

我建议您使用REPEATABLE READ隔离级别和乐观锁定：

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM table_a WHERE is_latest;  -- no locks!

/* perform your application ruminations */

UPDATE table_a SET is_latest = FALSE WHERE id = <id you found above>;

INSERT INTO table_a (is_latest, ...) VALUES (TRUE, ...);

COMMIT;

然后会发生三种情况：

1. 您的查询找到一行数据，事务成功。

2. 您的查询未找到任何行，则可以插入第一行。

3. 查询找到一行数据，但更新该行时出现序列化错误。
   在这种情况下，您知道有并发事务干扰，因此需要重复整个事务。

- Laurenz Albe

Answer 2

问题在于每个命令只能看到在查询开始之前已提交的行。有各种可能的解决方案...

更严格的隔离级别

您可以通过使用更严格的隔离级别来解决此问题，但这相对昂贵。

Laurenz已经提供了一个解决方案。

启动新命令

保持（便宜的）默认隔离级别READ COMMITTED，然后启动新命令。

仅锁定少量行

虽然只锁定少量行，但最简单的解决方法是重复相同的SELECT ... FOR UPDATE。第二次迭代会看到新提交的行并额外锁定它们。

存在一种理论上的竞争条件，即可能有其他事务在等待事务之前锁定新行。这将导致死锁。虽然可能性很小，但为了绝对确定，请以一致的顺序锁定行：

BEGIN;  -- default READ COMMITTED

SELECT FROM table_a WHERE is_latest ORDER BY id FOR UPDATE;  -- consistent order
SELECT * FROM table_a WHERE is_latest ORDER BY id FOR UPDATE;  -- just repeat !!

--  DO SOME APP LOGIC TO TEST VALIDITY

-- pseudo-code
IF all_good
   UPDATE table_a SET is_latest = true WHERE ...;
   INSERT table_a (IS_LATEST, ...) VALUES (true, ...);
   COMMIT;
ELSE
   ROLLBACK;
END; 

一个在(id) WHERE is_latest上的部分索引会是理想的。

需要锁定更多行

对于超过一把手的行，我建议创建一个专用的一行令牌表。一个经过严密实现的方案可能像这样，以管理员或超级用户身份运行：

CREATE TABLE public.single_task_x (just_me bool CHECK (just_me) PRIMARY KEY DEFAULT true);
INSERT INTO public.single_task_x VALUES (true);
REVOKE ALL ON public.single_task_x FROM public;
GRANT SELECT, UPDATE ON public.single_task_x TO public;  -- or just to those who need it

请参考以下链接：

• 如何只允许表格中有一行？

接下来：

BEGIN;  -- default READ COMMITTED

SELECT FROM public.single_task_x FOR UPDATE;
SELECT * FROM table_a WHERE is_latest;  -- FOR UPDATE? ①

--  DO SOME APP LOGIC TO TEST VALIDITY

-- pseudo-code
IF all_good
   ROLLBACK;
ELSE
   UPDATE table_a SET is_latest = true WHERE ...;
   INSERT table_a (IS_LATEST, ...) VALUES (true, ...);
   COMMIT;
END; 

一把锁的成本更低。
① 你可以选择是否额外加锁，以防止其他写入，可能使用较弱的锁....

无论如何，在事务结束时所有锁都会自动释放。

咨询锁

或者使用咨询锁。 pg_advisory_xact_lock()会持续整个事务：

BEGIN;  -- default READ COMMITTED

SELECT pg_advisory_xact_lock(123);
SELECT * FROM table_a WHERE is_latest;

-- do stuff

COMMIT;  -- or ROLLBACK; 

请确保为您的任务使用唯一的令牌，例如我的示例中的123。如果您有许多不同的任务，请考虑使用查找表。

要在不同时刻释放锁定（而不是在事务结束时），请考虑使用具有pg_advisory_lock()的会话级锁定。然后，您可以手动解锁或关闭会话。

这两个操作都会等待锁定资源。还有一些替代函数，它们返回false而不是等待...

- Erwin Brandstetter

---

感谢您提供的解决方案，我已经使用了建议锁路径，不是基于会话而是基于事务，使用 pg_advisory_xact_lock() 函数。如果事务被中止或成功完成，则该函数将自动解锁。 - Božidar Vulićević

@BožidarVulićević 是的，事务级别锁可能更适合您的任务。我已经相应地进行了调整。 - Erwin Brandstetter