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

In article <66kmh0$f78_at_cello.hpl.hp.com>, ruemmler_at_cello.hpl.hp.com says...
>
>In article <3482E5A7.30EDA94_at_NOSPAM.King.of.MyDomain.NOSPAM.Segel.com>,
>Michael Segel <Mikey_at_NOSPAM.King.of.MyDomain.NOSPAM.Segel.com> wrote:
>>
>>[.. description of a system possibly needing RLL-locks ...]
>
>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.
>
Apart from, as has been pointed out by others, that the TPC-C probably can't be used to single out a statement concering PLL/RLL, due to too many variables, but even if true the TPC-C application only tests an ever increasing load of very short transactions. I believe locking granularity does not become an issue here, instead the overhead of acquiring locks takes precedence.

I have stated before in this thread that I do not hold the TPC-C application to be typical for _all_ OLTP applications, it is only typical for _some_ and it can therefore not be used as a proof that _all_ OLTP applications will behave like it.

So, *are you listening Anthony*, I propose the following test application, with an ever increasing transaction complexity, to show that the finer granularity of lock the better performance, _for this type of application_.

In the following, any similarity to any real application is purely coincidental and not intended. The application is purely hypothectical and I have not tested it in practice. It is intended to illustrate under which circumstances I believe locking granularity becomes an issue.

Consider a traffic monitoring system, be it trucks, trains, cars, airplanes or whatever. Lets assume we have an extremely simple data model:

create table vehicle(
 id integer not null primary key,
 lat char(10),
 long char(10),
 ltime date year to fraction
);

Assume that we have two types of 'users'. One feeder and one reader.

The feeders:
The feeders could be regional traffic 'followers', transponder signal collectors, radars or whatever. The feeders send, as one transaction, a collection of tuples to the database containing the current situation. Each tuple has: (id, lat, long, observationtime, NewFlag, RemoveFlag)  

The feeder transaction will therefore be to: begin work
update all vehicles for tuples with NewFlag,RemoveFlag == FALSE delete all vehicles for tuples with RemoveFlag = TRUE insert new vehicles for tuples with NewFlag = TRUE commit work

NOTE!
It is not acceptable to use one transaction per tuple received, all or none must be registred.

The readers are typically operators that are responsible for a given geographical area.

The reader transaction will be therefore be a simple select from the vehicle table restricted by coordinates, ie:

	select * from vehicle 
	where lat > x1 and lat < x2 and long > x3 and long < x4 

Now there are three essential variables to manipulate and two variables to measure.

M1 - The number of feeders
M2 - The average number of tuples sent from one feeder as one transaction.
M3 - The number of readers

V1 - The average response time for a reader select. V2 - The average throughput time, ie the time it takes from a feeder sends

     a tuple until it can be fetched by a reader.

It is important to keep the average number of rows fetched by reader constant. It is assumed that the feeders are sending new transactions repeatedly with a constant delay, small relative to transaction time, in between.

My assumption is that when the feeder transactions start to get longer in real time, both response time and throughput time will decrease more on a system having only page locks than on a system having row locks.

This seems obvious to me because when one feeder deletes or updates a vehicle, all vehicles on that page will be locked for both(?) other feeders and all readers until the transaction is committed. The risk of deadlocks among feeders in a PLL system is also an issue worth watching out for.

/Johan

-- 
 ________________________________________________________________________
| >>> The opinions herein are mine and not neccessarily my employers <<< |
| Johan Andersson, Msc CSE                               jna_at_carmenta.se |
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~