[Gllug] A programming question. long.

[home at alexhudson.com]

On Fri, Jul 06, 2001 at 12:19:01PM +0100, Bruce Richardson wrote:
> > I don't necessarily agree with 'exclusive table lock' (bit of a waste for
> > one row!)
> 
> Which is why I said the lock should be placed on the view, not the
> underlying table. 

But then you're locking the view, not the data - and the data is still
exposed (which is fine, so long as everything is using the view..). And to
be honest, you don't need a view & set of triggers, because the standard SQL
row locks are all you need.

> > successfully, which is silent data loss. Ick. It's up to the application,
> > not the database, to remove items from the store.
> 
> No, it's the database's job IMO.

Ah :) Age old database disagreement. IMO, business logic has no place in the
database, for one simple reason - the database can't maintain consistency.
The database can tell you for sure that transactions proceeded okay, and
that the data fulfils a set of rules, but can't make the data obey those
rules by itself. 

> In my example the select trigger does something essential - marking
> the selected record "don't touch".

Which is what I don't understand - you seem to be using table-locking to
implement row-locking, which might be fair enough for MySQL (which doesn't
support row-level locking), but not great for those dbs which do.

> This way the database takes care of all data validation tasks. 

The database has no idea about data validation; business logic should be in
the software. For example, let's say that a banking application needs to
update a number of accounts at once (a transfer, perhaps). It's up to the
application to specify that the account debited and the account credited
must be done so within one transaction. It's not for the database to attempt
to work that out - the data within the database has meaning, and it's the
application that determines that meaning. The database can't hold the debit
transaction open expecting a credit transaction for the same amount - that
requires a priori knowledge of the type of transaction. 

The database may very well be happy that the data has been correctly stored
(as it may have been), but if it crashed at the wrong time (between two
account transactions, for example) even though the transaction log would
roll-back part of the data, the data itself might not be self-consistent.
The database guarantees only that it stores the data as you ask; the
application needs to make sense of the data itself.

Cheers,

Alex.

-- 

-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug