Re: Possible deadlock issue when one transaction waiting on other and vice versa? Should, ideally, postgres recognize blocking situation?

On Tue, Apr 26, 2011 at 2:45 AM, Prakash Itnal <prakash074(at)gmail(dot)com> wrote:
> I assume that the access to act_rnc_pkey causes the blocking, however why?
> Or how I can resolve the blocking (commit one transaction solves the
> problem, but should Postgres not recognize the blocking situation and
> release one transaction?). Is this an error in Postgres?

The UPDATE locks the tuple in exclusive mode, which then prevents the
INSERT from obtaining the share lock that it needs to hold until
transaction commit.

Alvaro Herrera is working on something related to this problem:

http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/
http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks_part_2/

...but I don't think it will actually fix this particular case,
because here the tuple is getting updated before the foreign key
attempts to apply a share-lock.

Off-hand, I'm not sure what to do about that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company