Re: [SQL] Concurrency problem

> 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(at)debis(dot)com (Jan Wieck) #