SKIP LOCKED unless all rows are locked

Question

Is there a way to do a query with PostgreSQL using SKIP LOCKED to avoid getting a row already locked but in the meantime if everything is already locked to wait for the first unlocked row ?

In my use case I'd like to avoid having false negative when requesting an available phone number. But in the case where I'd have all the number locked, unlikely but it still might happen and should be worked around.

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

Unrelated, but: [`ORDER BY random()` is really bad](http://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql) — pozs, Apr 11 '17 at 11:45
This *may* be solvable with [CTEs](https://www.postgresql.org/docs/current/static/queries-with.html), but I'm not sure: the [`FOR UPDATE SKIP LOCKED` docs](https://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE-SHARE) doesn't mention them explicitly, only the sub-selects are mentioned (so, syntax-wise it should be accepted in CTEs too). — pozs, Apr 11 '17 at 11:50

score 2 · Accepted Answer · answered Apr 11 '17 at 16:15

This seems to work:

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);

However, it waits for a random plain_number to release its lock. I don't think it's possible to wait for the first lock to be released.

Setup:

create table pool(
  plain_number text primary key
);

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

And here is a little node.js script I wrote for testing:

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);
}

SKIP LOCKED unless all rows are locked

1 Answers1