David T. Ashley wrote:
Nigel wrote:
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.
Hi Nigel,
Just one question: the documentation of get_lock() says that the lock is
freed when a process terminates.
Does this also occur with mod_php and persistent connections if a PHP script
dies (say, due to fatal error or CPU limit timeout)?
It isn't clear how mod_php works and why with persistent connections the
"LOCK TABLES" locks wouldn't be freed but the get_lock() lock would.
You were suggesting that I replace the LOCK/UNLOCK TABLES critical section
with one implemented using get_lock()???
Or maybe you are suggesting something else ...
Hi Dave,
mod_php will persist the MySQL connection holding open any lock or
syncronisation token obtained through any of the three methods :
begin/commit, lock/unlock tables or get_lock/release_lock. PHP does
ensure that even in the event of timeouts or fatal errors any shutdown
handlers registered are still executed so it is possible to clean up
properly whichever method is used.
http://uk.php.net/manual/en/function.register-shutdown-function.php If
you use php's pdo with transactions it perform a rollback for you on
abort or completion.
Database locking always comes down the safety vs concurrency. In a
multi-user enviroment you want to lock as few rows as possible during
your critical section. Innodb transactions are the only way to ensure
correctness in a multi-statement update, but if you can't use
transactions your goal is to find the smallest impact scheme which is
still safe.
I've used get_lock() string locks in the past to simulate row level
locks without transactions but it's only safe in certain update schemes,
if all your developers use the scheme consistently and never safe if
your application isn't the only thing which modifies the rows of the
tables/fields which need protection. Whether its a smart thing to do
depends on your situation.
HTH
Nigel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
