Re: Which one is better, Oracle or SQL server - clear answer !

Guido Stepken <stepken_at_little-idiot.de> wrote in message news:<bhndan$ldd$05$1_at_news.t-online.com>...
> Hi, Arsalan !
>
> This question is quite clear to me. From technical point of view, SQL
> Server seems to be quite fast in benchmarks, but 3 of four transaction
> modes cause heavyest locking on row entries. Only UNCOMMITTED does not
> lock, but there you cannot avoid dirty entries and phantom data. And, if
> load increases, SQL Server 2000 automatically switches to table locking,
> microsoft calls ist dynamic locking. Because overhead in locking row by
> each is increasing very much (3 times as long), they have decided that
> this converges to table lock. This is quite true, but then MS SQL Server
> shows the same behaviour, as the free MySQL database server. At more
> than 5-10 clients simulaneously accessing, the server seems to hang
> without load. Online (full) backups are not possible, only with tricks,
> because datasets will be blocked during backup. Lock escalations occurr
> very often, as you can see in the newsgroups. To keep a transaction as
> short as possible, to avoid lock escalation, as microsofts always
> suggests, is not possible in complex applications. Adding a 2nd or 4th
> processor and cluster or load balancing will worsen this behaviour.
>
> Oracle therefore has a mechanism, called MVRC, multi versioning read
> concurrency. Every dataset gets its own copy for writing during a
> transaction. Within a transaction no read locks will be set, only at the
> end, when a dataset has to be written. So a writing transaction will
> never block a reading transaction and vice versa.

>
> Oracle hasn't really made it perfect, because a write transaction can
> block another writing transaction.

you want a write transaction to block a write transaction of the same record. if not you end up with dissappearing updates. Its essential for consistency and data integrity.

>
> PostgreSQL has MVCC, multi versioning concurrency control. No writes,
> updates, reads block each other. You can program PostgreSQL in that way
> as:

thats not good. user A does an update and does not commit, user b does an update and commits... lets say the update looks like this.

update table
where col1 = col1+col2;

you end up with skewed data. very bad.
>
> 1. clients read a dataset.
> 2. the operator changes some data in mask, this can take time, as you know
> 3. then you set a lock, open a transaction, the dataset is tested on
> changes, if nothing has changed you update, release the lock and commit
> the transaction. This is done in milliseconds.

how robust is postGre SQLs procedural languagE? can you do stored procedures, triggers, etc... ? Just curious. If not, then you really dont want to do anything high end with it. Implementing business logic in C or Java and accessing the database leads to slow development times and long debug sessions.

>
> Sure, this you can do also with ms sql server, and you always should do
> so with other servers, but - if there are thousand of transactions
> running every minute, a lot of selects are done. MS SQL Server always
> locks (3 of 4 transactions), so the chance, a lock escalation occurrs,
> becomes bigger and bigger the more clients are accessing. At 10+ ACCESS
> clients entering data in complex database with many plausibility checks
> MS SQL Server shows problems with lock escalations, dynamic locking ....
> The problem worsens with introducing foreign keys, trigger, views.
>
> PostgreSQL has proven to be very fast, has a very orthogonal command set
> (you can combine all commands, as you like), has everything, MS SQL
> Server has and even MVCC.
> And you can use PostgreSQL for 24*7 jobs. Clustering, load balancing,
> online backup can be done without problems.
>
> Informix 9.30/9.40 and Inprise (Borland) Firebird also have MVCC.
>
> If you are really interested, read my thread on MVTO. Here you can see
> in detail, how to program without having to lock - never .....make your
> database run like hell on heavy load, complex data .....
>
> Stable are all database servers.....
>
> By the way, who knows, what OO databases have what sort of locking ? Do
> they need locking ?

The only place Ive seen OO databases used are on high end scientific systems. They seem popular there. The atom smasher at stanford uses a database called Objectivity. There docs arent very good so I dont have a good feel for it.
you know anyone else using OO databases?

>
> regards, Guido Stepken
>
>
> Arsalan wrote:
> > Which one is better Oracle or SQL server ? and why ?
> >
> >

its not quite as straight forward as you m ake it out to be. They have different features. I would be inclined to use SQL Server on low end projects since its cheap and easier to learn how to use. I would not use it on high end systems for one primary reason, it only runs in Windows. You cant run Solaris or IBM servers with it then. You really want your high end servers on Unix.

BTW, what platforms do postGre SQL and Informix port to? Isnt informix dead? IBM bought it and are shutting it down right?

> >
> >
Received on Mon Aug 18 2003 - 05:59:28 CDT