Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec

Serge Rielau wrote:
Mark Townsend wrote:

Serge Rielau wrote:

Galen Boyer wrote:

On 19 Feb 2006, galen_boyer@xxxxxxxxx wrote:


When would you ever want to read uncommitted records?



Uncommitted read is just fine for anything statistical.
When mining a DSS or ODS system there is no need to get exact data.
Whether someone returned a pair of shoes or not is irrelevant for trend analysis.
Does Oracle support query sampling? If so, there you go...



So IBM never recommends UR for DB2 LUW except for situations where users can tolerate wrong results (i.e data sampling ?).

Just like Oracle never recommends bitmap indexes in OLTP..
So _barring_stupidity_ I'd guess that's correct.
I can't warrant for 160,000 IGS employees ;-)

And presumably you would advise MS users to not use UR as well ?

Unless they can tolerate the implication. Just like I wouldn't recommend an Oracle customers snapshot isolation if their transaction must be serializable.

What is the default isolation level for DB2 LUW ?

Cursor stability (aka read committed).
This is also by far the most popular isolation level on DB2.

> What is the default isolation level for SQL Server 2005 ?
I have no clue. But apparently I'm on my way to becoming a SS2005 expert in no time in this group. ;-)

I find it highly amusing how posters justify isolation levels based on locking behavior.
Isolation is semantics, locking is implementation.
There are quite viable solutions for READ COMMITTED isolation level which have the exactly same concurrency behavior as Oracle's implementation of Snapshot Isolation.



I'm sorry - but who's implementation of RC is the same as Oracle's MVRC ?

And here goes another twist. == Concurrency behavior <> implementation. Not even <> isolation level.

SQL Server 2005, unless i'm gravely mistaken suppors two new twists.
One of them is MVRC, the other is last committed without readers blocking writers.
The point being made (at least as I understand it) is that SQL Server's IMPLEMENTATION of MVRC does not scale and is slow for unspecified reasons and that last committed is a bad isolation level BECAUSE of readers blocking writers.

So.. is there any interest in bringing the debate back to a technical discussion?
Obviously I am not trolling for M$. I'm really interested on what folks perceive as the issue.

Cheers
Serge

Having looked at M$'s implementation in my lab my impression is that
they have bolted MVRC on using scratch tables and huge amounts of CPU
just to be able to claim they can now do what they used to claim was
an Oracle weakness.

The average SQL Server developer/DBA doesn't have the skill set required
to understand and use it.

My impression is that it is there for marketing purposes only. No doubt
they will have a checkbox on a page with three columns something like
this:

SQL Server Oracle DB2
Tables Yes Yes Yes
Indexes Yes Yes Yes
MVRC Yes Yes No

Followed by some nonsense pretending they have equivalent functionality.
Microsoft has never been about quality software ... rather quality
marketing.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.

Relevant Pages

  • Re: isolation level is set to repeatable read
    ... Tibor, I am just suggesting that, if the isolation level is set from within ... SQL Server, then you will see the command in Profiler. ... > The only way to change this inside SQL Server is to use SET TRANSACTION ...
    (microsoft.public.sqlserver.server)
  • Re: change default isolation level on sql server 2005
    ... Have you actually set the database to Read_only? ... I believe SQL Server does some internal optimizations if it knows there will never be contention. ... I am also sure that there would be no way to change default isolation level ... "Kalen Delaney" wrote: ...
    (microsoft.public.sqlserver.programming)
  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... Please post your DDL and SQL so we can all take a look - thankyou. ... Just like Oracle never recommends bitmap indexes in OLTP.. ... This is also by far the most popular isolation level on DB2. ... > What is the default isolation level for SQL Server 2005? ...
    (comp.databases.oracle.server)
  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... When mining a DSS or ODS system there is no need to get exact data. ... This is also by far the most popular isolation level on DB2. ... SQL Server 2005, unless i'm gravely mistaken suppors two new twists. ... One of them is MVRC, the other is last committed without readers blocking writers. ...
    (comp.databases.oracle.server)
  • Re: many many concurrent users
    ... What they probably refer to is that Oracle has a snapshot isolation level This mean that writers doesn't block ... SQL Server uses simple blocking, which mean that the app developers has to make sure that they are using short ... transactions to avoid blocking when you have many concurrent users. ...
    (microsoft.public.sqlserver.programming)