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.

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).