Re: Does Informix do Versioning Read-Only Transactions?

In article: <3l9l2j$7bm@sword.eng.pyramid.com> graeme@pyramid.com (Graeme Sargent) writes: > > The Server Concepts Manual also conveniently (but I suppose justifiably) > omits to mention the impact of Discrete Transactions or "Snapshot too > old" errors on the Read Consistency model. I thought nobody used discrete transactions anyway, so the 'cannot continue consistent read...' error is never likely to be seen in practice. If you were to use them, because of the restrictions they carry, the possibility of this occurring would either be designed out of your applications, or they would be designed to handle the error and retry. As far as I'm concerned, it's academic, as nobody uses them... As for the 'snapshot too old...' error, this is sometimes seen, but can be designed out by ensuring that extremely long running queries and extremely long read-only transactions are not concurrent with very high insert/update/delete activity. In my experience, these problems rarely occur and can always be resolved. On the last production system I worked on, there was a huge overnight batch run of several thousand jobs, and the 'snapshot too old...' error was encountered occasionally in a few programs which contained extremely long running queries. Rescheduling these jobs so that they were not running at the same time as some of the very high update jobs solved the problem completely. My previous experiences are similar. > > : gs> : Or any other versioning transaction control like maybe Interbase? > > : gs> Now *that's* versioning! > > and I think Oracle Marketing are just trying to hitch a ride on their > bandwagon, same as their "Multi-Threaded Server" tries to hitch a ride > on the Sybase bandwagon. For what it's worth, the read consistency model used in Oracle7 was introduced in Oracle Version 4 (1984). Have Oracle been hitching a ride for 11 years? MTS is crap, as you say. > In order to achieve Dirty Read type levels of performance ORACLE7 has to > resort to kludges like Discrete Transactions (which break Oracle's Read > Consistency Model). Most people consider this to be impractical in the > real world. As most of my work tends to be performance oriented, I tend > to lean towards favouring Dirty Read over Read Consistency (as do > Informix, presumably). I do not recall anything other than MRP II where > I saw Read-Consistency or Repeatable Read as a better tradeoff than > Cursor Stability, but then it is many years since I was an application > programmer! Discrete transactions have (virtually) no place in real-world apps, and to my knowledge, no Oracle7 benchmarks use them (now) because of this (ie it's a dead issue). Do you not think dirty read is basically a similar kludge (that's a serious question; I'm not slamming you!)? What types of real-world application successfully use dirty read (once again, a serious question)? Does the application code need to be more sophisticated if dirty reads are being used? Oracle's read consistency model may make the benchmarker's job more difficult, but it makes the application designer and developer's job easy. For instance, running simultaneous DSS and OLTP on the same data is almost never a problem. The point at which extremely long running queries cause you to encounter a 'snapshot too old...' error will usually be way beyond the concurreny levels achieved by a database in which readers block writers and writers block readers. Out of interest, when you were an application programmer, which database or databases did you use the most? On a different note, does Informix have anything equivalent to Oracle7's shared SQL pool? Does contention during parsing ever become a problem with Informix, particulary for SQL that's not in stored procedures? Cheers, -Tony.