Re: Does Informix do Versioning Read-Only Transactions?

Tony Rothwell (Tony@santen.demon.co.uk) wrote: : In article: <3lb8n5$ecm@sword.eng.pyramid.com> graeme@pyramid.com (Graeme : Sargent) writes: : > : > Tony Rothwell (Tony@santen.demon.co.uk) wrote: [ stuff deleted ] : > : 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). : As an application designer, the situations in which a query : that only gave 'approximately' accurate results could be : acceptable to me would be far outweighed by those in which : it was wholly unacceptable. This obviously depends on the : nature of the application, but I think you're being very : optimistic about how acceptable dirty-read is. : Do you label your reports that use dirty reads with warnings : so that your users know not to put reliance on the detailed : data they contain? Yes. The technique is more generally applicable to interactive queries, but I feel that a report which uses it should have "approximate" plastered all over it! : Let's face it, dirty read is not something people want; it's : something people *may* be prepared to put up with if the : database they use is not capable of delivering consistent : reads whilst maintaining high concurrency. Once you'd : got used to not having to resort to dirty reads, you'd : probably not be too happy about going back to them. I : know I certainly wouldn't. Don't forget that Dirty Read is not default behaviour. I did not intend to advocate it's universal use. It's a way of getting round concurrency issues in *some* cases. The point I was trying to make is that Repeatable Read is also an extreme. Whereas I feel that developers tend not to make use of Dirty Read where they could usefully do so, I suspect that many developers use Repeatable Read when they do not *need* to do so. Read Consistency is a nice idea in principle, and you're right, it makes programming simpler. I'm just very dubious about the overheads involved. After all, Communism and Socialism were nice ideas in principle! :-) :-) (Flames and political discussions to /dev/null, please!) : > : > : 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. : It sounds like the users need to be more sophisticated (psychic : maybe?), so that they can 'guess' which data on their screens : and in their reports is accurate and which isn't. I've not actually tried it, but I *think* you can change your isolation level within a transaction, which I guess means that, if you *wanted* to get sophisticated you could highlight which bits were dirty and which weren't! : Are you seriously telling me that most of the Informix apps in : production rely on dirty-reads? I can't believe that people don't : find this a problem. It may just be a case of "what you've never : had, you don't miss", but I'm a bit dubious. No I'm not. I was saying that it is an underused technique. : > : > : 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? :-) : I guessed so! Most people seem to be a bit biased towards the : database they know best or use most. You can see where my : 'loyalties' lie :) Hmmm. Oracle is definitely the one I use most nowadays. I'm not sure which I know best. I know Oracle7 better than OnLine DSA, but probably not better than OnLine pre-DSA. I think the issue is more which one you know well *first*. I probably spend more time in c.d.i, but that's because c.d.i comes before c.d.o in my .newsrc (because i comes before o!). : > : > : 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. : I've believe that Oracle 7.3 has got a redesigned library cache : (shared SQL pool), but I don't know what changes this entails. : > : > : 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. : In Oracle a latch is held during parsing to prevent object : definitions changing whilst the statement is being parsed. But there is no equivalent latch in OnLine (DSA or pre-DSA) so far as I am aware. Informix must have the same requirement to keep the object static, but I am not sure how it achieves it. Shared locks on the data dictionary, perhaps? If so, they are not held until commit time, or I would have noticed them. : Contention on this latch is the bottleneck you can see. Shared : SQL reduces the time this latch is held. It doesn't just : remove/reduce bottlenecks, though, it also reduces the time : taken (and CPU used) during parsing, so parse calls are faster : and general database performance is improved as less resources : are being used. That's what makes it a brilliant idea. Having a bottleneck latch in the first place is *not* a brilliant idea. Even with the shared pool it is still a potential bottleneck. Not having it multiplexed is cruddy. Multiplexing it looks b. difficult given their starting point. : I'd be interested to hear of Informix apps (particularly OLTP) : with >1000 concurrent users, to see whether the volume of parse : calls is a problem. I can't point at any off the top of my head, but I suspect that DSA is capable of it. Actually, I have one site running >1000 users (I think) against OnLine 4.1, but they use a TP monitor, so I wouldn't call it >1000 concurrent in your context. Even with the Shared Pool, apps that were written to v6 tend to bottleneck by 1200-1400 users. Prior to Forms 4, I couldn't get through the 2000 barrier without a TP monitor or access to source code (and somebody