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

Yes. Also a properly designed application will use Optimistic Locking Protocols and not perform SELECT ... FOR UPDATE, insert, update, delete, before waiting for the user's input but will gather all locking operations until after the user is finished with manual data manipulations so that the locks are instantaneously held and released minimizing the effect on concurrency. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jan 30, 2012 at 5:36 AM, Fernando Nunes <domusonline@gmail.com>wrote: > 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... > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >

Thanks folks, I was coming to the same conclusion: SET LOCK MODE TO WAIT aReasonableNumberOfSeconds, and have my customer think how relevant it is to keep the master row locked for an unlimited time. Fernando, I checked that effectively the master table was granted a ROW LOCK on the PK row, thus preventing from any INSERT in the detail table for the PK value. Appartently, the PK constraint implementation cannot discriminate whether the PK is subject to be modified by the UPDATE statement ( appearing in the SET ( columns list) = ( values )), or not to be modified. I was expecting a higher constraint check granularity the mere ROW level. Why am I prevented from updating the master row if I don't modify the primary ? Nevertheless, you can UPDATE on detail as long as you respect the constraint terms... Maybe a feature request for the insert case ? :-) Eric

I suppose it comes down to the fact that an INSERT on the detail needs an exclusive lock on the master (to prevent it from being changed). And this can't happen while a lock is in place. But only more detailed insight info would allow a definitive answer. Regards. On Mon, Jan 30, 2012 at 12:56 PM, BeGooden-IT Vercelletto < begooden.it@gmail.com> wrote: > Thanks folks, > > I was coming to the same conclusion: SET LOCK MODE TO WAIT > aReasonableNumberOfSeconds, > and have my customer think how relevant it is to keep the master row > locked for an unlimited time. > > Fernando, I checked that effectively the master table was granted a > ROW LOCK on the PK row, thus > preventing from any INSERT in the detail table for the PK value. > > Appartently, the PK constraint implementation cannot discriminate > whether the PK is subject to be modified by the UPDATE statement > ( appearing in the SET ( columns list) = ( values )), or not to be > modified. I was expecting a higher constraint check granularity the > mere > ROW level. Why am I prevented from updating the master row if I don't > modify the primary ? > > Nevertheless, you can UPDATE on detail as long as you respect the > constraint terms... > > Maybe a feature request for the insert case ? :-) > Eric > _______________________________________________ > 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...