Re: Does Informix do Versioning Read-Only Transactions?

D. Erway (derway@ndc.com) wrote: : >>>>> "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. I was wrong! In practice as well as principle. I've already posted a grovel. : 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? At the time I wrote this, I was still under the delusion that Read Only transactions behaved the same way as Read Write transactions in Oracle. : 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? You're 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. Which is where I came in! -- 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.