[Gllug] A programming question. long.

[Alex Hudson]

>> be honest, you don't need a view & set of triggers, because the standard
SQL
>> row locks are all you need.
>No, row locks are *not* enough.

Brief point - I'm not a three year old, I don't need asterisks to draw my
attention to statements. Keep it up and I will send you html with <blink>
tags.. :P

BTW, you later outline a solution using row locks, so they obvously are
enough.. we're just disagreeing about implementation design here. I don't
see the point of all this faux-locking when a transaction is sufficient.

> Go look at David's description of his
>problem.  The way he is doing it he needs to know that *only* one client
>is *looking* at the row.  The only way to do that is to lock the *table*
>before running the select query.  It's the only way to ensure that
>another select query is not also doing the same thing you are.

I disagree. For only one client to 'know of' a particular row, you need to
lock the table between view & removal. If those are separate events,
performance is going to blow. The correct way is to do the select and delete
within one transaction, with a backup trigger on delete if required. If you
wish to add your extra column, then do so with an update, but again, you
don't need views, etc., a transaction is enough.

>Code is code is code.  If the server-side programming language can
>implement a function, why is that invalid?

It's not 'invalid', it's just a bad habit to put business logic in a
database. The only excuse you can have is to use import application into the
db server (stored procedures, triggers, etc.) for performance reasons, much
like denormalisation. But it should be avoided where possible.

>Perhaps you could actually say what is *wrong* with my appointments
example?

I didn't say anything was 'wrong' with it; I disagree with the design. For
the reasons I've given.

> Row-level locking can prevent two processes updating a row simultaneously
but
>can't stop two select queries reading the same row.

I wasn't suggesting that though. I wasn't suggesting a 'select for ..
update' with a later update, I was suggesting a transaction-based system.
You can't prevent clients viewing the same data set without complete table
serialization; however, you can distribute rows one-by-one with a simple
transaction, which I believe fulfils the requirement.

>  But my database will check anything he passes to it and only let him
see/do what he's
> supposed to.  Not one thing more.

That wasn't my point - my point was a database cannot validate meta-data,
hence business logic belongs in the application. It _is_ up to the database
to validate the data to the design; it _may_ be up to the database for basic
validation according to business logic if performance demands; however, the
final say is always for the application.

Cheers,

Alex.

[btw - heavily edited. Any quotes taken out of context are my fault, not
Bruce's]


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