postgresql - 即使多个 session 在 postgresql 中调用它们，也可以让存储过程一个接一个地运行

Question

在postgresql中:多个session想要从表中获取一条记录，但是我们需要保证它们之间不会相互干扰。我可以使用消息队列来做到这一点:将数据放入队列中，然后让每个 session 从队列中获取数据。但它在 postgresql 中可行吗？因为 SQL 人员更容易调用存储过程。有什么方法可以配置存储过程以便不会发生并发调用，或者使用一些特殊的锁？

Answer 1

我建议确保存储过程使用 SELECT FOR UPDATE，这应该可以防止表中的同一行被多个事务访问。

根据 Postgres 文档:

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends. The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have an unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future.

更多SELECT信息。

所以你不会一次锁定表中的所有行(即通过 SELECTing all 记录)，我建议你使用ORDER BY 以一致的方式对表进行排序，然后执行 LIMIT 1，以便它只获取队列中的下一个。还要添加一个 WHERE 子句来检查某个列值(即 processed)，然后在处理后将列设置为一个值，以防止 WHERE 从捡起它的子句。