Message-Id: <m12GhZA-0003kbC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] Concurrency problem
In-Reply-To: 
 <Pine.LNX.4.10.10002040529580.10043-100000@r149127-2815.dobiecenter.com>
	from Taral at "Feb 4, 2000 05:33:31 am"
To: Taral <taral@taral.net>
Date: Fri, 4 Feb 2000 13:10:24 +0100 (CET)
CC: pgsql-sql@postgreSQL.org
Reply-To: Jan Wieck <wieck@debis.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

> I have a small problem, and was wondering if there was a better fix than
> the one I have.
>
> Here goes: I have tables A and B, both with primary keys. A has a field
> which refers to rows of B, and vice versa. When these fields are modified,
> I want to ensure referential integrity, but without using triggers. Is it
> possible to avoid deadlock and preserve integrity in all situations
> without serializing the updates?
>
> Details: Without any locking, an update of A concurrent with a delete on B
> will cause an integrity breach. With locking, concurrent updates of A and
> B can cause a deadlock...

    You  cannot  use  a  regular trigger because of your circular
    dependency. What you really need to insure integrity  is  the
    FOREIGN  KEY  support  coming  with 7.0. It is implemented as
    triggers too, but they are handled a little different by  the
    trigger  manager  and  their  execution  can be delayed until
    COMMIT.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #