Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Surrogate Keys: an Implementation Issue

Re: Surrogate Keys: an Implementation Issue

From: paul c <>
Date: Sat, 29 Jul 2006 00:32:01 GMT
Message-ID: <50yyg.257305$Mn5.62721@pd7tw3no>

Bob Badour wrote:

> paul c wrote:

>> Bob Badour wrote:
>>> paul c wrote:
>>> ...
>> I meant, for example, that it should be possible to avoid having
>> logical lock manager and session manager components in an rdbms, so
>> users would be unconcerned with what arbitrary locking techniques a
>> system chooses.
>> ...
> Again, I am not sure I understand what you are trying to say. A dbms 
> must provide some facility to manage concurrency regardless whether the 
> dbms is an rdbms.

I guess that could come across as a little vague. I think it's the introduction of 'locks' that I am objecting to because I think you cannot get completely precise 'granularity', to use a term that concurrency books mention, without effectively duplicating a good deal of what the dbms is doing for other reasons. Assuming a strict two-phase lock protocol to illustrate, if I intend to update the bonus for all females older than 50 as of January 1, a predicate lock manager would have to save a predicate looking something like "P{sex = F and dob  >= 28071946}", at least until COMMIT time and until then compare every query against it, including ones that might have a predicate such as "P{surname = jones)".

I would rather think of a commit as the assertion of the predicates my particular application depends on, in the form, say of a single logical 'commit message' that combines the changes I want. It might look something like "P{sex = F and dob >= 28071946}; P.bonus(sex = F and dob  >= 28071946) = bonus * 1.10;". The predicate effectively becomes an application constraint.

(The dbms might re-do work from two weeks ago, assuming that the operator went on vacation before finalizing the transaction, but that is not the system's fault. This work might be more than offset by the savings of not having a lock manager check every other query against that lock predicate for two weeks!)

It's true that the original query may have displayed data for people who   two weeks later are no longer employees and failed to display people who became employees while the operator went on holiday. If it is a business rule that all and only those employees displayed on the operator's screen be given bonuses (perhaps he writes them down), then the commit message would have to be much more detailed, perhaps iterating all the before-images of the rows that are to change. It might also be a requirement that no other application can see those rows while the operator is looking at them. In a conventional dbms what are sometimes called intention locks give this side-effect. However, if it is a business requirement then I think it what the affected applications must recognize and be programmed to reflect.

Yes, a dbms must "provide some facility" but I would say the above loose description while seeming to avoid concurrency entirely, is dealing directly with the need for logical integrity. So I think a dbms would also need to give a way to merge db constraints en masse with the above application constraints. In fact, I'd go so far as to say that other kinds of constraints, such as user security could be handled the same way.

p Received on Fri Jul 28 2006 - 19:32:01 CDT

Original text of this message