using deferred initially deferred to solve foreign key checking issues

From: Philip de Nier <philip(dot)denier(at)rd(dot)bbc(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: using deferred initially deferred to solve foreign key checking issues
Date: 2006-08-21 18:44:46
Message-ID: 6.0.1.1.2.20060821192017.02787b90@pop3
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I'm having problems with concurrent transactions that involve foreign key
constraints that use "SELECT ... FOR UPDATE". These constraints result in
one transaction waiting for another (failing to get a ShareLock on a
transaction which already has an ExclusiveLock).

The solutions I could find in the mailing lists were either to upgrade to
version 8.1 which uses "SELECT ... FOR SHARE" (I'm currently using 8.0),
stop using foreign keys or add "DEFERRABLE INITIALLY DEFERRED" to the
constraints. For now I'd prefer to use the last option.

My questions are as follows:
Does using "DEFERRABLE INITIALLY DEFERRED" completely solve the problem of
transactions waiting for each other to release locks resulting from "SELECT
... FOR UPDATE"?
How are the deferred foreign key constraints checked - are they checked one
at a time? If they are checked one at a time then transactions will no
longer be waiting on each other to release locks because each transaction
will only have 1 lock.

Thanks
Philip

Responses

Browse pgsql-general by date

  From Date Subject
Next Message louis gonzales 2006-08-21 18:59:48 Re: cannot open pg_database
Previous Message Roman Neuhauser 2006-08-21 18:19:05 Re: cannot open pg_database