Re: Does Informix do Versioning Read-Only Transactions?

D. Erway (derway@ndc.com) wrote: : >>>>> "gb" == Graeme Burnett <rgb@xinit.demon.co.uk> writes: : >> D. Erway (derway@ndc.com) wrote: : >> : >> : Does anyone know if any versions of Informix, (maybe the latest - 7), do : >> : Versioning Consistency Control like Oracle 7 does? : gb> From "Informix Guide to SQL 7.1 Tutorial" pp7-15 para 5: : gb> "The definitions for ANSI Serializable, ANSI Repeatable Read, and : gb> Informix Repeatable Read isolation levels are all the same. : gb> The Repeatable Read isolation level asks the database server to put a : gb> lock on every fow the program examines and fetches. the locks that are : gb> placed are shareable for an ordinary cursor and promotable for an update : gb> cursor. The locks are placed individually as each row is examinted. They : gb> are not released util the cursor closes or a transaction ends." : Sorry, I wasn't clear. The Oracle feature, (and Multi-versioning consistency : control in general), allows a repeatable read snapshot of the whole database, ^^^^^^^^^^^^^^ Bullshine! *NOBODY* configures their Rollback Segments (or rather the tablespace(s) containing them) *that* large! : without locking out writers! If you don't need to lock out writers, then you probably don't need Repeatable Read. Dirty Read or Committed Read will do it most of the time. What is more, ORACLE7 does lock out writers, except for read-only transactions. On further reflection, I concede that Oracle Read Consistency qualifies as Multi-versioning given the read-only restriction. It does not, however provide updateable Multi-versioning, whereas I believe (but I am not sure) that Interbase et al do not impose this restriction. Also my belief that Oracle Read Consistency does not provide true Repeatable Read is apparently only true of read/write transactions, not read only transactions: ------------------------------------------------------------------------ Time User A User B ==== ====== ====== 1 SET TRANSACTION READ WRITE; 2 SELECT * FROM DUAL FOR UPDATE; D - X 3 INSERT INTO DUAL VALUES ('Y'); 4 SELECT COUNT(*) FROM DUAL; COUNT(*) ---------- 1 5 COMMIT; 6 SELECT COUNT(*) FROM DUAL; COUNT(*) ---------- 2 ------------------------------------------------------------------------ which is where my conclusions came from. whereas: ------------------------------------------------------------------------ Time User A User B ==== ====== ====== 1 SET TRANSACTION READ ONLY; 2 SELECT * FROM DUAL; D - X 3 INSERT INTO DUAL VALUES ('Y'); 4 SELECT COUNT(*) FROM DUAL; COUNT(*) ---------- 1 5 COMMIT; 6 SELECT COUNT(*) FROM DUAL; COUNT(*) ---------- 1 ------------------------------------------------------------------------ IMHO ORACLE7 should return a count of one at stage 6 in both cases. The problem with the ORACLE7 architecture is that in the former case, if User A went to the Rollback Segment data, thereby ignoring User B's insert, (s)he would also be unable to see his/her own updates! Shades of Discrete Transaction!!! My suspicions that OnLine >= 6.00 would allow inserts into a Repeatable Read set proved unfounded. User B would be locked out by Informix until User A commits, and Repeatable Read would be maintained. I understand how this was achieved in <= 5.0x, but not in >= 6.00. Given the addition of a WHERE clause to the above example to inhibit table locking and a LOCK MODE ROW table, then User B appears to place locks on uncommitted data in a different (new) data page, rather than the next available slot, yet Dirty Read transactions cannot see the data, and oncheck reports the page as an empty data page (and includes it in the count of allocated pages, but does not yet include it in the count of data pages). User B is still inhibited by a key lock placed by User A, although I cannot work out which one. Are you monitoring this thread, June? Any comments? -- 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.