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

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.

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

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.

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 ?

regards, Guido Stepken

Arsalan wrote:
> Which one is better Oracle or SQL server ? and why ?
>
>
>
>
Received on Sun Aug 17 2003 - 03:09:39 CDT