froi froi - 1 year ago 76
SQL Question

Is there a possibility of deadlock when updating many rows using "IN()" in postgres?

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

My worry is, it might cause a deadlock with another query that we run in intervals:

select my_table where field = 'value' for update order by id;

The query above will fetch multiple rows.

Is this scenario possible?

Just a bit of background:

We added the order by id before since when we run the query above multiple times at the same time, we were having random deadlocks due to different orders by that query.

We were wondering if this applies to update statements as well.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download