DB transaction won't roll back

[eluser]MadZad[/eluser]
This is the first time I've tried using CI's transactions, so I'm hoping someone will point out a common pitfall that I blindly walked into.

Here's my model's contructor:

Code:

private $mass_db;
  function Person_model() {
    parent::Model();
    $this->mass_db = $this->load->database("mass", TRUE);
  }

And here's the function in that model:

Code:

function save_person($person_id, $save_vals) {
      $this->mass_db->trans_start();

      $this->mass_db->delete("ada_person", array("person_id" => $person_id));
      foreach ($save_vals as $key => $value) {
          if (substr($key, 0, 3) == "ada") {
              if ($value == 1) {
                  $ap_data = array("ada_id" => substr($key, 3), "person_id" => $person_id);
                  $this->mass_db->insert("ada_person", $ap_data);
              }
              unset($save_vals[$key]);              
          }
      }

      $this->mass_db->update('persons', $save_vals, "id = " . $person_id);
      
      $this->mass_db->trans_complete();
      return !$this->mass_db->trans_status();
  }

So I've got a delete and zero or more inserts on one table, and an update on another table. Works fine under normal conditions.

If I specify trans_start(TRUE) or force a bad value in for $person_id, I was expecting the transaction to roll back, and have no changes to the DB. However, the DB updates are made normally, with the delete and inserts happening, and the update failing if I hack in $person_id = 9999.

Caveats - I am using MySQL, but changed both tables to be of type InnoDB. The rest of the code works well enough, so while not the smartest, it's good enough. The application uses multiple DBs, so that's why I'm using $this->mass_db. This is my first CI project.

Any wisdom and/or guesses how to successfully transactionalize?

[eluser]Henrik Pejer[/eluser]
I've been looking into the code for the transactions in CodeIgniter and it would seem as though CodeIgniter does not have its own system for handling transactions, but rather uses whatever transactions your database implements.

If you are using MySQL trans_begin is the same as running the following queries:

Code:

SET AUTOCOMMIT=0;
START TRANSACTION;

So it would seem that if your database does not support transactions, depending on the type of database (mysql, postgre etc) and version, transactions might not work.

I'd check what version of database you are running, and then try to see if you can use transactions when you are not using the transaction code included in CodeIgniter.

If it works 'outside' CodeIgniter then something is wrong, otherwise it seems to be a problem with your database and/or version.

I hope you are able to solve your problems, good luck!

[eluser]MadZad[/eluser]
Thanks, Henrik.
As is so commonplace, I was pulled onto a different project, but when I return to CI stuff, I have every reason to believe that we did indeed not get MySQL tweaked correctly for using transactions. First order of business is run some transactions outside of CI until it works.

Gene