Re: Sybase vs Oracle - which is better?

carl christianson wrote:
>
> I would tend to disagree that row level locking is only for "badly written"
> applications.
> An example from a previous job would be a Point of Sale system.
> In one department of this application would be preparing and shipping out
> equipment.
> Every morning new work would get loaded into the system by a batch process.
> Workers would then work this data to get it shipped out the door. Since the data
> is loaded in by
> a batch process it is put into the same data blocks. Since we had a 4K block size
> and the average
> row length is around 200 bytes we would have around 200 rows inside one block.
> I'm not sure how small your page locks could go but if I locked 200 different
> orders, remembering that since the data is all residing pretty much together I
> just put a major choke hold on the work flow for this process.
> Once this equipment is shipped out the help desk would tend to get more calls on
> the new clients and newly shipped pieces of equipment. Once again this data is
> pretty much grouped together in the database. If each helpdesk rep locked a
> couple of hundred rows when they are updating a client's information that would
> mean that other help desk reps would have to wait until they can do the same.
>
> So I guess I'm wondering how Sybase would deal with this problem and how small can
> a page lock be?
>
> regards
>
> Carl Christianson
>

Currently, Sybase page sizes are fixed at 2k (except Stratus which is 4k) which allow a max of about 2004 bytes of data per page. NOTE: An individual row can only be 1962 bytes due to trans. log limitations which is a definite limitation in Sybase.

Sybase has the concept of clustered indices (1 per table) which are similar to Index Only Tables (IOT) in Oracle 8. This would allow you to physically group related rows together on disk. Clustering a sequential key is common problem made by inexperienced Sybase DB designers. Clustered indices allow us to organize how data is physically loaded into a table.

Also, in Sybase I can partition a table to allow multiple page/block chains which create multiple insert points for a table in such a method that data is added in a round robin type approach to each table partition. Clustered indices and partitions are currently mutually exclusive.

Either one of these methods could greatly decrease the problem mentioned above. There are times when row level locking are required and prior to 11.9 of Sybase this had to be implemented via the MAX_ROW_PER_PAGE option or some other method. An important point is that most tables don't require row level locking. In 11.9 of Sybase, there are 3 locking methods: allpages (pre-11.9 method), datapages (separate index updates) and datarows (row level locking). Each table can be setup to use the method that is appropriate to its type of accesses. This allows the advantages of row and page level locking.

Kyle Hachey,
PLATINUM technolgoy, inc. Received on Sun Nov 29 1998 - 00:00:00 CST