Can two “SELECT FOR UPDATE” statements on the same table cause a deadlock?
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
Steve A <golfmat1@yahoo.com> writes:
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)?
Only if you use ORDER BY in each such query to constrain the rows to be
locked in the same order. You probably want to spend some time perusing
the fine manual very carefully:
http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-FOR-UPDATE-SHARE
regards, tom lane