How can I lock rows FOR UPDATE without returning data over the connection?

Question

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?

Here's a question about the problem I'm trying to address https://dba.stackexchange.com/questions/257217 — John Bachir, Jan 12 '20 at 19:19
I added some solutions for the underlying problem under your other question. — Erwin Brandstetter, Jan 13 '20 at 02:41
Your SELECT ... FOR UPDATE should contain an ORDER BY id, else the exercise is pointless. — Laurenz Albe, Jan 13 '20 at 05: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. — Daniel Vérité, Jan 13 '20 at 18:59

Erwin Brandstetter · Accepted Answer · 2022-08-29T22:01:16.497

5

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

Why am I getting a deadlock for a single UPDATE query?

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.

edited Aug 29 '22 at 22:01

answered Jan 12 '20 at 13:30

Erwin Brandstetter

175,982
27
439
600

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. – John Bachir Jan 13 '20 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 05 '22 at 13:53

How can I lock rows FOR UPDATE without returning data over the connection?

1 Answers1

Linked