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.

score 8 · Answer 1 · edited Oct 27 '17 at 13:04

8

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

edited Oct 27 '17 at 13:04

Alexander Morland

6,356
7
32
51

answered Oct 27 '17 at 05:31

swaroop suthar

632
3
19

Luiz · Answer 2 · 2017-09-01T11:57:36.443

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

edited Sep 01 '17 at 11:57

answered Aug 31 '17 at 20:51

Luiz

129
9

i want rollback transaction than what will do? – Pradip Talaviya Sep 01 '17 at 05:59
You are wrong about the use of `trans_off()`. Once that is run none of the other `trans_` method will execute. Well... technically they execute but once they see that `trans_off()` has been called they immediately return FALSE. – DFriend Oct 27 '17 at 17:19

ermacmkx · Answer 3 · 2017-09-02T21:47:00.433

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.

score 2 · Answer 4 · answered Oct 26 '17 at 17:57

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

score 2 · Answer 5 · answered Oct 27 '17 at 05:42

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

score 2 · Answer 6 · answered Oct 27 '17 at 17:38

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

transaction can't rollback in codeigniter

6 Answers6