referential constraint help
Have any of you had problems with referential constraints on IDS 7.3? We are about to roll out
a whole new system in about 28 hours and while we're doing the data conversion from our old
schema (without any constraints) to our new schema (with constraints) we keep getting errors
like the following:
Program stopped at "/bddata/exe/calccomfsc.4gl", line number 415.
SQL statement error number -691.
Missing key in referenced table for referential constraint (root.r719_958).
SYSTEM error number -107.
ISAM error: record is locked.
We have one key table that is referenced via a foreign key in a good 1/2 to 2/3 of our other
tables. The only thing I can guess is that we have so many tables trying to verify referential
integrity against this one table that we're having some kind of lock contention. Changing our
4gl's by adding "SET LOCK MODE TO WAIT" to them seems to solve this problem but is less than
ideal obviously.
To throw one more kink into it, the tables that we seem to be having this on the most often are
also fragmented (not intelligently at this point, just round robin due to their size and to
speed inserts).
Is this a bug? Has anyone else had a problem like this? Any solutions?
--------------------------------------------------------
Name: Bill Weaver
E-mail: Bill Weaver <billw@fscorp.com>
Date: 05/16/99
Time: 03:53:25
(Retrospectively realizes there is no future in hindsight)
--------------------------------------------------------
HI Bill,
I believe informix is trying to take a lock on primary key while the row
is being inserted in foreign key table. Since you are trying to insert row
into multiple tables that is why lock contention is happening.
Instaed of doing set lock mode to wait, you can do set lock mode to wait 20
so you don't wait indefinitely. also make sure that you check that the lock
type defined on the primary key table is row lock and not page lock.
thanks,
khem chander
kchande@yahoo.com
Bill Weaver <billw@fscorp.com> wrote in article
<7hluso$6f1$1@news.xmission.com>...
>
> Have any of you had problems with referential constraints on IDS 7.3? We
are about to roll out
> a whole new system in about 28 hours and while we're doing the data
conversion from our old
> schema (without any constraints) to our new schema (with constraints) we
keep getting errors
> like the following:
>
> Program stopped at "/bddata/exe/calccomfsc.4gl", line number 415.
> SQL statement error number -691.
> Missing key in referenced table for referential constraint
(root.r719_958).
> SYSTEM error number -107.
> ISAM error: record is locked.>
> We have one key table that is referenced via a foreign key in a good 1/2
to 2/3 of our other
> tables. The only thing I can guess is that we have so many tables trying
to verify referential
> integrity against this one table that we're having some kind of lock
contention. Changing our
> 4gl's by adding "SET LOCK MODE TO WAIT" to them seems to solve this
problem but is less than
> ideal obviously.
>
> To throw one more kink into it, the tables that we seem to be having this
on the most often are
> also fragmented (not intelligently at this point, just round robin due to
their size and to
> speed inserts).
>
> Is this a bug? Has anyone else had a problem like this? Any solutions?
> --------------------------------------------------------
> Name: Bill Weaver
> E-mail: Bill Weaver <billw@fscorp.com>
> Date: 05/16/99
> Time: 03:53:25
>
> (Retrospectively realizes there is no future in hindsight)
> --------------------------------------------------------
>
>
Bill Weaver wrote:
>
> Have any of you had problems with referential constraints on IDS 7.3? We are about to roll out
> a whole new system in about 28 hours and while we're doing the data conversion from our old
> schema (without any constraints) to our new schema (with constraints) we keep getting errors
> like the following:
>
> Program stopped at "/bddata/exe/calccomfsc.4gl", line number 415.
> SQL statement error number -691.
> Missing key in referenced table for referential constraint (root.r719_958).
> SYSTEM error number -107.
> ISAM error: record is locked.>
> We have one key table that is referenced via a foreign key in a good 1/2 to 2/3 of our other
> tables. The only thing I can guess is that we have so many tables trying to verify referential
> integrity against this one table that we're having some kind of lock contention. Changing our
> 4gl's by adding "SET LOCK MODE TO WAIT" to them seems to solve this problem but is less than
> ideal obviously.
I agree with Khem, there is nothing wrong with SET LOCK MODE TO WAIT,
if you are concerned about programs becomming hung place a reasonable
time limit on the wait as Khem suggests and then the lock error will be
returned. These locks are instantaneous so there should be little
contention. Also look at the tables were any of them, particularly the
parent table, created with page level locking? This can greatly
increase lock contention! Try setting the tables' lock levels to row
mode: ALTER TABLE mytable LOCK MODE ( row );
Art S. Kagel