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