Re: Newbie Locking Question
David T. Ashley wrote: Nigel wrote: 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. What is a pdo? Thanks, Dave. PHP's newest official way to talk to databases: http://www.*php*.net/*pdo *http://wiki.cc/*php*/PDO http://www.phpro.org/tutorials/Introduction-to-PHP-PDO-(PHP-Data-Objects).html Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Locking Question
Nigel wrote: 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. What is a pdo? Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Locking Question
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. Because my application is so non-demanding in terms of server resources, what I decided to do is use only one simple locking schema throughout the code that locks every table at the same time, i.e. -- 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? Anything I should watch out for? Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Locking Question
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]
RE: Newbie Locking Question
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 ... Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Locking Question
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]
