PostgreSQL: deadlock detected SELECT FOR UPDATE in transaction

Question

I have the following schema

ID (PK)| REF_ID | ACTIVE | STATUS

ID - Primary Key

I am using following query to select and update

BEGIN;    
select * from table where ref_id = $1 and is_active is true for update;
UPDATE table set status = $1 where id =$2;
END;

Explanation for above

1) Select query result will be used to lock all the rows with provided ref ID and that result is used for some business logic

2) Update query to update the STATUS of a row which is part of same ref ID

ISSUE

postgres@machine ERROR:  deadlock detected
postgres@machine DETAIL:  Process 28297 waits for ShareLock on transaction 4809510; blocked by process 28296.
        Process 28296 waits for ShareLock on transaction 4809502; blocked by process 28297.
        Process 28297: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update
        Process 28296: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update 
postgres@machine ERROR:  deadlock detected
postgres@machine DETAIL:  Process 28454 waits for ShareLock on transaction 4810111; blocked by process 28384.
        Process 28384 waits for ShareLock on transaction 4810092; blocked by process 28297.
        Process 28297 waits for AccessExclusiveLock on tuple (113628,5) of relation 16817 of database 16384; blocked by process 28454.
        Process 28454: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update
        Process 28384: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update
        Process 28297: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update

This table is used in highly concurrent and distributed application (100's in parallel with same ref_id) and thats why i wanted to avoid distributed lock by having select and then update in same transaction.But i am facing with this deadlock error I don't know why explicit locking is not working.

Expected behaviour is that any other job with same reference ID must wait if any one else with same reference ID has acquired the lock

Help me figure out what I am missing or another workaround for this. I am still not clear even after explicit locking and being within transaction why is deadlock occurring.

Formally

Put an ORDER BY into the SELECT ... FOR UPDATE. Other than that, deadlocks cannot really be avoided in a highly concurrent application where many rows are locked in one transaction. – Sherrell

@LaurenzAlbe thanks for quick response. It would be helpful if you can explain to me on what to ORDER BY or even how will this help, Secondly I can limit parallelism so would like to benchmark as It's not mission-critical for me(soln can work for me), that's why would like to know why it will not work in a highly concurrent application; Thanks – Formally

Well, if the rows are locked in a certain order, deadlocks are less likely to appear. – Sherrell

Thanks @LaurenzAlbe indeed it helped. But it was something out of the box from my understanding of locking – Formally

Answer 1

As Laurenz said, in this simple case you should be able to eliminate the possibility of deadlock with an ORDER BY in your locking query.

A deadlock arises when, for example:

• Process A acquires a lock on row 1
• Process B acquires a lock on row 2
• Process A requests a lock on row 2 (and waits for B to release it)
• Process B requests a lock on row 1 (and waits for A to release it)

...And at this point, the processes will be waiting on each other forever (or rather, until the server notices, and kills one of them off).

But if both processes had agreed ahead of time to lock row 1 and then row 2, then this wouldn't have happened; one process would still be waiting on the other, but the other is free to proceed.

More generally, as long as all processes agree to follow the same ordering when acquiring locks, it's guaranteed that at least one of them is always making progress; if you only ever try to acquire locks which are "higher" than the ones you already hold, then whoever holds the "highest" lock will never be waiting on anyone.

The ordering needs to be unambiguous, and stable over time, so a generated primary key is ideal (i.e. you should ORDER BY id).

Bronchiectasis

Thanks. I was too lazy to spell it out. – Sherrell

Answer 2

I'm late to the question here but I was having a similar issue where a ShareLock was being created when I executed a select ... for update and an update in the same transaction. In my case, and possibly in the case above, it was due to Postgres not liking the use of a non primary key column as the criteria for the select ... for update. Even if the column has a unique constraint on it, a select on that column seems to lock the table. There are two ways that I found to get around this:

1. Make the column being selected the primary key - in my case it was a unique text reference code. I dropped the id bigint primary key column from the table and made the reference column the primary key and the deadlocks stopped.

2. In the case above, you might need to find the ID of the row you want to select before the transaction starts. In pseudo code/sql:

$3 = (select * from table where ref_id = $1 and is_active is true)

begin transaction

select * from table where id = $3 for update;
UPDATE table set status = $1 where id =$2;

commit transaction

Fledgy