Is "order by random() for update skip locked limit 1" guaranteed to not lock more than 1 row?

Question

I did test this inside a serializable transaction and it seems to work as expected. But I'm wondering about the guarantees here. What is the order of execution? My expectation is order/skip->limit->lock.

Would it ever lock more rows than one?

My actual scenario: I have a table where each row represent a unit of work. I have multiple concurrent workers that should start a transaction, reserve one unit of work by select ... where completed=false order by random() for update skip locked limit 1 , and at the end of work update the row including a completed=true column before committing the transaction. If the reservation returns zero rows or errors with SQL Error [40001]: ERROR: could not serialize access due to concurrent update then it will restart its transaction. The point is to avoid doing unnecessary work by failing immediately at the beginning of work.

My concern is whether there could be dangling locks even if it selects only one row?

In the documentation for advisory locks, it warns against LIMIT queries:

because the LIMIT is not guaranteed to be applied before the locking function is executed. This might cause some locks to be acquired that the application was not expecting, and hence would fail to release (until it ends the session). From the point of view of the application, such locks would be dangling

However does this apply only to advisory locks and not SELECT FOR UPDATE? I cannot find such a caution documentation for SELECT FOR UPDATE. There is only a caution on the select docs about returning rows out of order, which is not a concern in my case because I just order by random anyway.

Answer 1

Yes, that is specific for advisory locks, which are taken using a function.

To answer questions like that, look at the execution plan. It will look similar to this:

EXPLAIN (COSTS OFF)
SELECT * FROM customers
ORDER BY random()
FOR NO KEY UPDATE SKIP LOCKED
LIMIT 1;
               QUERY PLAN                
═════════════════════════════════════════
 Limit
   ->  LockRows
         ->  Sort
               Sort Key: (random())
               ->  Seq Scan on customers
(5 rows)

The rows are locked after the sort, in the order they are returned by the sort, and processing will stop as soon as LockRows has locked the first row and passed it to the Limit node.

Note that FOR UPDATE is only the correct lock if you intend to delete the row or modify a primary or unique key. For normal UPDATEs, you should use FOR NO KEY UPDATE to avoid excessive locking.

You seem to be using an isolation level higher than read committed, because you are getting serialization errors. Note that that isn't required for this algorithm to be correct (but it is of course OK).