Re: transaction safety

DaVinci wrote:
> On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> > DaVinci <bombadil(at)wanadoo(dot)es> writes:
> > > procedencia int2 references procedencia,
> > > línea int2 references línea,
> > > empresa int2 references empresa,
> >
> > Depending on the data values you are working with, it could be that the
> > foreign key references cause interlocks --- ie, one transaction has to
> > wait to see if the other commits to know if it has a valid foreign key.
>
> 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.

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.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com