Re: [HACKERS] FOR SHARE LOCK clause ?

Clark Evans wrote:
>
> > Ok, in multi-version systems readers never lock
> > selected rows and so never block writers. Nice but
> > cause problems in some cases: if you want
> > to implement referential integrity at the
> > application level then you'll have to use
> > LOCK TABLE IN SHARE MODE or SELECT FOR UPDATE to
> > prevent updation of primary keys etc. Not so good...
>
> I was wondering if there is another perspective
> to look upon this problem.
>
> Assertion:
>
> The primary difference between "primary key" and
> "unique key" is that primary keys are _never_
^^^^^^^^^^^^^^^^^^^^^^^^
> updated (during transaction processing) where
^^^^^^^
> unique keys may be.

1. Is this standard requirement?
2. Note that foreign keys may reference unique key,
not just primary one...
3. I told about implementing referential
integrity _at_the_application_level_, not by the
DB system itself - it's up to the user decide
what's allowed and what's not, in this case.

> Question:
>
> It seems that the techinical solution here may
> not be better locking, but rather a mechinism
> to prevent updates on primary keys unless the
> entire table is locked, or some other dramatic
> gesture for non-transaction processing.
>
> This leaves the issue of delete looming, so
> mabye the suggestion won't help.

Yes.

Vadim