Hi,
I posted a variation of this question on StackOverflow (http://stackoverflow.com/questions/12825663/can-two-select-for-update-statements-on-the-same-table-cause-a-deadlock), but wanted to see if there was any more insight on this list.
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?
Thanks in advance for any thoughts!
Steve
