Re: how to build a database from scratch

From: paul c <toledobythesea_at_oohay.ac>
Date: Sun, 10 Dec 2006 01:44:04 GMT
Message-ID: <EDJeh.459870$R63.239454@pd7urf1no>

vc wrote:
> DBMS_Plumber wrote:
>

>>paul c wrote:
>>
>>> I knew one advanced product, eg., it had a predicate lock
>>>manager long before any of the big-name dbms'es, that was designed to
>>>eliminate dba's.  Sales were abysmal, in part because dba's in big
>>>corporations saw this as a big threat and did every thing they could to
>>>keep the product out.
>>
>>Or, as is far more likely, your DBAs recognized that predicate locking
>>schemes - which have a 30 years history of commercial and technical
>>failure - simply cannot be used to enforce serializability over the
>>following pair of queries.

>
>
> That is not the reason System R style predicate locking is not used
> today. The real reasons have been well known for about 25 years.
> Since there has been no commercial System R p.l. implementation, there
> has been no failure either.
>
>

>>Q1:
>>
>>UPDATE Foo F
>>    FROM Bar B
>>       SET F.Val = 5
>> WHERE B.X = :VALUE
>>      AND  F.Y = B.Y;
>>
>>Q2:
>>
>>SELECT * FROM Foo F WHERE F.Y = 5;
>>
>>If you believe that you can, please explain how you can determine
>>whether the predicates "F.Y = 5" and "F.Y = B.Y" collide?

>
>
> It's easy. System R p.l. would have Pbar = true and Pfoo=true because
> of the join so the intersection of rows defined by P(foo.y=5) and
> (Pfoo or Pbar) would be non-empty thus preventing the update or select
> depending on whichever comes last.
>
> Besides, with your SQL, no p.l. is needed because 2P by itself is
> enough to ensure Q1/Q2 serializability anyway.
>
>

>>Which is why
>>none of the big-name DBMSs use predicate based locking.
>>

>
>
> No.
>
>

>>But so what? None of this has anything to do with how to implement
>>B-Trees. Please keep to the topic.

>
>

Thanks for that. I always thought that the premise of a p.l. is that a human could read a query for example, without executing it and predict what locks would be taken, although it's so long since I read about system R that I could be wrong about its intent. If my thought is accurate, then it is beyond me why popular products would prefer to lock after they read blocks and why any btree implementation need be concerned about p.l.

(I only brought up p.l. because it is one way to eliminate one of the curses I think come with most products - dba's who arbitrate what app developers are allowed to do based on arbitrary product behaviour (eg., if the product has already cached something, it might produce a different lock than if it hasn't cached) rather than allowing the devs' to predict a consistent behaviour for each case and this is the same kind of problem that a btree algorithm that tries to more than operate an index might create - I also want to mention why I think updates should be two-pass since this all reminds of that thought, but I'm afraid I can't write it down very clearly at the moment.)

p Received on Sat Dec 09 2006 - 19:44:04 CST