Re: referential constraints / error -691 -107 : back to referential integrity basics

I believe the OP's point was that even in case of a rollback on session A, the record with item_num = 100 would still be there. I'd have to test this and monitor it, but I believe the problem is that session B will try to lock the record from item. And because there is a lock on the row, it fails. But I'd have to check the existing locks in item to confirm this. In practice, session B should have a reasonable LOCK WAIT time that would allow session A to commit. Regards. On Sat, Jan 28, 2012 at 4:48 PM, Jonathan Leffler < jonathan.leffler@gmail.com> wrote: > > > On Sat, Jan 28, 2012 at 07:50, BeGooden-IT Vercelletto < > begooden.it@gmail.com> wrote: > >> I have been submitted a challenge from a customer, which is the >> following: >> * we have a master table "item" with item_num as primary key >> * we have a detail table "operation, with operation_num as primary >> key, and item_num as foreign key, referencing >> item (item_num) >> >> session A initiates a session with >> begin work; >> update item set ( some item colums EXCEPT THE PRIMARY KEY ) = ( some >> values ) >> where item_num = 100 ;>> -- leave sesssion "as is" and open another session on other terminal >> >> > So the change in session A is uncommitted. > > > >> session B opens a new session and attempts: >> insert into operation VALUES ( operation_num_value,100,more values)>> >> ==> Session B receives error >> 691: Missing key in referenced table for referential constraint >> (PKconstraint). >> 107: ISAM error: record is locked.>> > > This means that there wasn't a committed value 100 that could be > referenced, so the statement should fail. > > This is the most basic requirement for COMMITTED READ or higher > isolation. I'm not sure it should work under DIRTY READ isolation even - > although the value might exist at the moment, if the DR session (session B) > was allowed to commit, session A might rollback, leaving the row inserted > by B not referencing anything - a state of semantic disintegrity that > referential integrity is supposed to avoid. > > > > > >> In my mind ( I can be wrong though), I do not expect this error >> > > This error should be expected - as much because of isolation issues as > because of referential integrity issues. > > because in the UPDATE statement, I have not >> modified the primary key value. In opposition, I would have expected >> the constraint violation >> if I had modified the primary key value in the update statement. >> >> It seems that the foreign key constraint does not refer to the primary >> key index, but only on the fact that the row >> is locked. >> >> In my same mind, I think this scenario would have worked in 7.XX >> versions, for the reason I stated just above, >> provided the condtion that I do not modify the primary key value. >> >> I have a simple and easy to execute test case with data available on >> request by email. >> >> I may have forgotten some referential constraint basics, my age would >> allow it :-) >> >> Any simple suggestion to achieve such a scenario? >> > > Add a COMMIT to Session A. That is the best, most reliable way to deal > with it. Pretty much anything else is at best dubious. > > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...