note 73816 added to function.mysqli-autocommit

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... <?php public function begin_transaction() { $ret = $this->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. ---- Server IP: 69.147.83.197 Probable Submitter: 67.124.149.32 ---- Manual Page -- http://www.php.net/manual/en/function.mysqli-autocommit.php Edit -- https://master.php.net/note/edit/73816 Del: integrated -- https://master.php.net/note/delete/73816/integrated Del: useless -- https://master.php.net/note/delete/73816/useless Del: bad code -- https://master.php.net/note/delete/73816/bad+code Del: spam -- https://master.php.net/note/delete/73816/spam Del: non-english -- https://master.php.net/note/delete/73816/non-english Del: in docs -- https://master.php.net/note/delete/73816/in+docs Del: other reasons-- https://master.php.net/note/delete/73816 Reject -- https://master.php.net/note/reject/73816 Search -- https://master.php.net/manage/user-notes.php