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@Yahoo.com #



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