Re: foreign key introduces unnecessary locking ?

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