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

Rob Weight wrote: > > 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 > -snip- > >}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. etc. etc. - snip- Rob, Thanks, we've seen both 2) and 3) (see also my post yesterday, Wednesday, about adjacent record locking) Owing to the nature of our application (we are using Brock) we cannot use the solutions you suggest and have had to make our own work round. Briefly, we create a master lock table (mlt) which has a column to contain the transaction number from the parent table and this is set up as a unique key. (This transaction number is also the primary key on the parent table and the foreign key on all the children - referential integrity constraints). All begin work / commit / rollback's have been removed so no more row locks, just dirty reads. The first thing a user does is to attempt to insert the transaction number of the parent into mlt. If it succeeds then they can perform any actions they want. On exit the last thing they do is to delete the row from mlt. If the insert fails then they know that someone else has got to the record first and the application refuses any further access. Thus 'begin work' is replaced with 'insert into mlt ...' and 'commit work' is replaced by 'delete from mlt ...'. Fortunately the main application does not require any rollbacks. There is a utility that cleans up the tables once a day that does require begin / commit / rollback and these are enclosed in a pair of mlt insert / delete's to prevent other users touching the record whilst it is being archived. We believe that this makes the applications Informix-version-level independent. It should still work when we eventually go to V 7. Peter Jennings.