I want to do something like this:

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;

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 dba.stackexchange.com/questions/257217 Jan 12 '20 at 19:19
  • I added some solutions for the underlying problem under your other question. Jan 13 '20 at 2:41
  • 1
    Your SELECT ... FOR UPDATE should contain an ORDER BY id, else the exercise is pointless. Jan 13 '20 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 '20 at 18:59

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

About the question in the title:

You can use an empty SELECT list with a locking clause:

SELECT FROM foos WHERE owner_id = 123 AND unread ORDER BY id FOR UPDATE;

Still locks all selected rows, but does not return any data. Still returns empty rows, though, so you still get a row count like usual.

Or, inspired by a_horse's comment:

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. (You can abort in case of 0 rows.) Rows are locked all the same. The SELECT list in the subquery can still be just empty (shortest, cheapest).

Aside: and unread=true is just a noisy, less efficient way of saying AND unread.

If unlocked can be NULL, consider to use unread IS NOT true in both statements.

  • 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 '20 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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