[Nottingham] SQLite and 'locking'

[Duncan John Fyfe]

On Mon, 2007-02-19 at 22:28 +0000, David Aldred wrote:
> There was a discussion here a bit ago about databases and (specifically) why 
> MySQL wasn't always the best around - I've got a related question and want to 
> check my understanding!
> 
> I want a routine which checks whether a 'status' field in a record 
> is 'booked', and if it isn't changes it to 'booked' and updates 
> the 'bookedby' field to show who's booked it.  Actually it's slightly more 
> complex than that, but that's the basic issue.
> 
> 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.
> 
> If I do something like:
> 
> BEGIN EXCLUSIVE
> <read the status and check it's not already booked>
> <book what needs booking>
> END
> 
> Does that achieve what I need?   I think it does....
> 

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.  

A BEGIN EXCLUSIVE locks the *database* against read/write.
If you have are likely to have multiple concurrent access to book
different resources then the above approach can cause problems.
Users will contend for read and write access and all but the first will
be denied even if they are accessing a different record in your table.
It will prevent more than one user from seeing *any* data held in the
database (database locked against read).

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.
 
You can break this down into:
	User views resource == SELECT

	User books resource ==
	BEGIN TRANSACTION
		if (row exists and has not changed since select)
		update row
		else
		wibble
	END TRANSACTION

You need to handle gazumping/abort/retry in your code.

This approach does not prevent users from seeing rows under
consideration by other users but to do that without encountering the
above problems (which may be an acceptable trade-off for your resources
and number of users etc) you will either want a database with better
locking or need to implement suitable locking yourself (because you need
to track user sessions).

An good way of handling gazumping is to introduce a cyclic counter field
into the table.  Then write your updates to cycle this field.  The
transaction above then tests against the cyclic counter:

	if (current cyclic counter == previous cyclic counter)
		the row has not changed so we can update it with cyc=cyc+1
	else
		The row has changed; the users data must therefore be regarded as invalid so abort the update.

Using a field dedicated to this purpose rather than using the status or
bookedby fields is good because it also guards against accidental data
corruption.
Eg.
	user select where rowid=X
	admin update (maybe a name has changed or a date needed changing) where rowid=X
	user update where rowid=X (oops, the older user data just overwrote the newer admin data)

You might be able to use a trigger to achieve this but I don't know SQLite triggers well enough.


HTH

Have fun,
Duncan