3

I want to do something like this:

begin;
select * from foos where owner_id=123 and unread=true order by id for update;
update foos set unread=false where owner_id=123 and unread=true;
commit;

The goal is to avoid deadlock when two processes execute the UPDATE simultaneously. Problem described more here: Why am I getting a deadlock for a single UPDATE query?

In the statement where I acquire the lock, I don't need any info about the rows. I just want to lock those particular rows. Is there a way to do this (elegant or hacky) which tells postgres to not do any of the work of actually giving me the data?

4
  • Here's a question about the problem I'm trying to address dba.stackexchange.com/questions/257217 Jan 12, 2020 at 19:19
  • I added some solutions for the underlying problem under your other question. Jan 13, 2020 at 2:41
  • 1
    Your SELECT ... FOR UPDATE should contain an ORDER BY id, else the exercise is pointless. Jan 13, 2020 at 5:37
  • It's a design problem at the upper level to have concurrent writes fighting for the same rows systematically. Solving that problem might be an alternative to working around deadlocks. Jan 13, 2020 at 18:59

1 Answer 1

5

About the goal to avoid deadlocks: see my answer to your related question:

About the question in the title:

Use an empty (shortest, cheapest) SELECT list with a locking clause in a subquery, and run count() in the outer SELECT:

SELECT count(*) AS locked_rows
FROM  (
   SELECT FROM foos
   WHERE  owner_id = 123
   AND    unread
   ORDER  BY id
   FOR    UPDATE  -- !
   ) sub;

This way you get a single result row with the number of locked rows. All rows selected in the subquery are locked for the duration of the transaction. (You can abort in case of 0 rows.)

Asides:

  • and unread=true is just a noisy way of saying AND unread.
  • If unread can be NULL, consider unread IS NOT TRUE instead.
2
  • Cool! using the subquery avoids sending unneeded info over the wire. But I suspected PG is still doing a tiny amount of what I would consider unneeded work, since its aggregating the row count and who knows what other internal metadata. Interesting that there is in 2020 no way to only lock rows in postgres. Jan 13, 2020 at 20:23
  • Thank you for the answer, it works. PostgreSQL docs keep sayng that "The locking clauses cannot be used in contexts where returned rows cannot be clearly identified with individual table rows; for example they cannot be used with aggregation." and "When a locking clause appears in a sub-SELECT, the rows locked are those returned to the outer query by the sub-query. ". Seems to be the bug in postgres docs.
    – Eugene
    Dec 5, 2022 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.