Re: oracle or mssql

Yechiel,

  If I understand your argument, you are saying that a select statement should return all data, even if it is not committed. Since most changes are usually committed you are getting better data than if you get the pre-commit data. This pre-commit data is wrong in your view because it will change in the very near future after the commit.   The flaw with this thinking is that you will see partially changed data if you see it without a commit being issued. Imagine that I am giving all employees a raise with the following statement:

update employees set salary=salary*1.15;

  If I run a select statement (that lets me see data before it is committed) while this update is running, I may see some employees that got the raise and some that didn't. You are making the assumption that updates to the data have been completed. There is no way to know this without issuing a commit. Therefore your queries of non-committed data will only be right 1000/1 times if the queries are issued AFTER the updates are finished. If they are issued while the updates are occurring, then your queries will be wrong every time. In a world where non-committed data is available, a query will almost never return the same results on tables that are being constantly updated.

  Oracle takes the commit/rollback odds into consideration in its architecture. Updates are made to datablocks and copies of the pre-update data are stored in the rollback segment in case a rollback is needed. If the odds were the other way around, the updates would be stored in a pre-commit area and the original data blocks would stay as they are until commit.

  A dirty block is a block that needs to be written to disk. A dirty read is what you would get if you saw data that has not been committed.

>From the 8i concepts manual:

The three preventable phenomena are:

dirty read
 A transaction reads data that has been written by another transaction that has not been committed yet.

nonrepeatable (fuzzy) read
 A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.

phantom read
 A transaction re-executes a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.  

Jay

>>> adar76_at_inter.net.il 10/31/02 10:28AM >>>
That was exactly my point.

It is NOT 6 of one , half dozen of the other.

You commit 1000's of times for each rollback. So the data you read is incorrect while you read it with enormous odds that the changes will be committed.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 30, 2002 8:09 PM

But Yechiel,

what is better? Getting data that has not been committed by the application, or data that has been updated by an application without a commit being issued?

In the mssql option, do you really want to return data as valid, taking the chance that the person who updated the record may issue a "rollback"?

I think it's 6 of one, half a dozen of the other. At least with Oracle, it's logical and under the applications control. If the user issues a commit, then the new data is available for query. If the application needs the data commited more frequently, then issuing commits more often is certainly available.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, October 30, 2002 11:55 AM To: Multiple recipients of list ORACLE-L

I would like to point out that what you call "dirty reads" are mostly the correct reads. Oracle method IS the dirty read.

I am sure that your users does at least 1000 commits to every rollback. So when oracle gives you the data it already knows that this data is wrong. If you do the query again a minute later you will get new results that were available when you did the original query but were committed later. So you get a 1000/1 chance to get incorrect data.

The "dirty read" method, on the other hand, gives you the current values, believing that they will be committed in a moment. So you get 1/1000 chance to get wrong data.

Which odds will you bet on?

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Tuesday, October 29, 2002 2:18 PM

List,
I'm always keen to refresh on database comparisons so thanks for everyone's pointers.

I'm surprised Oracle doesn't make more of an issue about their locking and concurrency methods (i.e. redo/rollback/undo).

MSSQL seems to deal with it in two ways: Default: readers and writers prevent writers from accessing data until they are finished with it!
Other method: no control, you just get dirty reads!

Anyone got anything to add to this? Or am I wrong?

• Mike.

**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jay Hostetter
  INET: jhostetter_at_decommunications.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).