Re: row level vs page level locking is it more than marketing hype? was Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

>>>>> "Jean-Marc" == Jean-Marc van Leerdam <Jean-Marc.van.Leerdam_at_xxremovexx.ingbank.com> writes:

Jean-Marc> 
Jean-Marc> In my opinion, to summarize the current status quo:
Jean-Marc> 
Jean-Marc> 1. We all are against a row-at-a-time approach to application design
Jean-Marc> 2. We all see PLL schemes perform better than RLL schemes
Jean-Marc> 	(not going into the reasons why...)
Jean-Marc> 3. We all see RLL advantages in that it gives a better relational/
Jean-Marc> 	setwise granularity with less unintended dependencies between rows
Jean-Marc> 	in a table.
Jean-Marc> 

I concur with your points one and two (and of course) not three. That is, the type of granularity that you are describing isn't fine enough such that RLL would give you a benefit. If we were talking about something like database locking vs PLL/RLL then I'd concur with item number three.

Now, to restate my position, I have *never* said that RLL is not good, what I have said is that RLL vs. PLL isn't worth all the hype that the RDBMS vendors would like you to believe. That's my point... I argue this position using a normalized OLTP application: TPC-C's. With the TPC-C's, it's shown that an RDBMS doing page-level locking can outperform an RDBMS doing row-level locking (at this point in time*).

When it comes to performance, what I look at are two issues:

        Bandwidth vs. Latency

Bandwidth is pretty easy to solve: faster network, wise use of stored procedures, scalable hardware, appropriate distribution of data (be it 'data' and/or 'log) on disk... anyway, you can Throw Hardware At It [tm] for this type of solution.

Latency is the tougher one to deal with. Whether it's how the RDBMS implements its own mutex'ing or the application holding locks too long or row-at-a-time processing or the O/S not scaling with a system call's invocation ... you get my drift... it's a tough cookie to solve because it requires code changes: app, O/S, RDBMS

(For the record, our group is responsible for the porting of Informix/Oracle/Sybase to our platform... I get to see the problems involved with all three RDBMS... hence, why I can have no opinion on which is "best").

If we have a problem where the application is holding locks for too long, it's not going to be solved with RLL. It's going to be solved by educating the app developers and re-writing those transactions. Because as those who are familiar with performance and tuning, it's a process of refinement.

Live with this motto and you'll live a long and wonderful life:

   For an OLTP app, short transactions are a Good Thing [tm].

• - TPC-C's are somewhat worthless, IMHO, because they really are just snapshots in time and they aren't too realistic:

        o using only about 300MB out of a 4gig drive
        o a >20 *minute* checkpoint

    Take a look at some executive summaries in www.tpc.org

-- 
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821
pablo_at_sgi.com              | Pg # (800) 930.5635   -or-   pablo_p_at_pager.sgi.com
-------------------------------------------------------------------------------
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]