[Nottingham] SQLite and 'locking'

[Duncan John Fyfe]

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.

Just to be clear:
	database level locking (read/write) means nobody outside of the locking
transaction can do anything to the entire database.

	table level locking would allow you to lock just the tables involved in
the transaction.

	page level locking would allow you to lock just part of the tables
involved in the transaction.

	row level locking would allow you to lock just the rows involved in the
transaction.

>From my understanding of sqlite (which may be out of data) 
BEGIN EXCLUSIVE uses a database level lock.

> 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. 

This is exactly the sort of situation where I have used the cyclic
counter I mentioned but for bank account updates.

Joint account holders A and B arrive at different branches to make a
deposit:
	Person A:  View balance b.		Select balance from accounts where ...
	Person A:  Chat to counter clerk about number 24 buses.
	Person B:  View balance also b.    	Select balance from accounts where ...
	Person B:  Deposit amount db		Update accounts set balance = b + db where ...
	Person A:  Deposit amount da		Update accounts set balance = b + da where ...

	Oops, we just lost deposit db.

It allows you to guard against such problems without using database implemented locking simply because
when you get to tables of several million rows this brings with it all sorts of database dependant issues
(for which DBA's get paid lots to know about).

By trying to combine the SELECT and UPDATE into a single transaction you are opening yourself up to denial of service attacks.
I understand what you are trying to do but if you want to prevent conflicts over seat bookings and minimise the DOS risk you will
need to devise your own locking scheme which includes for example timeouts.

Have fun,
Duncan