[Nottingham] SQLite and 'locking'

[Alex Tibbles]

(apologies for top posting).

you might be able to achieve the same with:

UPDATE table SET status = 'Booked' WHERE id = 123 AND status <> 'Booked';
COMMIT;

if 0 rows are updated or you get an error, it means someone else commited the change first

----- Original Message ----
From: David Aldred <david at familyaldred.org.uk>
To: nottingham at mailman.lug.org.uk
Sent: Monday, 19 February, 2007 10:28:51 PM
Subject: [Nottingham] SQLite and 'locking'


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

-- 
David Aldred

_______________________________________________
Nottingham mailing list
Nottingham at mailman.lug.org.uk
https://mailman.lug.org.uk/mailman/listinfo/nottingham





		
___________________________________________________________ 
What kind of emailer are you? Find out today - get a free analysis of your email personality. Take the quiz at the Yahoo! Mail Championship. 
http://uk.rd.yahoo.com/evt=44106/*http://mail.yahoo.net/uk