Code Igniter database transactions

[eluser]kirilisa[/eluser]
I am using CI 1.6.1, PHP 5.2.3, mysql 5.0.45 on Ubuntu Gusty (sure thats more info than you wanted)

First thing, the table you defined would have to have ENGINE=INNODB added onto its definition to make transactions apply to it.

That being said, when I run your code, above (but making the test table be an INNODB table), like you said you can reload it all day long and it doesn't freeze, just displays the boxed error about duplicate entries.

BUT something very disturbing, is that if you load the page the first time, it crashes with an error, check the contents of the test table, there is nothing there. now reload the page, it doesn't freeze, and still crashes with the error, but now look in the test table and the entry has been made! This shouldn't be, since the entry is wrapped in the txn with the bad query, so it should always be rolled back.

It is worth noting that when I turn db_debug to FALSE, your code displays the question mark as it should, and the test table is never updated (which is good!)

So it would seem that your code is not freezing because the transaction IS actually being committed (and thus showing up in the test table) and thus every time you reload, the transaction started is a fresh one. But I don't know why that would be. Is trans_start() automatically committing unfinished transactions or something? That would be awful.

...or am I missing something obvious??

When I run my code, below (which is using trans_begin/trans_rollback/trans_commit rather than trans_start/trans_complete) the first time is crashes quickly with a boxed error about BAD QUERY, and the second time it hangs forever and I have to restart DB or kill processes. It never does update the table, though (which is good, because it shouldn't!!)

When I turn db_debug to FALSE, however, it works fine, saying 'rollback' as it should.

Code:

// controller
$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";
  $this->db->trans_rollback();
} else {
  echo "committed";
  $this->db->trans_commit();
}

IF, however, I comment out the line which contains the valid SQL (the update SystemUser...) line, I can also reload my version ad nauseum with db_debug turned to TRUE, because they database won't have been touched with a good query and thus won't be locked.