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

root@candle.pha.pa.us wrote: > Let's suppose an order-entry app, with a customer table that has > row-level locking, and an order table with page-level locking. > > Why can't the app do a SELECT FOR UPDATE on the customer table for the > requested customer, do a non-locking SELECT on the order table, then > UPDATES on the order table, then release the lock on the customer table? > > This would seem to be the best of both worlds, with row-level locking > overhead only on the table that needs it, and it is kept while the user > is browsing the order table. > > Can't do this without the capability of row-level locking, and the nice > thing is you can do RLL only on the tables that need it. > > How are people locking the rows while people are browsing if they use > PLL? My guess is they are using the external lock mechanisms mentioned, > like lock table with lock entries. Yes, any approach you take is possible. How effectively it works needs to be measured. I'd been thinking about the issues raised in this thread over the weekend, and came up with a new server-side locking model. Basically, it takes the idea of the "soft lock" to create an "heirarchical object lock". All elements participating in this lock would be defined either thru the table creation statement or an explicit statement to identify which column participates (in other words, how its usually defined). Only one lock need be generated, that being for the top element of the heirarchy (in the above example, on the customer). All customer-related data with a matching relational key automatically participates in the lock. The lock placement syntax would be the same, e.g. "select for update". The lock would need to be explicitly released, however. The advantage here is that only one lock is ever required. This does away with physical row and page locks, but adds an overhead in that the server will need to check keys select for locking against existing locked keys (in other words, about the same overhead as existing lock tests). The lock granularity can be enhanced by specifying which fields are likely to be affect for a table that participates in this locking model. This means that you could do column locking (someone mentioned finer locking than row level in one post in this thread). What does everyone else think? Is this feasable? -am