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

"Galen Boyer" <galen_boyer_at_yahoo.com> wrote in message news:uzmkncj93.fsf_at_rcn.com...
> On Sun, 19 Feb 2006, tonyrogerson_at_sqlserverfaq.com wrote:
>
> > You would set the database options (once) to allow this
> > functionality....
> >
> > SET TRANSACTION ISOLATION LEVEL SNAPSHOT
>
> [...]
>
> >
> > Or, if you to wait on update...
> >
> > SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> >
>
> [...]
>
> Hm... Even in your database code you are extremely cognizant of the
> database locking issues setting transaction isolation level to some
> snapshot for if you, what?, want a consistent view coming back from a
> query, then, what?, read committed? You have to ask for that? WTF!!!
> You actually have to deliberately ask for the database to only show you
> committed rows? WTF? I thought SQLServer was finally caught up to
> Oracle! Let me ask you one fundamental question.
>
> When would you ever want to read uncommitted records?
>
> There is the question. If you answer nothing else, answer that one
> simple question.
>
> > Anyway, I'm done here; you guys believe what you want
>
> We don't believe anything. We are 100% assured of transactional
> integrity when using the Oracle database server.
>
> > ; the reality is different!
>
> Yes, as always, Oracle remains far ahead of SQLServer in this
> fundamental respect.
>
> --
> Galen Boyer

Galen,
You need read committed so you can have a system that works the same as a file based database (eg Clipper or xbase et al.) The snapshot isolation thing in SS is a KLUDGE. (I thank my lucky stars Oracle doesn't allow this mode.) You have to be careful using it so you don't slow down the DB. (poor implementation) It will be rare that people will actually use it in SS because of the performance problems it will generate. It is there for marketing purposes. SO MS can say we can check that box in requirements, but in marketing presentations to companies the MS guys will claim they have the same feature as Oracle but because that feature causes performance problems in databases (lumping Oracle in and thus implying Oracle performance is poor because they use this feature. Leaving out the poor architecture decisions by MS.) no one uses it.

Also Tony is rather disingenuous (or ignorant, but I suspect disingenuous) about deadlocks. Yes, in any database one can get in deadly embrace problems caused by locking particular database objects in different orders. However, in Oracle you won't get it if you are just reading. (the bitmap index thing is silly, Oracle recommends not using bitmap indexes in OLTP systems, just in datawarehouse systems) In SS unless you enter a non-default isolation level (and one MS will say you shouldn't enter for long due to performance problems) readers will lock and cause deadlocks.

Jim Received on Sun Feb 19 2006 - 11:13:01 CST