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

Issue

I have a table with these 3 columns:

1. task (string)
2. status (string)
3. date (datetime)

I want to write a query that does the following:

1. 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.
2. Updates the Status column so status = "In-Progress".
3. 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.

Solution

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

Answered By - Erwin Brandstetter

Answer Checked By - Robin (PHPFixing Admin)