Re: Guidelines to a decent support of surrogate key implementation

On May 24, 9:27 am, "Brian Selzer" <b..._at_selzer-software.com> wrote:

> CON
> Keys can change over time. Consider the following:
>
> R{A, B, C} | AB --> C, and several other relations referencing R[AB]. R:
> {A:1, B:2, C:3}
> {A:2, B:2, C:2}
>
> T1: User 1 reads row {A:1, B:2, C:3}, starts a long-running calculation.
>
> T2: User 2 updates row {A:1, B:2, C:3} to {A:3, B:2, C:3} updates cascade
> throughout the database. R:
> {A:3, B:2, C:3}
> {A:2, B:2, C:2}
>
> T3: User 3 updates row {A:2, B:2, C:2} to {A:1, B:2, C:3} updates cascade
> throughout the database. R:
> {A:3, B:2, C:3}
> {A:1, B:2, C:3}
>
> T4: User 1 updates row {A:1, B:2, C:3} to {A:1, B:2, C:4} -- BUG!!!!

 All your example does is show that users who don't understand isolation levels will create bugs.

 If User 1 was planning to update the tuple they had read, then they damn well better ensure that their read is repeatable. This would block User 2 and User 3.

  How is this any different than the case where you choose to concatenate R[A,B] into a single attribute? Let me repeat your example

R { A, B } A->B

[ A:1, B:1 ]
[ A:2, B:2 ]

 T1: User 1 Read(A:1,B:1) (Wander off to compute F(B:1) )
 T2: User 2 Update(A:1,B:1) -> (A:3,B:1)
 T3: User 3 Update(A:2,B:2) -> (A:1,B:2)
 T4: User 1 Update(A:1,X) -> (A:1, F(B:1) )  <-- Bug

 Let me also say that the biggest PRO of Cimode's Approach # 1 is purely performance. It would be simpler and smaller (fewer indices) and would make all kinds of nice things possible wrt.physical query plans.

  Why has it not been done? Because making this change involves serious surgery to the DBMS. Try getting that past the bean counters .... Received on Thu May 24 2007 - 20:14:40 CEST