Re: FW: SQL code -246, ISAM code -154 problem.

In article <5c0e3i$57a@cssun.mathcs.emory.edu>, "Kahler, Russell P." <Russ@oncontact.com> writes >}---------- >}From: Peter Jennings[SMTP:peter_jennings*@uk.ibm.com] >}Sent: Monday, January 20, 1997 2:57 PM >}To: informix-list@rmy.emory.edu >}Subject: SQL code -246, ISAM code -154 problem. >} >}Running OnLine V5.0 under AIX. >}We have a begin/commit process that locks a >}parent record by updating a column with its >}current contents. We find that if two users >}try to update different rows in the same child >}table, the first will suceed and the other fail >}with SQL code -246, ISAM code -154. >}The two users are accessing different rows >}in both the parent and the child tables. >}This only happens if the first user has not >}reached the 'commit' before the second user >}trys to update. >}Both the tables have row mode locking set. >} >}1st User User 2 >}begin work begin work >}lock parent row x lock parent row y >}. . >}. . >}update child row m . >}. update child row n (fails) >}. . >}. . >}commit . >} update child row n (OK) >} . >} commit >} >}Peter Jennings. >} Remember online 5 uses ADJACENT KEY locking not Online 7's KEY VALUE locking. E.g. User A: create table x (i int); (Create table + test data). insert into x values (8); (Note not done in transaction so insert into x values(9); immediately commited). create index djw1 on x(i); User A: begin work; update x set i=8 where i=8; THIS UPDATE takes an exclusive lock on the row with key value 8 and and Intent Exclusive (HDR+IX) Lock on the index key with key value 9 - The adjacent key value in the index - It is always the next biggest value (there is a theoretical 'infinity' key to handle updating the largest value in the index. ************************************************************ *** **** *** I'M NOT 100% SURE ABOUT THE NEXT BIT (I'LL BE **** *** TALK TO UK TECH SUPPORT ABOUT TO TOMORROW) BUT SO **** *** FAR MY EXPERIMENTS LEAD ME TO BELIEVE IT IS TRUE. **** *** (I'M HELPING TO TRACK DOWN ONLINE 5.01.UD1 ONLINE **** *** LOCKING PROBLEMS ON THE SYSTEM AT WORK WHICH WE **** *** USE TO TRACK SUPPORT CALLS). BY ADDING COLUMNS TO **** *** INDEXES I'VE MADE OUR LOCKING PROBLEMS GO AWAY ****  *** FOR THE LAST 48 HOURS - THE DOCUMENTATION IS NOT **** *** CLEAR ENOUGH ON THIS POINT. **** ************************************************************ Therefore if an index on the table is not very unique LOCKING THE ADJACENT KEY VALUE LOCKS ALL ROWS WITH THAT KEY VALUE. E.g. if 1000 rows have i=9 above they all get locked!!! Therefore under online 6 for any index make sure the combination of all the columns in the index give a unique value for each row. This may mean adding extra columns to the end of an create index statement just to reduce locking problems. *Hope you have serial columns or unique integers on all your big tables>. Will post a definate yes/no as to whether this will help when I get it. -- David Williams