RE: Share Mode Locking Problem...

On 21 Oct 1997, Clem Akins wrote: > 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. > This seems to be the recommended strategy from many people and makes a lot of sense given the information I gave to you to work with. Unfortunately, the base table has 11 million records, all interrelated. We need sub-second response times on these "scenarios". If I created what-if scenarios by using temp tables, the SQL to get the latest incarnation of each row becomes more complex and painfully slow due to a "not in" clause having to be used. select * from temp_table union select * from base_table where key not in (select key from temp_table) This solution just won't work. > 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.) > I only have the 7.1 manuals with me (the 7.2 is somewhere in this place)...I will have to find it and re-read it. > 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? > I'm sorry, but my OLTP background must be different from yours. It seems logical to me that if I issue an isolation level of "committed read" (re: give me only data that has been committed) on the 2nd query (the "select"), that I should not get an error when it comes to reading a shared locked row in the table which is being updated. I should get the data as it was in the table before a transaction started modifying it, assuming that the transaction is still in progress. But, then again, I came from Sybase and after all, we *are* number 3. So, maybe Informix is right in this case? But, maybe not. *smile* > 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. :) > Well, seeing as how much I'd love to make the world a better place... maybe I will open that request. > HTH, > > Clem > ___________________________________________________________ > 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 > > Thanks for your response, Chad