0

I am currently using SKIP LOCKED FOR UPDATE to schedule tasks as such:

UPDATE task
SET started_at = NOW()
WHERE id IN (
  SELECT id
  FROM task
  WHERE /* ... bunch of logic ... */
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id

I would like to abstract the sub-query used to select outstanding tasks using a view, e.g.

CREATE VIEW outstanding_task AS
SELECT *
FROM task
WHERE /* ... bunch of logic ... */

and then use the view to pick tasks for scheduling, e.g.

UPDATE task
SET started_at = NOW()
WHERE id IN (
  SELECT id
  FROM outstanding_task
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id

Does SKIP LOCKED FOR UPDATE work with views in this setup?, i.e. Does it guarantee that the same ID will not be picked?

How to test it to be sure?

0

It does work as expected.

To test it, start two sessions and use explicit transactions, e.g.

Session 1:

BEGIN;
SELECT * FROM outstanding_task WHERE id=1 FOR UPDATE SKIP LOCKED

Session 2:

-- Here SKIP LOCKED is optional.
BEGIN;
SELECT * FROM outstanding_task WHERE id=1 FOR UPDATE SKIP LOCKED

Assuming there is an outstanding task with id=1, then the first transaction should return a row and the second one should not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.