Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

In article <3482E5A7.30EDA94_at_NOSPAM.King.of.MyDomain.NOSPAM.Segel.com>, Michael Segel <Mikey_at_NOSPAM.King.of.MyDomain.NOSPAM.Segel.com> wrote:
>
>
>Pablo Sanchez wrote:
>
>>
>>
>> I have yet to see you refute any of my points regarding row
>> level vs page level locking. For a well written
>> application, and isn't that what you strive for?, it simply
>> doesn't matter.
>>
>
>Gee, well, I guess you talked me in to this.
>
>Try writing a hotel reservation system for a major national hotel chain.
>Read: Hyatt or something like that. ;-)
>
>Try booking a reservation in a major city like New York.
>(Multiple properties, multiple nights and multiple rates.)
>Oh, and lets not forget the multiple DSRs (Dumb Shit Receptionists) who are
>trying to access this system..... ;-)
>
>Now I am sure that you can do a work around, to COMPENSATE for page
>locking, but why? Row level locking is a much simpler solution. Of course it would be nice to
>allow for locking within a nested transaction, and even allow for nesting of transactions.
>But hey that's beyond the immeadiate question.
>
>Lets also look at other applications where row level locking is important.
>
>Hmmm, OK, how about in the financial industry?
>In a mortgage generation application ?
>
>Or how about in a travel industry application like a flight reservation system?
>Or in the telecommunication industry?
>Or in a realtime inventory control / POS system?
>
>True, you can write these with page level locks, however you won't get the performance,
>and you will have to write extra code to compensate.
>

This is not true. The TPC-C benchmark is just like any of these. It has a fixed number of warehouses, districts, and customers all being updated simulatenously by multiple people. Sybase, with its page-level-locking, has the best non-cluster TPC-C number in the industry beating Oracle, Informix, and DB2.

The TPC-C benchmark has lots of updating/inserting and uses repeatable-read locking. Page-level locking is not an issue, but a performance benefit. I've actually used Page-Level locking on Informix on this benchmark and gotten a substantial speedup over the overhead intensive row-level locking. So if you want performance, page-level locking is the only way to go.

No extra code is needed to compensate for lack of row-level locking for the TPC-C application.

Basically page-level locking is for power users that know what they are doing and know how to program well (to avoid locking issues). Row-level locking is for people that don't really know what they are doing. So there is a place for row-level locking, but you can always do better with page-level locking (there is always less overhead).

>Sorry Pablo, defend Sybase on another point. Surely there are things that Sybase does that
>Informix can't right? I mean, take Oracle for instance. They have some neat indexing
>techniques that Informix lacks.
>

What happens when a database company comes out with column locking within a row (lets take row-level locking one step further)? Are all databases that use row-level locking obsolete? Am I unable to write good without this new locking scheme? So you see, row-level locking is just another level of granularity (vs page, table, and database). A Sybase "page" is only 2KB of data (well it can go up to 16KB now with 11.0). This is tiny, given systems are shipping with 16GB of memory. I find it hard to believe that apps REALLY need more granularity than 2KB for their data. All this row-level locking stuff is just a Buzz-word that some good marketing type put out (kindof like "Java" or "Threads" :) ).

The bottom line is that all of these "row" locks take up memory and cost performance. Why grab a row-lock when a page lock does just fine? Why grab a "page" lock when a table lock would do just fine? There are so many other techniques that can be used to eliminate the "page" contention that row-level locking is trying to solve without actually using row-level locks (ie clustered indexes, limiting the number of rows per page, partitioned tables, etc).

--Chris
My own views. Received on Tue Dec 09 1997 - 00:00:00 CET