RE: Share Mode Locking Problem...

There are several issues here. Let's investigate... For storing calculated values temporarily for a single user, use temp tables. That way everyone can have their own set, the original data is preserved, and you can always update the original table from the values in the temp table if the user wants to. The reason you get a "record locked" error, even though you used share mode, is explained in the Informix Guide to SQL-Tutorial, chapter 7. (Page 7-10, on my 7.2 CD-ROM version.) The philosophical reasons for providing the new values stem from an OLTP background, where you want to use the most recent data, and you want to know if that data is changing even as you read it. Can you imagine the requests we'd get if we provided only the oldest information? If you can eloquently describe a mechanism by which the engine could follow a locking strategy that *would* make you happy, then I invite you to register a feature request. It's how the world gets better. :) HTH, Clem Frido answers: }This is normal under Informix. Once you have updated the database values }there is no way you can get the old values. I'm not happy with it either }but Informix thinks it's brilliant (and it performs, I must admit). Chad asks: }} I wish to run a process which updates a bunch of rows in a table, }} makes a calculation, and then rollback all the changes in the end (think }} what-if }} scenarios). At the same time as playing out the scenario, I need for }} other users to be able to have read access to the old (already }} committed) data. }} }} So, I issue the following: }} }} begin work; }} lock table table in share mode; }} update table }} set colb = #### }} where cola = "this"; }} }} Then, without comitting/rolling back the above, in another session, I }} issue: }} }} select * from table }} order by cola; }} }} Instead of getting the old values for each row, I get: }} }} 243: Could not position within a table (informix.test_update). }} 107: ISAM error: record is locked. }} }} My isolation and transaction modes are set to their defaults (non-ansi }} databases). }} }} Informix is Online 7.2 for IRIX. }} }} Anyone have any clues as to why this basic locking strategy is }} failing? }} }} Thanks, }} Chad } } ___________________________________________________________ Clem Akins (aka clem@informix.com) <- NEW ACCOUNT! Informix Software, Inc (Standard disclaimers apply) International Technical Support Last seen: Palo Duro Canyon, heart of the Texas Panhandle