I’ve found that if PHP exits due to a code bug during a transaction, an InnoDB table can remain locked until Apache is restarted. The simple test is to start a transaction by setting $mysqli_obj->autocommit(false) and executing an insert statement. Before getting to a $mysqli_obj->commit statement – have a runtime code bug bomb PHP. You check the database, no insert happened (you assume a rollback occurred) .. and you go fix the bug, and try again… but this time the script takes about 50 seconds to timeout – the insert statement returning with a “1205 – Lock wait timeout exceeded; try restarting transaction”. No rollback occurred. And this error will not go away until you restart Apache – for whatever reason, the resources are not released until the process is killed. I found that an ‘exit’, instead of a PHP code bug, will not cause a problem. So there is an auto-rollback mechanism in place – it just fails miserably when PHP dies unexpectantly. Having to restarting apache is a pretty drastic measure to overcome a code bug. To avoid this problem, I use “register_shutdown_function()” when I start a transaction, and set a flag to indicate a transaction is in process (because there is no unregister_shutdown_function()). See below. So the __shutdown_check() routine (I beleive it needs to be public) is called when the script bombs – which is able to invoke the rollback(). these are just the relevant bits to give u an idea… mysqli_obj->autocommit(false); $this->transaction_in_progress = true; register_shutdown_function(array($this, „__shutdown_check“)); } public function __shutdown_check() { if ($this->transaction_in_progress) { $this->rollback(); } } public function commit() { $ret = $this->mysqli_obj->commit(); $this->transaction_in_progress = false; } public function rollback() { $ret = $this->mysqli_obj->rollback(); $this->transaction_in_progress = false; } ?> True for PHP 5.1.6 + MySQL 5.0.24a.
Наши спонсори са:
Български трактори на добри цени при изключително качество
