Transactions within Transactions Newbie question, can someone help me please?

[eluser]gtech[/eluser]
I wonder if you can help me, I have been using code igniter for a couple of months now and am glad to find another good technology for deploying web applications.

I have been going through my application adding in database transactions to queries, and the rollback mechanism works well:

Code:

// just a quick example
  function dbtest($objid) {
    $this->db->trans_begin();
    $this->db->delete('table1',array('ObjID' =>$objid));
    $this->db->delete('table2',array('ObjID' =>$objid));
    $this->db->trans_complete();

    if ($this->db->trans_status())
    {
      $this->db->trans_rollback();
      return True;
    } else {
      $this->db->trans_commit();
      return False;
    }
  }

However if I try calling a transaction within a transaction as shown in the code below, rollback appears not to work:

Code:

// just a quick example
  function dbtest_call($objid) {
    $this->db->trans_begin();
    // call function containing a transaction
    $this->dbtest($objid);
    $this->db->delete('table3',array('ObjID' =>$objid));
    $this->db->trans_complete();

    if ($this->db->trans_status())
    {
      $this->db->trans_rollback();
      return True;
    } else {
      $this->db->trans_commit();
      return False;
    }
  }

I know there may be ways around this coding solution but I was wondering if codeigniter supported transactions within transactions or whether this is purely a database limitation (I am currently using MYSQL with InnoDB table types)?

any help would be greatly appreciated.

[eluser]gtech[/eluser]
I have answered my own question, No.. nested transactions do not work using mysql, I have not tested this with other databases such as oracle/postgres.

To get around this I used a default parameter useTrans, something like the following which worked fine:

Code:

// just a quick fix to the quick example
  function dbtest($objid,$useTrans=1) {
    if ($useTrans) {
      $this->db->trans_begin();
    }
    $this->db->delete('table1',array('ObjID' =>$objid));
    $this->db->delete('table2',array('ObjID' =>$objid));
    if ($useTrans) {
      $this->db->trans_complete();

      if ($this->db->trans_status())
      {
        $this->db->trans_rollback();
        return True;
      } else {
        $this->db->trans_commit();
        return False;
      }
    }
    return;
  }

  function dbtest_call($objid) {
    $this->db->trans_begin();
    // call function containing a transaction
    // second parameter lets function know its already within a transaction
    $this->dbtest($objid,0);

    $this->db->delete('table3',array('ObjID' =>$objid));
    $this->db->trans_complete();

    if ($this->db->trans_status())
    {
      $this->db->trans_rollback();
      return True;
    } else {
      $this->db->trans_commit();
      return False;
    }
  }