In a nutshell, I'm curious about the order in which PG will lock rows during a SELECT FOR UPDATE. If two simultaneous SELECT FOR UPDATE statements select intersecting rows from the same table, can PG be relied upon to lock the rows in a consistent manner that always avoids deadlock (e.g. in order of ascending primary key)?
If PG cannot be relied upon to do this by default, is there a way to force these statements to lock rows in a consistent order that avoids deadlocks? E.g. if an ORDER BY <primary-key> ASC clause is added to each statement, will this ordering impact the underlying locking order and therefore guarantee that a deadlock won't happen?
