postgresql - 如何使用 Postgres 的行级锁定更新表中的随机 4 行？

Question

我们计划使用一个没有引用的简单平面表作为 Postgres 中的事务数据存储。该表不断从不同的线程更新。我们需要从表中选择最多 4 行，最少 1 行，锁定它，更新状态字段并释放锁。理想情况下，这应该是并发查询不会相互阻塞的方式，每个查询都可以修改自己的行集。

阅读文档后我认为我可以这样做:

SELECT user_id FROM rooms LIMIT 1 INTO local_user_id 
WHERE user_status=0 FOR UPDATE;

UPDATE rooms  SET user_status = 1 
WHERE user_id = local_user_id;

但是，我当时需要更新 1-4 个用户，将所有最多 4 个用户捆绑到一个事务中会使事务数量下降到最佳情况的 1/4(假设大多数选择结果是 4 个用户) .使用 Postgres 9.6 解决此问题的惯用方法是什么？

更新 1:

我在这里试图实现的是一个类似队列的行为，每个线程获取一些项目并处理它们，而其他线程可以愉快地从其余行中读取并以并发和并行的方式处理其他 4 行。

Answer 1

您可以将选择的 ID 存储在数组中:

WITH x AS (
       SELECT user_id
         FROM rooms
        WHERE user_status=0
        ORDER BY random()
        LIMIT 4
          FOR UPDATE
)
SELECT array_agg(user_id) FROM x;

然后在一次查询中或单独进行更新。