I am looking for some expert guidance here.

What I am seeing contradicts my understanding of basic RDBMS internals.

Env specifics: {Red Hat Linux EL3 (kernel 2.4.21-9.ELsmp), Informix SE 7.25UC6, Informix Client SDK 2.81UC2X5}

The scenario: Two tables, where one (table A) contains a foreign key to the other (table B) as a reference. When table A is UPDATEd, table B is mysteriously getting UPDATEd as well. As volume increases, this is creating locking conflicts with the row on table B.

My question is: Why is table B getting UPDATEd when it is not part of the UPDATE query and the relationship doesn't logically support it?

Table creation SQL looks like this:

CREATE TABLE t_A (
C_id INTEGER NOT NULL
REFERENCES t_C(C_id)
CONSTRAINT fk_index_1,
index_time_hms DATETIME HOUR TO SECOND NOT NULL,
B_id INTEGER NOT NULL
REFERENCES t_B(B_id)
CONSTRAINT fk_index_2,
size_kb INTEGER NOT NULL,
dt_last_update DATETIME YEAR TO FRACTION DEFAULT CURRENT NOT NULL,
PRIMARY KEY (C_id, index_time_hms)
CONSTRAINT pk_A
);

CREATE TABLE t_B (
B_id SERIAL PRIMARY KEY CONSTRAINT pk_B,
B_desc NCHAR(32) NOT NULL,
dt_last_update DATETIME YEAR TO FRACTION DEFAULT CURRENT NOT NULL
);

The update query bangs on table A frequently (once a second for each server that corresponds to a C_id) like so:

UPDATE t_A
SET B_id = 3,
size_kb = 0,
dt_last_update = CURRENT
WHERE index_time_hms BETWEEN hms_begin AND hms_end;

So far this is pretty basic stuff (IMHO). What I find, though, is that, as the volume of update txns increases, I start to get SQL errors:

exception : looking for handler
SQL error = -691 ISAM error = -107 error string = = "informix.fk_index_2"

That seems odd- the text for error -691 says "Missing key in referenced table for referential constraint (%s).", and the text for error -107 says "ISAM error: record is locked."

Detective work yields the following clues:

(1) SELECT on table B confirms the row with ID of 3 is in fact there.
(2) secheck utility reveals no detectable irregularities.
(3) selog utility reveals that table B, which should remain almost completely static, is actually being UPDATEd each time the UPDATE query is processed against table A!!!

TY Trx ID User User Name Date/Time Lngth FD Recno Filename
-- ------ ---- ---------- -------------- ----- -- -----
----------------------
FO 14869 1002 svs3500 3/22 22:40:07 74 2 t_ind00161
UP 14869 1002 svs3500 3/22 22:40:07 128 2 3
Pre-Image:
0000 0003 4172 6368 6976 6564 2F53 6372 ....Archived/Scr
6174 6368 2020 2020 2020 2020 2020 2020 atch
2020 2020 C714 020C 0311 0D2F 0B3C ......./.<
Post-Image:
0000 0003 4172 6368 6976 6564 2F53 6372 ....Archived/Scr
6174 6368 2020 2020 2020 2020 2020 2020 atch
2020 2020 C714 020C 0311 0D2F 0B3C ......./.<
FC 14869 1002 svs3500 3/22 22:40:07 74 2 t_ind00161
CW 14869 1002 svs3500 3/22 22:40:07 26
FO 14869 1002 svs3500 3/22 22:40:08 74 2 t_ind00161
UP 14869 1002 svs3500 3/22 22:40:08 128 2 3
Pre-Image:
0000 0003 4172 6368 6976 6564 2F53 6372 ....Archived/Scr
6174 6368 2020 2020 2020 2020 2020 2020 atch
2020 2020 C714 020C 0311 0D2F 0B3C ......./.<
Post-Image:
0000 0003 4172 6368 6976 6564 2F53 6372 ....Archived/Scr
6174 6368 2020 2020 2020 2020 2020 2020 atch
2020 2020 C714 020C 0311 0D2F 0B3C ......./.<
FC 14869 1002 svs3500 3/22 22:40:08 74 2 t_ind00161
CW 14869 1002 svs3500 3/22 22:40:08 26
FO 14870 1002 svs3500 3/22 22:40:08 74 2 t_ind00161
UP 14870 1002 svs3500 3/22 22:40:08 128 2 3
Pre-Image:
0000 0003 4172 6368 6976 6564 2F53 6372 ....Archived/Scr
6174 6368 2020 2020 2020 2020 2020 2020 atch
2020 2020 C714 020C 0311 0D2F 0B3C ......./.<
Post-Image:
0000 0003 4172 6368 6976 6564 2F53 6372 ....Archived/Scr
6174 6368 2020 2020 2020 2020 2020 2020 atch
2020 2020 C714 020C 0311 0D2F 0B3C ......./.<
FC 14870 1002 svs3500 3/22 22:40:08 74 2 t_ind00161
CW 14870 1002 svs3500 3/22 22:40:08 26

Note that the before and after images are identical- no change is intended or prepared for table B at all (shown here as its UNIX filename t_ind00161).

So... can anyone tell me why Informix might behave this way?

If I were desperate, I could remove the constraint, but it is there for a reason, and I am reluctant to take that approach just because this one does not seem to be behaving like a referential integrity constraint is supposed to.

Thanks in advance for any help you can offer.

Hi,

Please change on the table, the lock mode to, ROW. Set ISOLATION LEVEL TO DIRTY READ, if is possible.

Thanks for the response, Gustavo, but I'm not sure it pointed me in the right direction.

There are some characteristics of Informix-SE that narrow the problem space, and at the same time, make this even more perplexing to me:

Informix-SE supports only row-level locking (Informix Guide to SQL:Reference v7.2, p. 1-6).

Translation: Only row-level locking can occur in my environment.

Only those Informix-SE database servers that use kernel locking have the SET LOCK MODE statement available (Informix Guide to SQL:Reference v7.2, p. 1-7).

Translation: While the Linux OS I am using supports kernel locking, no "SET LOCK MODE" statement has ever been processed against my database, so it defaults to "NOT WAIT" (Informix Guide to SQLyntax Volume 2 v7.2, p. 1-562), which is critical for me, since I need to process one row per second for each client.

Informix-SE supports the SET TRANSACTION statement for access modes only. All databases running on Informix-SE use the Read Uncommitted isolation level (Informix Guide to SQL:Reference v7.2, p. 1-7). The "Read Uncommitted" isolation level corresponds to the "SET ISOLATION TO DIRTY READ" statement (Informix Guide to SQLyntax Volume 2 v7.2, p. 1-576).

Translation: The Informix-SE environment would support attempts to explicitly control some of the locking behavior via use of SET TRANSACTION statements. However, there is no use of any SET TRANSACTION statement anywhere in our code, nor was any applied against the database using DB-Access. The only way (that I can see) these environmental settings could be changed without my knowledge is if there is something happening behind the scenes with the ODBC/SDK layer (RogueWave SourcePro Ed. 6), and we don't have any code using that interface to control DB locking, either. We WANT the default of "Dirty Read"...

So... what I'm saying here is: AFAICT, we are already set up with the most permissive locking scheme available. The locking and backwards updating of a referential link on a foreign key should not be happening. I cannot explain it, and so far, have been unable to find any evidence that exposes a reason for this behavior. I'm still hoping someone has seen this before, perhaps, or can tell me somewhere else to look, or some other tool to use, to get to the bottom of this.

TIA!
Joe

Youd made sure there wasnt a trigger on table A doing the update?

Probably no such thing as a dumb question, but:

I've got no triggers, either...