[Nottingham] SQLite and 'locking'

David Aldred david at familyaldred.org.uk
Mon Feb 26 12:48:24 GMT 2007

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?

David Aldred

More information about the Nottingham mailing list