[Nottingham] SQLite and 'locking'

[Duncan John Fyfe]

On Mon, 2007-02-26 at 12:46 +0000, David Aldred wrote:
> On Monday 26 February 2007 10:49, Duncan John Fyfe wrote:
> > On Sat, 2007-02-24 at 11:57 +0000, David Aldred wrote:
> > > On Thursday 22 February 2007 12:34, Duncan John Fyfe wrote:
> > > > I think it does what you want but with potentially undesirable
> > > > consequences. If you go down this route you probably want an explicit
> > > > commit at the end of your transaction as well.
> > >
> > > ...
> > >
> > > > Also be warned that if you code this up so everything happens within
> > > > the transaction then you give users an avenue for (inadvertent) denial
> > > > of service).  Think:
> > > >
> > > >         User looks at status (BEGIN TRANSACTION)
> > > >         User wanders off for coffee.
> > > >         User comes back and books / cancels (END TRANSACTION)
> > > >
> > > > Your database is locked during the coffee break.
> > >
> > > Actually, what is happening during the transaction is checking by the PHP
> > > script of the data returned by select statements, so the user taking 
> > > abreak shouldn't matter!
> >
> > The point is the break is out of your control (eg. they go on holiday
> > for two weeks).  If by viewing the seats available the *database* is
> > locked (start a transaction) then nobody else can look for *any*
> > available seats.  In fact that admin would find it difficult to update
> > (eg. add new performances) to the database while it is locked.
> 
> I don't see how that can happen, though.  The user going on holiday doesn't 
> stop the PHP script running: as soon as the 'submit' button was clicked the 
> script was started, and it doesn't return to user control until after the 
> whole transaction (so after the set of seats selected has been checked for 
> availability, and booked if and only if all are available).  The transaction 
> simply protects against a seat's booked status changing (via another instance 
> of the same script) while the check that all are available is being made.

> Am I missing something?
> 

One of your original statements was:
"I don't want more than one person able to get a booking, so if person A is 
between reading the status and changing it, person B's (and persons C..Z, of 
course!) shouldn't be able even to read the status."

To me "person A reading the status" is when person A first selects 'show
me available seats'.  I therefore interpreted your requirement as
wanting to give each person a 'grace period' between asking 'are seats
7,8, and 9 free' and submitting 'I would like to book seats 7, 8 and 9'
to limit:
	person A views available seats (7, 8 and 9 available)
	person B views available seats (7, 8 and 9 available)
	person B selects seat 8.
	person A is stuffed.
conflicts.
	
There may be additional selects as part of your checking/database update
but that is not under the users control and therefore not "person A
reading the status".

Have fun,
Duncan