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

In article <3482E5A7.30EDA94@NOSPAM.King.of.MyDomain.NOSPAM.Segel.com>, Michael Segel <Mikey@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.