Re: Newbie question about db normalization theory: redundant keys OK?

David Cressey wrote:
...
> I'm not sure how to interpret the above.
>
> In an airline reservation system, you might have the following code to keep
> track of the free seats:
>
> set FreeSeats = FreeSeats -1
>
> with the appropriate context. The DBMS needs to serialize these updates (if
> they are for the same flight). Failure to do so would result in a phantom
> update. That is, two concurrent updaters might each subract one from five
> yielding four, instead of the correct answer which is three if one update
> uses the output of the other, as it will if they are serialized.
>
> From your previous writings, I'm just about certain you know this already.
> But I'm at a loss to understand what you might have meant in the above.
>
>

I'm assuming that by "appropriate context" you would accept "FreeSeats" as a single database variable. In the context of a single database store and engine, at least one that supports physical features such as logs or checkpoints, concurrency is a myth, what is written to a log, to a memory cache is serialized (often the term used is "synchronized") and that physical action is usually measured in milliseconds or less, ie., the physical locks as opposed to logical locks in a pessimistic scheme are extremely short-lived. I believe the same comparison is apt between optimistic journaling such as Oracle might use and the physical synchronization that Oracle would use.

To use this example, application-based locking might send the following "atomic set of statements" to a dbms:

Freeseats = 5 AND set Freeseats = 4;

If the very next subsequent "message" to the dbms were identical, it would be rejected, but if some intervening message happened to set Freeseats back to 5, it wouldn't!

Note that I would not necessarily expect an application developer to need to code the above.

Until sometime in the 1990's, all the big-name databases used quite primitive locking methods based on physical coordinates, none of them could lock, say, a Customer row where Custid = 2532. (Note that as above these mechanisms were quite separate from physical memory or disk synchronization ones). Gray called this a "predicate lock" but it's not clear that he had relational predicates in mind. He was right when he said that a logical lock manager could be a black hole. I knew one developer, former professor at that, who may have been going mad before he was told to enhance the predicate lock manager of an obscure product to support ranges. This may have tipped him over the edge. The last time I bumped into him, he couldn't tarry for long as the secret police were hot on his tail. Received on Thu Dec 27 2007 - 20:19:56 CET