Can two "SELECT FOR UPDATE" statements on the same table cause a deadlock?

Question

Suppose that two simultaneous transactions execute the following queries on a Postgresql DB:

Transaction A:

SELECT * FROM mytable WHERE id IN (1, 2, 3, 4) FOR UPDATE

Transaction B:

SELECT * FROM mytable WHERE id IN (6, 3, 2, 1) FOR UPDATE

Is it possible for a deadlock to occur due to Postgresql acquiring row locks in an inconsistent order? E.g. if Postgresql were to acquire row locks in the order the ids are given in this example, then there is a potential for deadlock.

Or is Postgresql internally intelligent enough to always acquire row locks in a way that simultaneous, discrete SELECT FOR UPDATE statements on the same table cannot deadlock each other (e.g. by always acquiring row locks in order of primary key)?

If Postgresql doesn't automatically prevent such deadlocks from occurring, is there a way to modify the queries to prevent such a situation (e.g. if in fact Postgresql acquires row locks in the order the ids are given, then consistently sorting the ids should prevent deadlock)?

Thanks for any help!

Expressivity

This question would qualify for dba.SE. – Prolocutor

The IN( .. , ..) set does not imply an ordered set. It is just a set (collection of items), similar to the result of select ... which is an (unordered) collection of tuples. In other words: the order of evaluation / execution is undefined – Firehouse

It is not really an answer to your question but, if you are afraid of deadlocks, why not elevating the isolation level SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; That way, the second SELECT would be rejected. – Joaniejoann

Answer 1

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.

Pennywise

Do you have a link to the relevant doc page? What you're saying makes sense, but I didn't see ORDER BY mentioned on postgresql.org/docs/9.1/static/explicit-locking.html. – Melantha

It's in the SELECT/FOR UPDATE section: postgresql.org/docs/9.1/static/… – Pennywise

Thanks for the answer. I did see that in the docs, but to me it's not clear that the actual row locking is done on rows in the order determined by the ORDER BY clause. That description could be read as saying that the ORDER BY simply predetermines the output order (which may be no longer be accurate once all locks are acquired) but has no effect on actual locking order. – Expressivity

Answer 2

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.

Melantha

Postgres can resolve deadlocks, yeah. But this does not answer the question, whether a deadlock can occur in this setup to begin with. – Prolocutor

@ErwinBrandstetter: I expect it can, since the order of rows returned by SELECT is unspecified. An ORDER BY clause should fix that though. – Melantha