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.
CodePudding user response:
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: