CODEWITHSUNDEEP
phpmysqlcodeignitertransactionscodeigniter-3
Pradip Talaviya
Pradip Talaviya

Reputation: 409

transaction can't rollback in codeigniter

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.

Upvotes: 4

Views: 4421

Answers (6)

DFriend
DFriend

Reputation: 8964

The code you have written is pretty much the same as way automatic transactions are written - only not as well. My advice? Don't try to outsmart CodeIgniter, use the "automatic" mode for transactions. Then rollbacks, if required, will be taken care of for you.

I've redone your code using automatic transactions mode. I also collect the data from $orderItemList so it can be inserted using a batch instead of calling db->insert repetitively in a loop.

$this->db->trans_start();
$this->db->insert('tblorder', $data);
$orderid = $this->db->insert_id();
foreach($orderItemList as $orderItem)
{
    $orderedItems[] = [
      'orderid' => $orderid,
      'productid' => $orderItem->productid,
      'amount' => $orderItem->amount
    ];
}
if(isset($orderedItems))
{
    $this->db->insert_batch('tblorderitem', $orderedItems);
}
$this->db->trans_complete();
$this->db->trans_status();

Upvotes: 2

swap
swap

Reputation: 282

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();
}

Upvotes: 8

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

Do like this. If you want to Learn more about the Transaction Check My answer on another question. That helps you to get a clear idea about Transactions.

$this->db->trans_begin();

$this->db->insert('tblorder',$data);
$orderid=$this->db->insert_id();

$orderitem = array();

foreach ($orderItemList as $orderItemList) 
{
    $orderitem[] = array('orderid' =>$orderid ,'productid' =>$orderItemList->productid ,'amount' =>$orderItemList->amount);
}

$this->db->insert_batch('tblorderitem', $orderitem); 

$this->db->trans_complete();

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

Upvotes: 2

Rick James
Rick James

Reputation: 142298

Why do you want to ROLLBACK? Is it because of something other than a false trans_status? If so, call trans_rollback before calling trans_complete or trans_commit.

It sounds like this would be optimal for your situation (apologies for incorrect syntax):

trans_start
...
if ( some non-db problem )  trans_rollback
trans_complete

Upvotes: 2

ermacmkx
ermacmkx

Reputation: 449

As I know for CI version 3.0.1 the only thing that you should worry about is where to put $this->db->trans_start(); and $this->db->trans_complete();

So for situations like:

$this->db->trans_start();
//any code goes here
$this->db->trans_complete();

Transaction will be rolled back if something goes wrong, or will be committed on call to $this->db->trans_complete();

Because if you look under the hood of trans_start method it contains trans_begin. And trans_complete method contains check for trans_status and accordingly calls to trans_commit or trans_rollback methods.

Same applies for nested transactions:

$this->db->trans_start();
//any code goes here
$this->db->trans_start();
//any code goes here
$this->db->trans_complete();
//any code goes here
$this->db->trans_complete();

By default Codeigniter runs all transactions in Strict Mode so on last trans_complete all will be committed, or if anything fails all rollbacks.

Upvotes: 2

Luiz
Luiz

Reputation: 129

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();
}

Upvotes: 2

Related Questions