On 09/26/2013 06:35 PM, Stephan Beal wrote:
Hi, all,
some months ago i was tasked with writing a WWW/PHP/MySQL-based application
which takes a group of meeting tables and randomly assigns them to
participants (yes, this was/is a real application). One of the problems i
faced is an inherent race condition in the selection of meeting slots.
MySQL offers table locking, but it's riddled with bugs and doesn't work
properly when subselects or aliases come into play (it fails loudly with
cryptic/downright wrong messages). So i gave up on the idea of protecting
against the race and just hoped for the best (so far, so good - it's a
low-use app and the result of a race collision is not worth the development
efforts needed to avoid it).
About 2 minutes ago i had the idea that i could open up an sqlite3
connection which is _only_ used as a mutex to block of access to the MySQL
tables while those bits are running. (i tried doing something similar with
MySQL back then, but MySQL's explicit table locking support is surprisingly
limited and i couldn't get it working.)
My question now is (assuming this is feasible): what SQL do i need to do
this? Would it suffice to call BEGIN EXCLUSIVE on an empty db, and only
continue on to the MySQL bits after the sqlite3 BEGIN EXCLUSIVE returns?
:-?
Sounds right. BEGIN EXCLUSIVE obtains an exclusive lock on
the db. If this succeeds, no other connection to the same database
will be able to successfully execute BEGIN EXCLUSIVE until
your first connection has concluded its transaction or closed
the database handle.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
