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

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?

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.

StackExchange Database Administrators Q#257188, answer score: 5