Bad problem with transactions freezing entire app

[eluser]kirilisa[/eluser]
This is actually similar to this thread but since that hadn't been touched in a while I decided to start a new thread.

Transactions are fundamental to my app. In an attempt to make sure transactions were working properly on txn failure, I came across what I feel is a bug. Here is code for purpose of demonstration:

Code:

class Test extends Controller {
  function __construct()
  {
    parent::Controller();        
  }

  function one() {
    $this->db->trans_begin();
    $this->db->query("update SystemUser set lastName = 'blah1' where userName = 'cmiuser'");
    $this->db->query("BAD QUERY");

    if ($this->db->trans_status() === FALSE) {
      echo "rollback one";
      $this->db->trans_rollback();
    } else {
      echo "committed one";
      $this->db->trans_commit();
    }    
  }

  function two() {
    $this->db->trans_begin();
    $this->db->query("update SystemUser set lastName = 'blah2' where userName = 'cmiuser'");

    if ($this->db->trans_status() === FALSE) {
      echo "rollback two";
      $this->db->trans_rollback();
    } else {
      echo "committed two";
      $this->db->trans_commit();
    }    
  }
}

1) typing 'show processlist' at mysql cmd line shows no active threads but the processlist

2) in web browser, browse to localhost/ci/test/one. PHP dumps an error:
An Error Was Encountered
Error Number: 1064
You have an error in your SQL syntax;
BAD QUERY

3) reload the page localhost/ci/one or go to localhost/ci/two: it hangs forever, eventually quitting with an error:
An Error Was Encountered
Error Number: 1205
Lock wait timeout exceeded; try restarting transaction
update SystemUser set lastName = 'blah1' where userName = 'cmiuser'

4) type 'show processlist' at mysql cmd prompt. it says:

Code:

mysql> show processlist;
+----+----------+----------------+-------+---------+------+----------+---------------------------------------------------------------------+
| Id | User     | Host           | db    | Command | Time | State    | Info                                                                |
+----+----------+----------------+-------+---------+------+----------+---------------------------------------------------------------------+
| 14 | root     | localhost      | fcprd | Query   |    0 | NULL     | show processlist                                                    |
| 23 | kirilisa | mojojojo:42272 | fcprd | Sleep   |   96 |          | NULL                                                                |
| 24 | kirilisa | mojojojo:42274 | fcprd | Query   |   30 | Updating | update SystemUser set lastName = 'blah1' where userName = 'cmiuser' |
+----+----------+----------------+-------+---------+------+----------+---------------------------------------------------------------------+

i.e. because PHP crashed with a mysql error about my bad query, it never got around to the $this->db->trans_rollback(); bit of the controller, and so that table is now locked up for good.

The only way to unlock it is to kill all the listed processes or restart the DB.

Granted one would hope that SQL statements wouldn't be crashing with PHP errors, but it seems to me that even if they do, CI's database abilities should be able to handle this without resulting in this locked table problem.

I don't know what to do about this. I thought putting a $this->trans_rollback(); in the beginning of the trans_begin() function might fix it, but it did not. Help!

[I honestly don't understand why it is even dumping the mysql error to screen as I have log_errors set to Off in my php.ini and I see all the mysql functions in mysql_driver are using @, but that's an unrelated issue. I'd really just like to understand how to make CI rollback transactions on PHP/mysql error that halts the code execution.]

I am using PHP 5.2.3 on ubuntu gutsy, MySQL 5.0.45, CI 6.1 with the bundled DB library.

Any help would be most appreciated. I'm a bit stressed about this transaction thing. Thanks!

[eluser]kirilisa[/eluser]
Maybe in DB_driver.php query() method as soon as it hits a bad query (i.e. where it sets $this->_trans_status = FALSE) it should do a rollback?

Code:

/** part of DB-driver.php -> query() at line 297 **/
// Run the Query
if (FALSE === ($this->result_id = $this->simple_query($sql)))
{
  // This will trigger a rollback if transactions are being used
  $this->_trans_status = FALSE;
  $this->trans_rollback(); //@@@ added by kirilisa
  
  if ($this->db_debug)
  {
    log_message('error', 'Query error: '.$this->_error_message());
    return $this->display_error(array('Error Number: '.$this->_error_number(),
                      $this->_error_message(),
                      $sql));
  }
  
  return FALSE;
}

I just stuck that in and it seemed to fix it, but the whole DB thing is so complicated, I don't know what repercussions it may have!

Arg. I don't know. Please advise.....

[eluser]Syed Rakib Al Hasan[/eluser]
Hey.... i am also struggling with exactly this issue. Have you been able to resolve anything? Could you get any response from stackoverflow.com