Re: Does Informix do Versioning Read-Only Transactions?

Tony Rothwell (Tony@santen.demon.co.uk) wrote: : 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. All true, but probably not obvious to the target audience of the Server Concepts Manual. In addition to X-referencing the section on "Explicit (Manual) Data Locking", it could quite easily have x-referenced the documentation on the exceptions and drawbacks. : > : > : 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? Although the architecture has been there since Oracle V4, I don't believe that they used to label it "Multi-Version Consistency Model" until others started to use the term to mean updateable versions. As I recall they used to label it "Read Consistency model" (although I must admit I don't go back as far as V4, so maybe they just re-introduced the term???) : MTS is crap, as you say. Getting better. I've seen reasonable results produced (by others, don't know how) using 7.1.4. I just wish they hadn't labelled it "multi-threaded" when it's not. : > 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 I've not thought so, although it's an interesting viepoint. : use dirty read (once again, a serious question)? Does the I think most could use the technique. Many queries and reports are used to gain an appreciation of the approximate state of the data (eg "Have we made more or less profit/revenue/widgets than last week/month/day/ year?", rather than "How much more down to the last cent/penny/unit?"). Those that *do* need to get a consistent view could usually usefully be update transactions (eg record the fact that it does/does not balance, summarise or archive the result etc). : application code need to be more sophisticated if dirty reads are : being used? I don't think the code does. Maybe the designers/developers do. : 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? Informix (pre-SQL). Doesn't it show? :-) : On a different note, does Informix have anything equivalent to : Oracle7's shared SQL pool? Does contention during parsing ever No. The shared SQL pool is a brilliant idea, although the implementation could be a bit better. : become a problem with Informix, particulary for SQL that's not : in stored procedures? The onus is on the developer to use PREPARE intelligently. I've not seen it become a problem, but I haven't been involved with running as many users against Informix as I have with Oracle, and I still have minimal experience of DSA. Even if it was the bottleneck in Informix, I'm not sure how I could identify it as such. Most of the Oracle apps I encounter still negate much of the benefit of the Shared Pool by using constants where they should use bind variables. -- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.