Re: transaction safety - Mailing list pgsql-general

From DaVinci
Subject Re: transaction safety
Date
Msg-id 20010214135010.A2668@fangorn.net
Whole thread Raw
In response to Re: transaction safety  (Jan Wieck <janwieck@Yahoo.com>)
Responses Re: transaction safety
List pgsql-general
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

pgsql-general by date:

Previous
From: Jan Wieck
Date:
Subject: Re: Bad book review
Next
From: "Mikhail V. Majorov"
Date:
Subject: Parameters if trigger...