Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP Path: utzoo!utgpu!water!watnot!watmath!clyde!rutgers!ames!ptsfa!ihnp4!occrsh!occrsh.UUCP!tiger.UUCP!authorplaceholder From: authorplaceholder@tiger.UUCP.UUCP Newsgroups: comp.databases Subject: Re: Re.: Ingres vs Informix Message-ID: <143900002@tiger.UUCP> Date: Mon, 23-Mar-87 10:52:00 EST Article-I.D.: tiger.143900002 Posted: Mon Mar 23 10:52:00 1987 Date-Received: Wed, 25-Mar-87 01:21:14 EST References: <1207@ihlpf.ATT.COM> Lines: 77 Nf-ID: #R:ihlpf.ATT.COM:-120700:tiger.UUCP:143900002:000:3959 Nf-From: tiger.UUCP!jlm Mar 23 09:52:00 1987 And now for additional cannon-fodder intended to incite thought (if not all-out riots)... 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). A hypothetical situation: User A is in the process of doing a select from a table (a rather large one 300,000+ rows). The select includes an order by clause and even on a single user system, the query takes 10 to 15 minutes. (yes, that's a while, but it is a join or some such thing creating a large aggregate report). User B wants to update one or two rows of the same table and submits his update request just a minute or two after User A begins his select. What happens to User B??? Ingres: Locking is page level promoted to table level after a tunable percentage of pages are locked UNLESS the select requested minlocks in his QUEL range statement (analagous SQL syntax??). Problem: if minlocks were not requested, the select has LOCKED the table to updates. The poor data entry operator attempting to change a patients phone number must sit and wait for her screen to come back 15 minutes later (boy is that some fast database manager :-) ). If minlocks were requested (select will not lock the table (or pages)), the update process completes immediately; however, the updates may affect the results of the select already begun. Since updates are really a delete and an append, the updated row can and often does appear in the report twice. Minor problem you say... not when the reports are Work In Progress reports for high volume manufacturing lines. Neither solution is satisfactory. Informix: Yes last responder, Informix only locks rows... except in the midst of a transaction! After begin transaction and one update, the table is locked until commit or rollback completes. Soooo, without transactions, the updates are once again visible to the select, and with transactions the table lock cannot be granted until the select completes. Why transactions?? how about transferring cash from one bank account number to another while a balance report is being generated. Does he get credit for both accounts or neither account? Yes, odds are against error, but odds go up as the number of concurrent users goes up. Plus, if transactions are in progress, the select will not even begin until the locks are released. Oracle: Implements locking of individual rows with table locks in the middle of transactions (similar to Informix). However, there is one MAJOR difference... Selects of the table are never blocked. Once a select has begun, it is assured a snapshot of the table(s) as they exist at that moment. Plus, updates (inserts and delete too) can continue to take place. A "before image" of the table is maintained until the select completes. Soooo, selects are assured accurate snapshots AND other update users can continue to work simultaneously; kinda like the best of both worlds. As for performance of the products, Informix is quicker on simple queries on small to medium tables (less than 50,000 rows), but try a 3 table join or a nested select and you can measure Informix performance in hours. Oracle performance is near linear on table size and complex features work too. Bottom line: 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! Jeff McReynolds (otherwise known as authorplaceholder, aren't gateways nice) AT&T Network Systems (see, I don't even work for Oracle) Oklahoma City, OK ...occrsh!tiger!jlm