Can two concurrent but identical DELETE statements

Question

Assume some_table has two rows, with primary key 1 and 2. The following sequence of statements can cause a deadlock:

session 1: begin;
session 2: begin;
session 1: DELETE FROM my_table WHERE my_key = 1;
session 2: DELETE FROM my_table WHERE my_key = 2;
session 1: DELETE FROM my_table WHERE my_key = 2;
session 2: DELETE FROM my_table WHERE my_key = 1;

The deadlock would not have occurred if both sessions deleted in the same order.

Now, coming to my question, what happens if the DELETE statement touches multiple rows? For example:

session 1: begin;
session 2: begin;
session 1: DELETE FROM my_table;
session 2: DELETE FROM my_table;

Is it possible that two concurrent but identical DELETE statements will delete rows in a different order? Is it possible to enforce the deletion order to avoid a deadlock?

I could not find this information in the documentation, so I would say that deletion order is not guaranteed (although it might be indirectly as an implementation detail). I wanted to double check here.

Answer 1

Yes, this could lead to a deadlock, because the order of rows in a table is not fixed.

Any UPDATE may change the order of rows returned by a sequential table scan, and if synchronize_seqscans is at its default value on, the order may change even if the table doesn't if several sequential scans are executed concurrently (like in your case).

You should first run a SELECT ... FOR UPDATE with an ORDER BY clause to reduce the risk of a deadlock, but even then you cannot be absolutely certain, unless you sort by a column that will not get updated concurrently (like the primary key).