Re: transaction safety

DaVinci wrote:
> On Wed, Feb 14, 2001 at 06:34:01AM -0500, Jan Wieck wrote:
> > 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.

You're right, at the time two FK rows referencing the same PK
get inserted, there is no need to block one of them. Thus,
PostgreSQL *shouldn't* block.

But at the time beeing, the foreign key triggers issue a

SELECT oid FROM <pktable> WHERE <key> = <referenced-value>
FOR UPDATE;

Since both INSERT operations on the FK table do it, the
second one is blocked until COMMIT of the first, releasing
the lock.

What we need is something like

SELECT ...
WITH LOCK;

applying a shared (read) lock of the PK row. In this state,
UPDATE/DELETE to the PK row in question or SELECT ... FOR
UPDATE of that will block, but more SELECT ... WITH LOCK
would be allowed and pass through without blocking.

I don't really want that language construct, since it's not
SQL standard. Maybe it's possible to achieve the same
behaviour with dirty reads or the like. However it's done
finally, it should behave like the above.

>
> 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.

You're welcome.

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