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
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 |