transaction can't rollback in codeigniter

Question

I am working on transaction , and in mentioned code I faced some problem. I didn't commit the transaction , but it inserted data into my database.

$this->db->trans_begin();
$this->db->insert('tblorder',$data);
$orderid=$this->db->insert_id();
foreach ($orderItemList as $orderItemList) {
    $orderitem = array('orderid' =>$orderid ,'productid' =>$orderItemList->productid ,'amount' =>$orderItemList->amount);
    $this->db->insert('tblorderitem',$orderitem);
}
$this->db->trans_complete();

if ($this->db->trans_status() == 1) {
    $this->db->trans_rollback();
    return "true";
} else {
    $this->db->trans_commit();
    return "false";
}

I rolled back transaction , and again all data was inserted in my database. What must be the problem? I can't get it.

Answer 1

Do not use $this->db->trans_complete();

as per documentation

$this->db->trans_begin();

$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');

if ($this->db->trans_status() === FALSE)
{
        $this->db->trans_rollback();
}
else
{
        $this->db->trans_commit();
}

Answer 2

ANSWER UPDATED!

I have to tell you the default of codeigniter´s config is automatically commit all transactions.

If for any reason you want to disable this function you have to use this line:

$this->db->trans_off();    

before of

$this->db->begin();

Said that, so when you use

$this->db->trans_complete();

you will need to commit or rollback, like this:

$result = $this->db->trans_complete();

if($result === true){
    $this->db->trans_commit();
}else{
    $this->db->trans_rollback();
}

Finally what you want is:

$this->db->trans_off();

$this->db->query("insert this...");
$this->db->query("insert that...");

$result = $this->db->trans_complete();

if($result === true){
    $this->db->trans_commit();
}else{
    $this->db->trans_rollback();
}