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

The error and scenario you describe can be cause by one of three problems that I've encountered: 1) bug# 14829 - successive inserts and deletes can lead to negative pn_npdata. This occurs in some 5.0x version of online and is real fun to track down and fix. The easiest way to find out if its is a bug on your system is to call ifx support and ask. They can also send you a fax describing the problem and the fix (if you can't do an upgrade.) 2) Another cause is the "scan bug" problem. This is where you are doing an update or delete on a table under transactions, but you are not using an index to specificaly identify the row to update or delete. The delete "scans" until it hits a locked row and can't go on and ... kaboom! The only reliable way around this is to do the update or delete by rowid. 3) Closely related to the "scan bug" is a fun problem you encounter if your program does updates and/or inserts into tables with indexes and a commit may not occur for some time. This causes causes a locked row to be hit by anyone else that does work in that table and if your commit doesn't occur in time, their program times out. A workaround to this is to use either a real table with no indexes or a temporary table without indexes. You then do all your work in the unindexed table and the last thing you do before committing your work is to update your real table fro the working table and then delete the working data using rowid. This is immediately followed by a commit work, which hopefully will clear all locks before the second program times out. In summary, if you have the PN_NPDATA bug, the engine will think a row is locked when it realy isn't and this can cause deadlocks to occur. Make sure that all deletes/udpates to a table occur via an index, preferably rowid. Try to do inserts into tables that contain indexes thru intermidiate, non-indexed tables and then move the intermidiate table data into the real table as the very last thing before committing work. Hopefully I got all this straight... I'm working from memory so some stuff may not be explained exactly right. If you need more info I can dig up a big writeup on this and mail it to you. -robw On 20 Jan 1997 13:38:42 -0500, Russ@oncontact.com ("Kahler, Russell P.") wrote: >We need more info to help but one thing you may want look at is your >Isolation level. if you are not using dirty read you may run into >locking problems of this type. > >Russ... > > >}---------- >}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. >}