#Postgres deadlock update

Process 28384: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update Process 28454: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update Process 28297 waits for AccessExclusiveLock on tuple (113628,5) of relation 16817 of database 16384 blocked by process 28454. Process 28384 waits for ShareLock on transaction 4810092 blocked by process 28297. Process 28296: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for ERROR: deadlock DETAIL: Process 28454 waits for ShareLock on transaction 4810111 blocked by process 28384. Process 28297: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update Process 28296 waits for ShareLock on transaction 4809502 blocked by process 28297. ISSUE ERROR: deadlock DETAIL: Process 28297 waits for ShareLock on transaction 4809510 blocked by process 28296. UPDATE table set status = $1 where id =$2 ġ) Select query result will be used to lock all the rows with provided ref ID and that result is used for some business logicĢ) Update query to update the STATUS of a row which is part of same ref ID

Select * from table where ref_id = $1 and is_active is true for update I am using following query to select and update BEGIN

Postgres deadlock