Re: The Practical Benefits of the Relational Model

"Jan.Hidders" <hidders_at_hcoss.uia.ac.be> wrote in message news:3db5c972$1_at_news.uia.ac.be...
> If you have a shared budget that you buy the stocks from then the actions of
> (1) reading the current budget and (2) writing the new budget, should not
> allow reads and writes of other transactions between them. It's the
> classical example of a concurrency problem.

Yes that is the classical example but it does not show that transactions are *required*. I'm saying that problem is more flexibly solved by database design rather than by transactions. I guess I could concede that it makes database design more difficult, but I see that as a price that we need to pay to free us from some users abusing transactions. Moreover, I don't believe that a database is a complete description of a business problem unless the locking semantics are explicit, and following the information principle, explicit in the database (design) itself.

one of many possible db designs would be

STOCK (S StockSymbol, Price Money)
BUDGET(M Money)
BUDGET_LOCK(L Boolean, U User)

check(NOT (M <> M' AND L AND U <> USER()) )
check(NOT (L' AND NOT L AND U <> U') )
check(NOT (L' AND M <> M' AND L) )
check(NOT (L <> L' AND U <> USER()) )

Using a single quote to indicate 'previous values' (and so breaking my own rule about modelling transition constraints as constraints on temporal (i.e. history holding) tables):

The first check ensures that if the budget lock is set, the budget can only be altered by the user who set the lock.
The second check ensures that only the user who set the lock can unset it. The third checks that if the budget is changed, the lock must become unset. The final checks that when the lock is changed, U must record how changed the lock.

If the budget lock is unset, the budget can be altered by anyone.

Making locking an explicit part of the database design allows us to tweak the semantics as we wish. E.g. in the above there is no concept of sessions, so if a user has two connections open to the database and has a lock on the budget, they can update the budget from either connection. This cannot be done in the implemented internal locking schemes that I know of.

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Wed Oct 23 2002 - 16:06:06 CEST