[Nottingham] SQLite and 'locking'

[Alex Tibbles]

----- Original Message ----
From: David Aldred <david at familyaldred.org.uk>
To: nottingham at mailman.lug.org.uk
Sent: Monday, 26 February, 2007 12:46:27 PM
Subject: Re: [Nottingham] SQLite and 'locking'

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.

---------------------------------
How about 200 concurrent users all making booking. Will SQLite simply queue them, or will it return "Database locked" errors to 199 of them?

alex





	
	
		
___________________________________________________________ 
New Yahoo! Mail is the ultimate force in competitive emailing. Find out more at the Yahoo! Mail Championships. Plus: play games and win prizes. 
http://uk.rd.yahoo.com/evt=44106/*http://mail.yahoo.net/uk