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

In article <MYJ26DA7yyg0EwI1@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> wrote: >In article <yutra7x9dht.fsf@mew.corp.sgi.com>, Pablo Sanchez ><pablo@sgi.com> writes >>>>>>> "Michael" == Michael Segel <Mikey@NOSPAM.King.of.MyDomain.NOSPAM.Segel.com >>> writes: >>Michael> >>Michael> [ examples deleted ] >>Michael> >>Michael> True, you can write these with page level locks, >>Michael> however you won't get the performance, and you will >>Michael> have to write extra code to compensate. >> >>What do you base your assertion that "you won't get the >>performance"? The applications you talked about are all >>applications that require a highly tuned application in >>order to get excellent performance. >> > > Imagine 1 page with 2 rows one it. > > 1. Page level locking > > User A updates row 1 > At the same time user B updates row 2. User B has to wait for > User B to commit. > > 2. With row level locking > User A updates row 1 > User B updates row 2 (with no waiting). > > How can 1 be faster than 2 if user B has to wait? > The fact is that user B probably never has to wait because the chance that user B and user A are accessing their data at exactly the same time is very small. How often do you think that I access my ATM account at the same time you do? Now if this happened to be a table where every row is accessed at a very high frequency, then you just put 1 row per page and get on with life. If every row is accessed at high frequency, I doubt the table is very large in size. So 1 is faster because less resources (and code) is used to lock the row vs locking the page. To lock the row you most likely have to grab a "page" lock first before getting the "row" lock. More overhead means less performance. >>If you look at the TPC-C's (as I've mentioned, before) >>you'll see another highly tuned (OLTP) application. If I >>compare the *currently* submitted numbers for Sybase >>vs Informix I see the following: >> >> Sybase..... 39,469 tpmC >> Informix... 24,309 tpmC >> >>That's a 62% performance increase by using Sybase. Now I >>grant you that I believe that we'll see Informix >>Inc. publishing even higher numbers but that's not my >>point. My point is: >> > I checked the results, these figure come from different machines. > So Sybase runs their TPC benchmarks on larger hardware..so? > Actually, the Informix box used 28 processors, the Sybase box only used 16. What's your definition of larger? >> For a finely tuned application, row level locking >> does *not* matter. > Lock granularity is still a problem. Why do UNIX kernels lock > individual files and data structures rather than having one large > kernel lock? Try reading > Well one large kernel lock would be equivalent to locking the entire database! I don't think page level locking does this :). Locking individual files is like a table lock, so looking pretty good so far. Locking individual data structures is a page lock so still looking good. What is the issue again? Row-level locking would be like locking every single cache-line in the kernel. I don't know of any OS that is dumb enough to do that. > UNIX Systems for Modern Architectures > Sysmmetric Multiprocessing and Caching for Kernel Programmers > by Curt Schimmel > Addison-Wesley > ISBN 0-201-63338-8 > > "If the processes in the application job mix use seperate kernel > resources, each of whose data structures are protected by separate > locks, then these processes will not contend for the same locks and > will be able to run simultanueously on different CPUs, whether they > are in user or kernel mode" > > Translate to databases and internal data structures. > See above for the translation. --Chris My own views