postgresql - SKIP LOCKED 除非所有行都被锁定

Question

有没有一种方法可以使用 SKIP LOCKED 对 PostgreSQL 进行查询以避免获取已锁定的行，但同时如果所有内容都已锁定以等待第一个解锁行？

在我的用例中，我想避免在请求可用电话号码时出现误报。但是在我锁定所有号码的情况下，这不太可能，但它仍然可能发生并且应该解决。

SELECT plain_number FROM pool
  ORDER BY RANDOM()
  LIMIT 1
  FOR UPDATE
  SKIP LOCKED

Answer 1

这似乎可行:

WITH sl AS (
  SELECT plain_number FROM pool ORDER BY random() LIMIT 1 FOR UPDATE SKIP LOCKED
),
fu AS (
  SELECT plain_number FROM pool WHERE NOT EXISTS(SELECT 1 FROM sl) ORDER BY random() LIMIT 1 FOR UPDATE
)
SELECT * FROM sl FULL JOIN fu USING (plain_number);

但是，它会等待一个随机的 plain_number 来释放它的锁。我认为不可能等待第一个锁被释放。

设置:

create table pool(
  plain_number text primary key
);

insert into pool(plain_number)
select generate_series(1, 9)::text;

这是我为测试编写的一个小 node.js 脚本:

const pg = require("pg");
const options = {
    "user": "test",
    "password": "test",
    "database": "test"
};

function thread_ish() {
    const client = new pg.Client(options);
    const end = client.end.bind(client);
    const rollback = function (client) {
        client.query("ROLLBACK", end);
    };

    client.connect(function () {
        client.query("BEGIN", function (err) {
            if (err) {
                console.error(err);
                return rollback(client);
            }
            client.query(
                "WITH sl AS ("
                + "  SELECT plain_number FROM pool ORDER BY random() LIMIT 1 FOR UPDATE SKIP LOCKED"
                + "), fu AS ("
                + "  SELECT plain_number FROM pool WHERE NOT EXISTS(SELECT 1 FROM sl) ORDER BY random() LIMIT 1 FOR UPDATE"
                + ")"
                + "SELECT * FROM sl FULL JOIN fu USING (plain_number)",
                function (err, result) {
                    if (err) {
                        console.error(err);
                        return rollback(client);
                    }

                    console.log("Selected number is", result.rows);
                    setTimeout(function () {
                        client.query("COMMIT", end);
                    }, 1000);
                }
            );
        });
    });
}

for (var i = 0; i < 13; ++i) {
    setTimeout(thread_ish, Math.random() * 100);
}