[Nottingham] SQLite and 'locking'

[Martin Garton]

On Mon, 2007-02-19 at 22:28 +0000, David Aldred wrote:

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

Without knowing more, it's hard to advise, but for some situations the
simplest way to do this kind of thing is with something like:

update foo set status='booked', bookedby='me' where status='notbooked'
and ordernumner='0123'

which will obviously prevent two users colliding, because the second one
will not update any rows, but whether that is sufficient depends on what
else you are doing.

Your "BEGIN EXCLUSIVE" looks like it will certainly achieve what you
need functionally, but from the documentation (I'm not at all familiar
with sqlite) may not scale too well. (if that matters in your case?)

Others may know better. Most of my experience is with other DBMS.

-- 
Martin.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 191 bytes
Desc: This is a digitally signed message part
Url : http://mailman.lug.org.uk/pipermail/nottingham/attachments/20070220/36014337/attachment-0001.bin