[Nottingham] SQLite and 'locking'

[David Aldred]

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!

Perhaps I should say a little more about the application: the idea is for a 
drama group to dip a toe into the water of on-line booking, on a basis which 
is not enormously sophisticated but workable.   Obviously it's important that 
two people can't book the same seat - that would be handled by creating an 
UPDATE statement with a WHERE clause ensuring that it only booked a seat 
which wasn't already booked - no problem.

The potential problem arises where:

- Seats (say) 7,8 and 9 are available in a row, but 6 and 10 are booked
- Person A wants three seats together and wants to book 7,8 and 9
- Person B is at the same time booking seat 7

So for Person A's booking, we need to know that the three seats are *all* 
available before booking any of them, and return for further user input if 
not.  I can't see any easy way round seperate SELECT an UPDATE statements, 
and if Person B's instance of the script gets to the UPDATE between Person 
A's SELECT and UPDATE, we could have a problem without some sort of locking!

I think the BEGIN TRANSACTION...END TRANSACTION is going to deal with this - 
thanks, all. 

-- 
David Aldred