From: Vadim Mikheev <vmikheev@sectorbase.com>
To: Jan Wieck <janwieck@Yahoo.com>
Cc: 'Rini Dutta' <rinid@rocketmail.com>
Cc: pgsql-sql@hub.org
Cc: pgsql-hackers@hub.org
Cc: Jan Wieck \\(E-mail\\) <janwieck@Yahoo.com>
Subject: Re: foreign key introduces unnecessary locking ?
Date: Mon, 23 Oct 2000 11:22:28 -0700
Message-ID: <004f01c03d1e$3430cae0$b67a30d0@sectorbase.com> (raw)
References: <200010221226.HAA00859@jupiter.jw.home>
> > though constraint triggers should use SnapshotDirty instead of
> > SELECT FOR UPDATE anyway.
> >
> > Did you consider this, Jan?
>
> Whenever the checks are done, the transaction inserting a new
> reference to the key must ensure that this key cannot get
> deleted until it is done and it's newly inserted reference is
> visible to others. Otherwise a referential action,
> preventing referenced key deletion (or other action) wouldn't
> see those and it would be possible to violate the constraint.
>
> I don't see any other way doing it than obtaining a lock.
> Using SnapshotDirty would mean, that one transaction could
> DELETE a reference, then another transaction removes the
> primary key (because using Dirty the DELETE is already
> visible), but now the first transaction rolls back. Voila,
> constraint violated.
Using Dirty transaction removing/updating PK could see that concurrent
xaction attempts to update/insert FK and so would wait for its commit/abort.
Just like now same row writers wait for each other.
Having this, we could insert FK without holding locks over PK. At the moment
of constarint check we would see and wait concurrent PK deletion.
If two xactons will wait for each other then one of them will be aborted.
This behaviour is more natural for MVCC system postulated that
only same-row-writers wait for each other. Why two same FK inserters
should wait for each other if we can avoid this?
Also, is there any way to get deferrable PK/UK constraints? I wonder
why unique index is used for them.
Vadim
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: vmikheev@sectorbase.com, janwieck@Yahoo.com, rinid@rocketmail.com, pgsql-sql@hub.org, pgsql-hackers@hub.org
Subject: Re: foreign key introduces unnecessary locking ?
In-Reply-To: <004f01c03d1e$3430cae0$b67a30d0@sectorbase.com>
References: <200010221226.HAA00859@jupiter.jw.home> <004f01c03d1e$3430cae0$b67a30d0@sectorbase.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox