Thread: 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... Taral
> 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 differentby 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) #
On Fri, 4 Feb 2000, Jan Wieck wrote: > 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. Does the CVS version have this support? Taral
> On Fri, 4 Feb 2000, Jan Wieck wrote:
>
> > 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.
>
> Does the CVS version have this support?
Mostly. Look at
http://www.PostgreSQL.org/projects/
to get a clue about the syntax. And follow the TODO link on
http://www.PostgreSQL.org/~wieck/
to see what we still need to fix/finish until BETA.
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) #