Concurency: Oracle vs. MSSQLServer 2K

Hello Gurus,
Im currently reading a Software Engineering for Web Applications Course
which is published online at the MIT (Open CourseWare Site) and here it says
at least it suggests that concurrency behaviour is best in Oracle than in
MSSQLServer. Im SQL Programmer and essentially I love its ease of use and
Id like you guys to comment this transcript. Is it possible to get the same
results in SQLServer by fine tuning the DBMS?
"
Concurrency is Oracle's strongest suit relative to its commercial
competitors. In Oracle, readers never wait for writers and writers never
wait for readers. Suppose the publisher at a large site starts a query at
12:00 PM summarizing usage by user. Oracle might have to spend an hour
sifting through 200 GB of tracking data. The disk drives grind and one CPU
is completely used up until 1:30 PM. Further suppose that User #356712 comes
in at 12:30 PM and changes his email address, thus updating a row in the
users table. If the usage tracking query arrives at this row at 12:45 PM,
Oracle will notice that the row was last modified after the query started.
Under the "I" in ACID, Oracle is required to isolate the publisher from the
user's update. Oracle does this by reaching into the rollback segment and
producing data from user row #356712 as it was at 12:00 PM when the query
started. Here's the scenario in a table:
Time Publisher Public Web Application
12:00 PM Starts a 90-minute query summarizing usage for preceding
year --
12:30 PM usage summary continues to chug away User #356712 updates
email address from "joe@.foobar.com" to "joe@.yahoo.com"
12:45 PM usage summary arrives at User #356712; Oracle reaches into
rollback segment and pulls out "joe@.foobar.com" for the report, since that's
what the value was at 12:30 PM --
1:30 PM usage summary report completes --
How would this play out in Microsoft SQL Server? When you're reading, you
take read locks on the information that you're about to read. Nobody can
write until you release them. When you're writing, you take write locks on
the information that you're about to update. Nobody can read or write until
you release the locks. In the preceding example, User #356712 would submit
his request for the address change at 12:30 PM. The thread on the Web server
would be blocked waiting for the read locks to clear. How long would it
wait? A full hour with a spinning/waving "browser still receiving
information" icon in the upper right corner of the browser window. If you're
thoughtful, you can program around this locking architecture in SQL Server,
but most Internet service operators would rather just install Oracle than
train their programmers to think more carefully about concurrency.
"
--
Jorge Luzarraga C
"I can do it quick. I can do it cheap. I can do it well. Pick any two."SQL Server 2005 contains a feature called "snapshot isolation" that can help
you with this issue.
You really should evaluate the concurrency and data freshness needs of your
application to determine which model you want to use. I personally have
seen cases where the before image was not what the application really
needed. I've also seen cases where it was fine. So, please make sure you
pick what works for your application requirements.
Thanks,
Conor
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:ubX1kzVFGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hello Gurus,
> Im currently reading a Software Engineering for Web Applications Course
> which is published online at the MIT (Open CourseWare Site) and here it
> says at least it suggests that concurrency behaviour is best in Oracle
> than in MSSQLServer. Im SQL Programmer and essentially I love its ease of
> use and Id like you guys to comment this transcript. Is it possible to
> get the same results in SQLServer by fine tuning the DBMS?
> "
> Concurrency is Oracle's strongest suit relative to its commercial
> competitors. In Oracle, readers never wait for writers and writers never
> wait for readers. Suppose the publisher at a large site starts a query at
> 12:00 PM summarizing usage by user. Oracle might have to spend an hour
> sifting through 200 GB of tracking data. The disk drives grind and one CPU
> is completely used up until 1:30 PM. Further suppose that User #356712
> comes in at 12:30 PM and changes his email address, thus updating a row in
> the users table. If the usage tracking query arrives at this row at 12:45
> PM, Oracle will notice that the row was last modified after the query
> started. Under the "I" in ACID, Oracle is required to isolate the
> publisher from the user's update. Oracle does this by reaching into the
> rollback segment and producing data from user row #356712 as it was at
> 12:00 PM when the query started. Here's the scenario in a table:
> Time Publisher Public Web Application
> 12:00 PM Starts a 90-minute query summarizing usage for preceding
> year --
> 12:30 PM usage summary continues to chug away User #356712
> updates email address from "joe@.foobar.com" to "joe@.yahoo.com"
> 12:45 PM usage summary arrives at User #356712; Oracle reaches
> into rollback segment and pulls out "joe@.foobar.com" for the report, since
> that's what the value was at 12:30 PM --
> 1:30 PM usage summary report completes --
> How would this play out in Microsoft SQL Server? When you're reading, you
> take read locks on the information that you're about to read. Nobody can
> write until you release them. When you're writing, you take write locks on
> the information that you're about to update. Nobody can read or write
> until you release the locks. In the preceding example, User #356712 would
> submit his request for the address change at 12:30 PM. The thread on the
> Web server would be blocked waiting for the read locks to clear. How long
> would it wait? A full hour with a spinning/waving "browser still receiving
> information" icon in the upper right corner of the browser window. If
> you're thoughtful, you can program around this locking architecture in SQL
> Server, but most Internet service operators would rather just install
> Oracle than train their programmers to think more carefully about
> concurrency.
> "
> --
> Jorge Luzarraga C
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
>|||> Im currently reading a Software Engineering for Web Applications Course
> which is published online at the MIT (Open CourseWare Site) and here it
> says at least it suggests that concurrency behaviour is best in Oracle
> than in MSSQLServer. Im SQL Programmer and essentially I love its ease of
> use and Id like you guys to comment this transcript. Is it possible to
> get the same results in SQLServer by fine tuning the DBMS?
Well, Oracle and SQL Server both appear on this list three times.
http://www.tpc.org/tpcc/results/tpc...=5¤cyID=0
Yes, Oracle appears above Microsoft, but two notes:
(a) Oracle's most recent data is from April 2005
(b) Oracle' average cost is almost 19% higher than SQL Server (avg $7.31 vs.
$6.15), so yes, you can get a bit better performance (even if you believe
the data is valid after that much time) but it's going to cost you a lot
more up front to build the system.
DB2 is at the top of the list, so why does this article choose to only
compare Oracle and SQL Server? Wait, let me guess, it was written by
someone on Oracle's board, or Oracle has made huge investments in MIT's
labs, or...

> How would this play out in Microsoft SQL Server? When you're reading, you
> take read locks on the information that you're about to read. Nobody can
> write until you release them.
That's only with certain isolation levels. You can certainly do a lot of
reading without blocking anyone, either with dirty reads (a.k.a. NOLOCK) or
with the new snapshot isolation in 2005.

> but most Internet service operators would rather just install Oracle than
> train their programmers to think more carefully about concurrency.
Is there any evidence to back this up? How many service providers do you
know of, that provide Oracle as the back end and say, go ahead, build your
database and application around it? I've never heard of one.
There are plenty of ways to design a well-performing application, and while
database vendor choice is certainly a significant factor, it's not the only
one, and it's not the one you end up spending the most time on.
A