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

From: Steve A <golfmat1(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Can two “SELECT FOR UPDATE” statements on the same table cause a deadlock?
Date: 2012-10-10 23:26:10
Message-ID: 1349911570.4906.YahooMailNeo@web114111.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Sabino Mullane 2012-10-10 23:36:18 Re: Planner chooses multi-column index in 9.2 when maybe it should not
Previous Message Gavin Flower 2012-10-10 23:25:58 Re: Index only scan