[NOVICE] SELECT FOR UPDATE with ORDER BY - Mailing list pgsql-novice

I am trying to understand the locking order of SELECT FOR UPDATE with ORDER BY and multiple result rows.

I found a thread from 10 years ago discussing the very same question, link -> https://www.postgresql.org/message-id/2382.1170171581%40sss.pgh.pa.us, which led me to think that I am doing the right thing  with using ORDER BY. However, I still experience dead-locks.

OS: Ubuntu 14.04 64bit 3.11.0-26-generic

PostgreSQL: 9.5

What I do:

I have the following transactions happening in parallel:

START TRANSACTION;

-- Select some rows of interest
SELECT * FROM my_table AS t WHERE t.attr1 = 'foo' AND t.attr2 = 'bar' ORDER BY t.id ASC FOR UPDATE;

-- Update some of the rows returned by the SELECT above

UPDATE my_table SET attr3 = 1 WHERE id = 1;

UPDATE my_table SET attr3 = 2 WHERE id = 2;

COMMIT;

Two transactions for which the SELECT FOR UPDATE query returns the same set of rows lock those rows in the order specified by the ORDER BY clause and thus eliminating the possibility of dead-locks.

Sporadic dead-locks between transactions, happening while either both transactions are executing the SELECT FOR UPDATE query or one transaction is executing SELECT FOR UPDATE and another one the UPDATE query.


Is my understanding of how SELECT FOR UPDATE with ORDER BY locking works wrong?

I tried to find the answer in the official docs regarding the locking order of multiple results produced by SELECT FOR UPDATE, but I couldn't find anything that explicitly describes this.

Thanks in advance for any feedback.