Re: Surrogate Keys: an Implementation Issue

From: paul c <toledobythesea_at_oohay.ac>
Date: Thu, 27 Jul 2006 16:18:40 GMT
Message-ID: <AH5yg.250333$IK3.94968@pd7tw1no>

Brian Selzer wrote:
> "Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
> news:PPMxg.21411$pu3.353655_at_ursa-nb00s0.nbnet.nb.ca...

>> Brian Selzer wrote:
>> ...

> I know what an external predicate is. What does that have to do with the
> fact that Bob just updated the wrong row? How could you prevent that in
> application code, or in the middle-tier? You can't, unless you either (1)
> lock the row until Bob gets back from Tahiti, or (2) use a surrogate to
> guarantee that the row that's about to be updated is the same as the one
> that was read out.
> ...

This paragraph shows a muddling of different ideas. Locking rows is a heavy-handed implementation technique that often locks more values than the application intends (using values in the way Bob Badour's later post does). Also, early lock managers didn't recognize external predicates so were unable to lock the non-existence of a row, maybe some are still unable to. (Maybe there are products today that will use views to 'lock' only the values an application is concerned with but I doubt it.)

Regardless, so-called surrogates aren't going to help an application whose updates depends on the non-existence of some row (logically the falseness of some proposition), so the idea that surrogates somehow substitute for a lock manager is false.

It has to be said that RT has nothing to do with 'duration' (let alone lock managers) nor does it need to. Theoretically, all an implementation needs to guarantee is 1) serialize the changes that an application desires to be made atomic, ie. give the effect of applying all changes at a point in time and 2) at update time, allow the application to re-iterate all (and only) the assertions it is depending on (not just true ones but also false ones). I doubt that there are any such rdbms's today. It is like criticism of RT because of SQL shortcomings - the lack of faithful implementations only serves to encourage naive extensions to RT.

If the Information Principle is to be followed, Bob Badour is right when he says that all keys are surrogates. However, updates that are faithful to an application need not involve declared keys (eg., keys stated in a catalogue).

So-called predicate locking techniques bring other problems, all of which have to do with implementation, not RT, for example the need for session managers that enforce a two-phase locking protocol. A thoroughly pure RT implementation would have no such component and no lock manager either.

p Received on Thu Jul 27 2006 - 11:18:40 CDT