David T. Ashley wrote:
Hi,
I'm doing a PHP application, and there are just a few instances where I need
to do atomic operations on more than one table at a time and I can't express
what I want to do as a single SQL statement.
What I'm trying to guard against, naturally, is race conditions when more
than one process is making modifications at a time, i.e. multiple
simultaneous page hits.
LOCK TABLE widgets WRITE, thingamabobs WRITE ...
Make multiple interrelated table changes.
UNLOCK TABLES
----------
In my estimation, what this should do is cause every other process to sleep
briefly until the first one is through to the UNLOCK TABLES.
I can't see going to a more complex locking model with such a simple
application.
Will the "lock every table" approach work as I intend?
Yes, it will work as you suggest: It lock your database solid, and aim
the programming rifle squarely at both feet in readiness for the fateful
day which your client doesn't complete as quickly as you've estimated.
Anything I should
watch out for?
Your entire database freezing solid when the client fails to terminate.
If your using persistent connections with a mod_php webserver and the
script aborts without relasing the lock mod_php will kindly hold the
mysql connection with the lock open for you.....
If you can't or won't do this properly by using a transactional table
and begin/commit at least look at using get_lock() based guard
conditions which only lock a string leaving the database accessable.
Whatever you do if you client is php install a shutdown handler to clean
up any locks.
HTH
Nigel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
