How to select a row, lock it, update it, then select again?

Question

I have a table with these 3 columns:

task (string)
status (string)
date (datetime)

I want to write a query that does the following:

Selects the first row WHERE status != "In-Progress" Sorted by Date (oldest first), and Locks it - so other computers running this query concurrently can't read it.
Updates the Status column so status = "In-Progress".
Return the row's columns (like a regular Select * statement).

How do I write this query?

My main concern is that the row is only fetched by 1 computer, no matter how many concurrent instances are running.

[This answer](https://stackoverflow.com/a/52557413/9705687) shows a method for locking specific rows of a table. — bfris, Dec 20 '21 at 00:41
What do you want to happen to another session that is also running this process in parallel and finds the row locked? — The Impaler, Dec 20 '21 at 01:15
Ideally I'd like the row to not show up in other queries at all. — Robby Frank, Dec 20 '21 at 01:21

score 3 · Accepted Answer · answered Dec 20 '21 at 03:24

Assuming a table named "tbl" with a PK named "tbl_id":

UPDATE tbl
SET    status = 'In-Progress' 
WHERE  tbl_id = (
         SELECT tbl_id
         FROM   tbl
         WHERE  status <> 'In-Progress'
         ORDER  BY date
         LIMIT  1
         FOR    UPDATE SKIP LOCKED
         )
RETURNING *;

For an in-depth discussion of every step, see this related answer on dba.SE:

Postgres UPDATE ... LIMIT 1

How to select a row, lock it, update it, then select again?

1 Answers1