Re: Need help on stale data and scroll cursors.

android@texas.net (Andy Combs) wrote: > > We're having concurrency problems with some of our ESQL/C > applications, and hope that somebody may have an answer. The problem > occurs when one user updates or deletes a row that is in the active > set of another user's cursor - the second user still sees the old > data. The old data still shows up even if the row is fetched again. > The only way we know to update the data is to close and re-open the > cursor, which is too expensive an operation. > > Just to complicate things, the behavior is inconsistent - with simpler > cursors, the updates show up in the second user's cursor. The more > complicated the select statement, the less likely it is to work the > way we need. > > But there has to be some solution out there - the Perform screens show > all updates when a record is re-fetched. If a user deletes a row in > another user's active set, the second user even gets a message - an > extremely useful thing that we cannot duplicate at all. > > So, our biggest question is: How can we do in ESQL what sperform does > ? We'd also appreciate hearing about any other solutions people may > have found to stale data problems. > > > Versions: > > Informix ESQL/C 4.11 > Informix SE 4.11 (Standard Engine, not OnLine - does OnLine work the > way we need ?) > > Interactive Unix (SVR3.2) > > Thanks. I think more complicated cursors involve internal temporary tables which aren't updated when someone else changes the original data. Simpler cursors probably use the original data and indexes as a base for following fetches. This is what I've always assumed, but I'm guessing a little. If you want to check whether a row has changed, you can execute an individual select statement (using rowid?), and compare. This is the only way I know of defending against stale data when, for example, a cursor is open while the program waits for user input. This is common with pick-lists and such where you don't want to reopen cursors every time the user demands a new page of data. If you make an individual select on every row you fetch and compare the selected and fetched data, you can send a message to the user if any differences are found. Otherwise there are things called isolation levels in online which you can set. As usual in software, you have to find a balance between integrity and speed.