Codeigniter Transactions

Question

I'm using Codeigniter transactions

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->trans_complete();

This works fine , the problem I have is that inside the trans_start and trans_complete I'm calling other functions and those functions deals with database so they contains inserts and update and some deletes ... ex:

$this->db->trans_start();
 $this->utils->insert_function($data);
 $this->utils->update_function2($test);
$this->db->trans_complete();

Now if those functions are executed and some errors occur CodeIgniter won't do a rollback.

What is the best way to deal with such issue?

The only solution, I have in mind, is to return an error from those functions and inside those function add (trans_stat and trans_complete) And if it returns an error test an do $this->db->trans_rollback

ex:

    $this->db->trans_start();
     $result = $this->utils->insert_function($data);
     if($result === false){
       $this->db->trans_rollback();
     }
    $this->db->trans_complete();

Is there a better way of doing this?

Update 1:

As requested a sample of the external function i'm calling :

   // insert_function contains

    $rec = array(
        'numero' => $numero,
        'transaction_id' => $id,
        'debit' => $product_taxes['amount_without_taxes'],
        'date' => $data['date_transaction'],
    );
    $this->addExerciceAccountingRecords($rec);

  and addExerciceAccountingRecords contains

   function addExerciceAccountingRecords($records) {
    $this->db->insert('transactions_exercices', $records);
    }

Answer 1

What I tried was more of a trick, but it worked for me.

$this->db->trans_begin();
  $rst1=  $this->utils->insert_function($data);
  $rst2 =  $this->utils->update_function2($test);
if($this->db->trans_status() === FALSE || !isset($rst1) || !isset($rst2)){
   $this->db->trans_rollback();
}else{
   $this->db->trans_commit();
}

Answer 2

Using transactions means support databases to insert data safely. So in Codeigniter we write every database related functions in the Model not in Controller.. And in your second code(which is not working)you have pointed model on there.(utils). So simple I'm sure this will not work. Because its not a insert data with model and Controller parallel. Transaction should be coded in the Model(I will write in Model in my answer).

---

Load this stuffs as well

1. Database Library
2. Model Class
3. URL helper
4. Session

---

Assumptions

In your code you have used $data and $test as array. So i assume there is two array for inserting and updating data.

---

Your data sets

$data = array(
   'title' => 'My title' ,
   'name' => 'My Name' ,
   'date' => 'My date'
);

$id = 007;
$test = array(
   'title' => $title,
   'name' => $name,
   'date' => $date
);

Your Code

$this->db->trans_start(); # Starting Transaction
$this->db->trans_strict(FALSE); # See Note 01. If you wish can remove as well 

$this->db->insert('table_name', $data); # Inserting data

# Updating data
$this->db->where('id', $id);
$this->db->update('table_name', $test); 

$this->db->trans_complete(); # Completing transaction

/*Optional*/

if ($this->db->trans_status() === FALSE) {
    # Something went wrong.
    $this->db->trans_rollback();
    return FALSE;
} 
else {
    # Everything is Perfect. 
    # Committing data to the database.
    $this->db->trans_commit();
    return TRUE;
}

---

Notes

1. By default Codeigniter runs all transactions in Strict Mode. When strict mode is enabled, if you are running multiple groups of transactions, if one group fails all groups will be rolled back. If strict mode is disabled, each group is treated independently, meaning a failure of one group will not affect any others.

Answer 3

I suspect the problem has to do with how CodeIgniter is handling objects.

If you go to the CI documentation under the section "Creating Libraries" at:
http://ellislab.com/codeigniter/user-guide/general/creating_libraries.html
and look at the section related to:

$CI =& get_instance();
$CI->load->helper('url');
$CI->load->library('session');
$CI->config->item('base_url');

In your main controller, you have loaded/instantiated the database class either using auto load or explicitly loading the class.

You then go ahead and open the transaction, and then, you access your database functions through your utils library.

However, once you use $this-db in your library, you are actually accessing another copy of the database instance, NOT the one that is associated with your transaction.

To access the same instance, you need to use the get_instance() function.

I think that should fix your problem. Your original coding style to separate function into various modules is excellent. You simply need to understand this additional detail.

Please try and confirm that the roll back works as you expect.

The guts of the code consists of the following controller:

$this->db->trans_start();
$this->User_profile_m->create_new_user_profile();
$this->User_profile_m->create_new_user();
$this->db->trans_complete(); 

and a simple model user_profile_m to deal with data persistence:

function create_new_user()
{
    $data['user_name_usr'] = $this->input->post('user_name');
    $data['create_date_usr'] = NULL;

    $this->db->insert('user_usr', $data);  
}

function create_new_user_profile()
{
    $data['user_name_pro'] = $this->input->post('user_name');
    $data['user_description_pro'] = $this->input->post('user_description');
    $data['create_date_pro'] = NULL;

    $this->db->insert('user_profile_pro', $data);  
}

Essentially, the demonstration tries to do two inserts (one in each of two tables). If one insert fails, the other is rolled back.

I built this in CodeIgniter 2.1.3 and I can make the application files available through GitHub or zip them up and send them to you.

Answer 4

Try this procedure. It really work for me :)

$this->db->trans_start();
   $this->utils->insert_function($data);
   $this->utils->update_function2($test);
if($this->db->trans_status() === FALSE){
   $this->db->trans_rollback();
}else{
   $this->db->trans_complete();
}

Answer 5

Note: Make sure to use $this->db->trans_begin() when running manual transactions, NOT $this->db->trans_start().

$this -> db -> trans_begin(); 
$this -> utils -> insert_function ( $data );
$this -> utils -> update_function2 ( $test ); 
$this -> db -> trans_complete ();

Certify in case use MySql, use in InnoDb Format