Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP Path: utzoo!dciem!nrcaer!cognos!garyp From: garyp@cognos.UUCP (Gary Puckering) Newsgroups: comp.databases Subject: Re.: Ingres vs Informix Message-ID: <477@cognos.UUCP> Date: Fri, 27-Mar-87 00:25:30 EST Article-I.D.: cognos.477 Posted: Fri Mar 27 00:25:30 1987 Date-Received: Sat, 28-Mar-87 13:37:18 EST References: <1207@ihlpf.ATT.COM> <143900002@tiger.UUCP> Reply-To: garyp@cognos.UUCP (Gary Puckering) Organization: Cognos Incorporated, Ottawa, Canada Lines: 63 In article <143900002@tiger.UUCP> authorplaceholder@tiger.UUCP.UUCP writes: > >When referring to the concurrency capabilities (or lack thereof) of the >Ingres 5.0 vs Informix 3.0 (or SQL) vs Oracle 5.0.20 (all on unix hosts >of course), my argument is based on what happens to the second user's >process whilst the first user has locked a table (or row of a table). > > ... > >Oracle NEVER blocks select processes (unless you explicitly ask it too) and >ALWAYS presents consistent snapshots. Yes, there are always work-arounds, >but I want my programmers writing constructive code, not constantly working >around the "Features" of my database manager. I only wish some benchmarks >would concern themselves with REAL issues, not just transaction rates! Nicely put. The approach used by Oracle is also used by DB2, SQL/DS, and Rdb/VMS (although actual implementation details vary). The scenario you describe points out that their are two types of transactions: read_only and read_write. A transaction involving only queries (i.e. only SELECT's) can be read_only. This enables the database manager to perform a Versioned Read on the tables involved. A versioned read involves checking the snapshot file to ensure that the record returned is current as at the start of the transaction (i.e. hide any updates that might have occurred because of concurrent transactions which had not committed before your transaction began). This means that all updates must be logged in the snapshot file, sometimes called a write-ahead log (since a write-ahead protocol is usually used). There is, of course, a penalty you pay for this. What you get, though, is degree 3 consistency (i.e. reproducable reads and no phantoms) and high concurrency for readers. Concurrent read_write transactions may still be blocked, if they are trying to update a record which your transaction has updated (but not committed). Some relational systems provide Transient Reads. This is the ability to read records *without* going through the snapshot file. For some situations, this is acceptable because read reproducability is not required and because a consistent view of the data is not required either. In other words, there are cases when seeing an uncommitted update is not all that serious. For example, many statistical queries are in this category. An objection I have to many relational systems is the lack of choice they provide in transaction control. I can understand why degree 3 consistency is the default, but I can't understand why some systems don't allow you the option of degree 2 or even degree 1. Some systems don't even let you specify a wait time for locks -- your transaction just dies if it has to wait longer than the system-defined wait time, or else it waits forever. Again, lack of choice. While on the subject of transactions, has anyone encountered problems with the fact that most relational database managers cannot retain your cursor position once you've committed the transaction? If so, in what circumstances have you encountered the problem and how did you get around it? -- Gary Puckering 3755 Riverside Dr. Cognos Incorporated Ottawa, Ontario decvax!utzoo!dciem! (613) 738-1440 CANADA K1G 3N3 nrcaer!cognos!garyp