Re: transaction safety

On Wed, Feb 14, 2001 at 06:34:01AM -0500, Jan Wieck wrote:
> DaVinci wrote:
> > Problem is with referential integrity like you say. But I don't understand
> > well reason. Interlocks should appear only when values of referenced tables
> > are changed, added or removed, shouldn't they?. But that is not the case. I
> > only insert in table "aviso".
> >
> > Is this normal?
>
> Maybe.
>
> The problem is, that a referential integrity constraint needs
> to ensure that from the moment the constraint got checked
> until your INSERT got made persistent (COMMIT), nobody else
> has a chance to kick out the referenced key.
>
> Due to the lack of the ability to put a shared row lock with
> a SELECT, we currently use FOR UPDATE, placing an exclusive
> lock onto the referenced key.

I understand this but, why then other changes on "aviso" get locked?. My
first impression is that only referenced keys should be, but not a table
that references them.

If this works so for now, is any plan to change in future?.

> In your case it might help to make the constraints INITIALLY
> DEFERRED. That causes that the checks are delayed until
> COMMIT, so it shortens the time the lock is present.

Thanks!. That works fine.

David