RE: SQL-Server Presentation/Reality Check

Befor slamming sql-server you'd better look at how Oracle works. Oracle's default is statement level read consistency. That is a statement will not see data that was changed by another user after the statement was issued whether that data is committed or not. However, if the other user commits the data, and the first user issues the statement again, the changed data will be seen. The read was not repeatable. A transaction can contain several selects. If another user changes and commits the data between these selects the transaction is not read consistent. if you want transaction level read consistency you need to use "set transaction read only", I believe that's the syntax. There are restrictions on doing so such as the transaction cannot make any changes. It also puts a load on the rollback segments. How does Oracle's statement level read consistency differ frol SQL-Server's "READ COMMITTED". Are you saying that a sql_server query will see committed changes made! !
 after
the query started. That it doesn't even provide statement level read consistency.

I'm not sure what is meant by phantom. Is it that users making changes cannot perform repeatable reads because they their own uncommited data. This is how Oracle works as well.

Doesn't 9i give you the ability to do dirty reads, if you want. I have heard rumors to that effect.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu  

-----Original Message-----
Sent: Wednesday, June 27, 2001 3:50 PM
To: Multiple recipients of list ORACLE-L

>From SQL-Server BOL (Books on Line)

"READ COMMITTED Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

READ UNCOMMITTED Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels."

Note in the above 'nonreaptable reads'
and 'phantom data'. Again, from BOL:

nonrepeatable read:
When a transaction reads the same row more than one time, and between the two (or more) reads, a separate transaction modifies that row. Because the row was modified between reads within the same transaction, each read produces different values, which introduces inconsistency.

phantom:
By one task, the insertion of a new row or the deletion of an existing row in a range of rows previously read by another task that has not yet committed its transaction. The task with the uncommitted transaction cannot repeat its original read because of the change to the number of rows in the range. If a connection sets its transaction isolation level to serializable, SQL Server uses key-range locking to prevent phantoms.

My observations:
Note that setting the transaction isolation level to serializeable may make large portions
of a table unavailable to another transaction yet that's the only way to insure no phantom
data, and is too restrictive to be the default.

Also note that unless a transaction explicitly defines 'Begin Transaction', data is auto-committed. i.e. 'update students set lname='Smith' followed by 'go' will trash your student table if you forget the 'where' clause.
You cannot issue a rollback, since you didn't define a transaction. In addition,
another transaction already reading the student table will get some percentage
of all rows as 'Smith' even though it began before the 'bad' update. Try it yourself, as a I did (I used a large 'waitfor' in the middle of a procedural loop to simulate a larger table or more per-row processing)

HTH
Still unashamedly anti-Redmond,

Ed Maurer
Sr. DBA
Acquirex                                 

> -----Original Message-----
> From: Post, Ethan [mailto:epost_at_kcc.com]
> Sent: Wednesday, June 27, 2001 2:35 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL-Server Presentation/Reality Check
>
>
> Can you provide some references to lack of support of
> read-consistency. Not
> that I don't believe you but I just read that to a guy next
> to me and he is
> clamoring for evidence. - E
>
> -----Original Message-----
> Sent: Wednesday, June 27, 2001 2:07 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Privilege ? Reality? Reality and MS is an oxymoron.
> I've had the fortune (misfortune) to become a SQL Server DBA
> in addition to my Oracle duties, due to packaged application
> requirements. As an admitted defiler of all MS stands for and
> it's dubious accomplishments, I'll offer a few grains of sand,
> most of which you might be advised to take up with management
> away from the MS marketing morons.
>
> First, be aware that MS pricing is not nearly as inexpensive
> as their hype would have you believe, once you remember that they
> have always charged for upgrades, their discounts aren't usually
> as steep, and support... well, what there is of it s****. I've
> spent 3 calls at $245.00 each only to be told each time the
> answer was to 'reboot'. This on W2K/SS2K fail-over cluster,
> with many of their best support people contributing to this
> non-solution.
>
> On a technical note: Virtually every one of their new speed
> marks of note have been done on multi-server clusters; Oracle
> still holds most single-server benchmarks. Additionally, their
> application benchmarks have no relationship to real world, as
> even their SAP benchmark was done on a modified SAP installation
> not available in 'ordinary' SAP applications.
> The overhead of multi-server/cluster management makes their
> scalability arguments, even if they could be accommodated by
> existing software, moot.
>
> Self-Tuning - Bah, Humbug. I've had the opportunity to see
> some server code from SAP, PeopleSoft and Commerce One designed
> for SQL Server. Guess what ? The real stuff, that which has to
> perform, is often hinted (statements like
> 'option (loop join , force order, maxdop 1 )' which is pretty
> much self explanatory; 'maxdop 1' = Oracle's noparallel.)
>
> Then there's the entire discussion of read-consistency, which
> SQL Server doesn't support. A whole thread there, and one
> reason why SQL Server can be very quick in some operations: No
> RBS to support. Everything is done from the transaction logs.
>
> And then there's functionality...
>
> Just my 2 cents...
>
> Ed Maurer
> Sr. DBA
> Acquirex
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ed Maurer
  INET: EMaurer_at_acquirex.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).