Skip to content
Advertisement

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

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.

Advertisement

Answer

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:

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement