Can two "SELECT FOR UPDATE" statements on the same table cause a deadlock?
Sorry, I had another answer but it was wrong.
The documentation states that an ORDER BY clause is applied before the FOR UPDATE clause. So the locks are acquired in whatever order the rows are selected (I have confirmed as such by testing). If you need to select them in a different order, you can use:
SELECT * FROM (SELECT * FROM table ORDER BY id FOR UPDATE) ORDER BY another_column;You may want to try your question on the PostgreSQL mailing list.
From http://www.postgresql.org/docs/9.1/static/explicit-locking.html:
PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved
That page uses an example involving UPDATEs, which are equivalent to SELECT ... FOR UPDATE with respect to locking.