Is FOR UPDATE needed in a sub query?

Question

Consider the following UPDATE. I think the naming is clear enough to not require any further explanation. The identification of which row to update happens in a sub query. Is there a concurrency risk here? Could another process "sneak in" between the execution of the sub query and the outer query, and book the row? If so, I think the risk could be eliminated by doing a FOR UPDATE in the sub query (see my comment in the query)

UPDATE
  bookableresource_slots
SET
  booked = $1,
  status = $2,
  data = $3
WHERE id = (
  SELECT
    id
  FROM
    bookableresource_slots
  WHERE
    starttime = $4
    AND booked IS NULL
  LIMIT 1
  -- SHOULD I USE "FOR UPDATE" HERE TO LOCK THE ROW?
)
RETURNING
  id;

I'd really like to know from wiser men about this. Given an example in the docs https://www.postgresql.org/docs/12/sql-select.html this seems a feasible idea. I was rather under the impression though this would be executed in one transaction and no need for the FOR UPDATE clause. Seems I might be wrong there. — Bjarni Ragnarsson, Feb 25 '21 at 14:18
Note: instead of the `WHERE id = ( scalar subquery based on LIMIT 1)` , you could use `EXISTS(...)` — wildplasser, Feb 25 '21 at 14:34
The operation is atomic but won't lock just one row, which can result in excessive blocking or deadlocks. As [this SO question shows](https://stackoverflow.com/questions/66006278/are-nested-sql-queries-atomic) and the [far more detailed discussion in dba](https://dba.stackexchange.com/questions/69471/postgres-update-limit-1) explains, you need `FOR UPDATE SKIP LOCKED` to be able to update multiple rows concurrently — Panagiotis Kanavos, Feb 25 '21 at 14:37
Are you sure @PanagiotisKanavos ? The docs specify the only rows locked are those used by the outer query. Does the LIMIT 1 not limit the locks to one row? — Bjarni Ragnarsson, Feb 25 '21 at 14:54
@BjarniRagnarsson that LIMIT 1 isn't a filter, it restricts the result set *after* the result rows are read, filtered and sorted. The operation is atomic, which means the rows will be held until it completes. For specifics and how everything interacts you'll have to read the answers at dba.stackexchange.com though. I'm still reading it — Panagiotis Kanavos, Feb 25 '21 at 15:02
Thank you for the information. In the questions you link to I found a link to [this question](https://stackoverflow.com/questions/11532550/atomic-update-select-in-postgres/11533623), the accepted answer to which seems pretty conclusive. Yes, there is a risk two connections could clash, and FOR UPDATE can be used to prevent the clash. I am still not sure I fully understand the SKIP LOCKED part though, but I am getting there. — Henrik Söderlund, Feb 25 '21 at 22:33
@BjarniRagnarsson treating a table as a pool harder than it looks. I tried something similar in the past and ended up losing slots. I still can't tell you why without thinking through it and reading the explanation again. Filtering starts with Shared locks which are elevated to Exclusive for updating, which means other connections can still read them. `FOR UPDATE` is needed to take a *stricter* lock and prevent other connections from seeing the row. In the end, it's far safer to use a separate queue and "destructive read" with `DELETE ... RETURNING` to effectively dequeue each record. — Panagiotis Kanavos, Feb 26 '21 at 07:12
@HenrikSöderlund the connections can clash but the operation is still atomic. `FOR UPDATE` itself won't prevent the clash. In an `UPDATE` rows are read for filtering etc using "read" locks. This means other operations can still read those rows. When the time comes to update a row, the lock is escalated to an "exclusive" lock. Other queries may have read that row as well and try to update it though. Those queries will have to wait for the first UPDATE to complete. `FOR UPDATE` takes "exclusive" locks from the start BUT that would block other concurrent queries. The queries are serialized — Panagiotis Kanavos, Feb 26 '21 at 07:46
@HenrikSöderlund with `SKIP LOCKED` concurrent UDPATEs can simply skip over the exclusively locked row and pick the next one. Creating a pool over a table without exclusively locking the table or using the SERIALIZABLE isolation level can be tricky. You should consider using a separate "queue" table instead to hold only free slots and "dequeue" them with `DELETE ... RETURNING`. — Panagiotis Kanavos, Feb 26 '21 at 07:54
Thank you. I think I understand now. In this particular situation I think we will start with a simple FOR UPDATE and accept that concurrent queries will have to wait in line. In our scenario the load should not be extreme, and the operations should be fast, but we do want to protect against multiple actors booking the same slot. If you want to put together an answer I'm happy to mark it accepted. Otoh I feel I have the answers I need now, so it's up to you if you want the extra points. :) — Henrik Söderlund, Feb 26 '21 at 11:15

score -1 · Answer 1 · edited Feb 25 '21 at 15:23

-1

Why don't you put it into a transaction. The DBMS would do the concurrency control for you. Example from: https://www.postgresql.org/docs/current/tutorial-transactions.html

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;

edited Feb 25 '21 at 15:23

answered Feb 25 '21 at 14:12

SevenOfNine

630
1
6
25

Why use a transaction if the operation is atomicc? And a single `UPDATE` *is* atomic. Adding a transaction in this case only increases the chance of a bug, if you forget to commit – Panagiotis Kanavos Feb 25 '21 at 14:14
Is nested SQL in postgres atomic? https://stackoverflow.com/questions/66006278/are-nested-sql-queries-atomic – SevenOfNine Feb 25 '21 at 14:20
And that answer says `yes it is, but the problem is LIMIT 1`. The problem is that [too many rows would be locked](https://dba.stackexchange.com/questions/69471/postgres-update-limit-1) without `FOR UPDATE SKIP LOCKED` resulting in deadlocks – Panagiotis Kanavos Feb 25 '21 at 14:28
A transaction could certainly be used to eliminate the concurrency issue, but my question is more academic than practical. – Henrik Söderlund Feb 25 '21 at 22:23

Is FOR UPDATE needed in a sub query?

1 Answers1