Re: Surrogate Keys: an Implementation Issue

Bob Badour wrote:

> paul c wrote:
> 

> 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 Sat Jul 29 2006 - 02:32:01 CEST