Re: Does Informix do Versioning Read-Only Transactions?

>>>>> "gs" == Graeme Sargent <graeme@pyramid.com> writes: gs> D. Erway (derway@ndc.com) wrote: de> Sorry, I wasn't clear. The Oracle feature, (and Multi-versioning de> consistency control in general), allows a repeatable read snapshot of the de> whole database, gs> ^^^^^^^^^^^^^^ gs> Bullshine! gs> *NOBODY* configures their Rollback Segments (or rather the tablespace(s) gs> containing them) *that* large! Bah. In principle it is the whole database. The fact that you'll run out of disk space is a detail. The function of the mechanism does provide in principle a snap-shot of the whole database. And as long as the amount of transactions still fit in the rollback segments, you have your snap-shot of the whole database - most of it hasn't changed, so the snap-shot is equivalent to the current state. gs> : without locking out writers! gs> If you don't need to lock out writers, then you probably don't need gs> Repeatable Read. Dirty Read or Committed Read will do it most of the gs> time. The 2 are independent. If I want to generate a report on the database as it was at a point in time, without stopping updates, what are my choices? gs> What is more, ORACLE7 does lock out writers, except for read-only gs> transactions. So what? It is only better for read-only transactions. That's still better than not at all right? gs> On further reflection, I concede that Oracle Read Consistency qualifies gs> as Multi-versioning given the read-only restriction. It does not, gs> however provide updateable Multi-versioning, whereas I believe (but I am gs> not sure) that Interbase et al do not impose this restriction. Interbase does provide multi-versioning reads to update transactions, but if you use it, you are not guaranteed serializability! You can't have it both ways. If you allow multi-versioning reads, then you can have 2 intertwined transactions that take the database from state 0 to state 1, which is not equivalent to any decomposition of A then B, or B then A. With interbase you have to turn this off to provide guaranteed serializability, and then they only do table level locking! gs> Also my belief that Oracle Read Consistency does not provide true gs> Repeatable Read is apparently only true of read/write transactions, not gs> read only transactions: gs> ------------------------------------------------------------------------ gs> Time User A User B gs> ==== ====== ====== gs> 1 SET TRANSACTION READ WRITE; gs> 2 SELECT * FROM DUAL FOR UPDATE; gs> 5 COMMIT; gs> 6 SELECT COUNT(*) FROM DUAL; gs> COUNT(*) gs> ---------- gs> 2 gs> ------------------------------------------------------------------------ gs> which is where my conclusions came from. gs> whereas: gs> ------------------------------------------------------------------------ gs> Time User A User B gs> ==== ====== ====== gs> 1 SET TRANSACTION READ ONLY; gs> 2 SELECT * FROM DUAL; gs> 5 COMMIT; gs> 6 SELECT COUNT(*) FROM DUAL; gs> COUNT(*) gs> ---------- gs> 1 gs> ------------------------------------------------------------------------ gs> IMHO ORACLE7 should return a count of one at stage 6 in both cases. This is a bit scary. It looks like Oracle provides safer information to the less critical, (read-only) transaction! That is awful.