Currently, we are updating many rows at the same time using this statement:
update my_table set field = 'value' where id in (<insert ids here>);
select my_table where field = 'value' for update order by id;
Yes, these can deadlock. To avoid this, run the
select ... for update order by id in the same transaction immediately before the update. This will lock all rows affected and avoid any other transaction from running the same
select ... for update query.
I am not saying consolidate the same two tasks. I am saying use the same locking select in both.