[Gllug] A programming question. long.

[home at alexhudson.com]

On Thu, Jul 05, 2001 at 10:37:56PM +0100, Bruce Richardson wrote:
> > so we need some sort of locking. Enter the spin lock.
> 
> No, you don't.

Yes he does, as you later state:

> To *guarantee* that no two people see the same record, you need to run
> this inside a transaction and perform an exclusive table lock

I don't necessarily agree with 'exclusive table lock' (bit of a waste for
one row!), but transactions are the answer. I don't think setting a trigger
on a select would do anything useful - in fact, it would be dangerous,
because if the trigger is removing the information from the table it doesn't
yet know whether the client dealt with the information it received
successfully, which is silent data loss. Ick. It's up to the application,
not the database, to remove items from the store.

Another problem presents itself from the design of this system. Firstly, the
database is being used as a queue. Time to look into clustered indexes, I
think, and combine that with a row-level locked transaction, because
otherwise the performance is really going to bite. I presume the design is
intended to be FIFO, which is going to hurt the transaction cache if any get
left open too. If we're talking a large number of rows (3 million was it?)
it's going to need some proper design..

Cheers,

Alex.

-- 

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