SELECT FOR UPDATE with ORDER BY

From: Bogdan Zlatanov <bogdan(dot)zlatanov(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: SELECT FOR UPDATE with ORDER BY
Date: 2017-03-09 10:51:17
Message-ID: CAJ8QyM3AiiMAaLYEtX8hMkwHOBr5L5BDj5z-W4sYWC8moTr9qQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello everybody,

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.

*My setup:*

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;

*What I expect:*

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.

*What I actually experience:*

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.

Browse pgsql-novice by date

  From Date Subject
Next Message Yaser Raja 2017-03-12 15:53:53 Re: Adding a third DR PostgreSQL native replication
Previous Message vinayak spratt 2017-03-09 01:30:44 Adding a third DR PostgreSQL native replication