Avoiding PostgreSQL deadlocks when performing bulk update and delete operations

Use explicit row-level locking in ordered subqueries in all competing queries.
(SELECT does not compete with write-locks.)

DELETE

DELETE FROM table_name tUSING (   SELECT id_A, id_B   FROM   table_name    WHERE  id_A = ANY(array_of_id_A)   AND    id_B = ANY(array_of_id_B)   ORDER  BY id_A, id_B   FOR    UPDATE   ) delWHERE  t.id_A = del.id_AAND    t.id_B = del.id_B;

UPDATE

UPDATE table_name tSET    val_1 = 'some value'     , val_2 = 'some value'FROM  (   SELECT id_A, id_B   FROM   table_name    WHERE  id_A = ANY(array_of_id_A)   AND    id_B = ANY(array_of_id_B)   ORDER  BY id_A, id_B   FOR    NO KEY UPDATE  -- Postgres 9.3+-- FOR    UPDATE         -- for older versions or updates on key columns   ) updWHERE  t.id_A = upd.id_AAND    t.id_B = upd.id_B;

This way, rows are locked in consistent order as advised in the manual.

Assuming that id_A, id_B are never updated, even rare corner case complications like detailed in the "Caution" box in the manual are not possible.

While not updating key columns, you can use the weaker lock mode FOR NO KEY UPDATE. Requires Postgres 9.3 or later.

The other (slow and sure) option is to use the Serializable Isolation Level for competing transactions. You would have to prepare for serialization failures, in which case you have to retry the command.