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

What Storage Engine are you using for your MySQL tables? Also, how do you know that the roll back failed? — Marc Audet, Mar 05 '13 at 17:48
@MarcAudet INNODB , I know because When there is a rollback is not applied on tables modified during the function call inside the trans_start — Tarek, Mar 05 '13 at 18:00
Can you post the code to utils->insert_function and utils->insert_function2 ? I have a notion of what the issue might be... — Marc Audet, Mar 05 '13 at 18:03

Abdulla Nilam · Accepted Answer · 2022-01-20T13:27:01.410

48

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

Database Library
Model Class
URL helper
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

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.

edited Jan 20 '22 at 13:27

answered Jan 09 '16 at 15:51

Abdulla Nilam

36,589
17
64
85

4

trans_complete() already does a status check and rollback/commit – Alex Aug 01 '16 at 06:10
@AL-zami Why you remove those ?? **Don't Conflict the content** – Abdulla Nilam Aug 25 '16 at 09:00
@spartan those few lin i edited was for manual transaction.see the documentation for your self https://www.codeigniter.com/user_guide/database/transactions.html – AL-zami Aug 25 '16 at 12:43
@AL-zami bro its to manage errors. Check this https://www.codeigniter.com/user_guide/database/transactions.html#managing-errors – Abdulla Nilam Aug 25 '16 at 14:58
if trans_complete() commits automatically, in the "ELSE" case it will commit again...don't you think it is a problem ! – AL-zami Aug 25 '16 at 17:26
there are two options as follows - 1) remove else condition or 2) remove $this->db->trans_complete(), please check manual & update your answer.@Spartan – Jobayer Sep 19 '16 at 09:52
1

**Make sure to use $this->db->trans_begin() when running manual transactions, NOT $this->db->trans_start().** – Shihas Oct 13 '16 at 08:31
@Shihas Why? When examining the code, `trans_start()` actually calls `trans_begin()` with same parameters and there is no additional check in place either. Perhaps readability. – Muhammad Ali Mar 26 '17 at 09:00
in my case i want to make order at that time one order have **multiple order item in separate table** so when i make order i want to transaction. step-1: i insert order in order table. step-2: based on **order inserted id** use in **order item table** it multiple in one order. when order item inserted at that time i check order time availability if order item quantity not available i want roll back all transaction but `$this->db->trans_status()` getting true how can do it. **i Try a lot but not work when availability fail i just `$this->db->trans_rollback();` but it can not roll back** – Pradip Talaviya Feb 24 '18 at 10:32
Hello @AbdullaNilam, is it really possible to run $this->db->trans_complete(); and then run $this->db->trans_rollback(); ? – Valter Ekholm Sep 23 '20 at 14:06
@ValterEkholm yes. `trans_complete` only write your data in DB. if failed Rollback will remove partial saved on DB – Abdulla Nilam Sep 24 '20 at 05:08

score 8 · Answer 2 · answered Aug 08 '16 at 23:22

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

Marc Audet · Answer 3 · 2014-03-27T01:59:42.003

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.

Sorry maybe i gave a bad example , the problem occurs even if the insert function is in the same model and i call it by ($this->insertFunction...) — Tarek, Mar 06 '13 at 13:16
Hi Tarek, did you try get_instance() ? I am a bit curious about this; you are not doing anything out of the ordinary. Let me think about this over a cup of coffee, please check in later today. — Marc Audet, Mar 06 '13 at 15:38
@Marc Audet: when update operation return 0 (affected row) then transaction is rollback or not? Thanks. — secretlm, May 05 '13 at 15:17
@secretim Good question. You really should be checking `$this->db->trans_status()` instead of `$this->db->affected_rows()`, since the affected row result can vary depending exactly where you place the statement (inside or outside the transaction block). — Marc Audet, May 05 '13 at 20:51

score 4 · Answer 4 · answered Jul 21 '14 at 03:39

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

answered Jul 21 '14 at 03:39

Kahlil Vanz

169
1
3

2

trans_complete() already does a status check and rollback/commit – Alex Aug 01 '16 at 06:10

score 3 · Answer 5 · edited Jan 30 '14 at 15:07

3

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

edited Jan 30 '14 at 15:07

Tarek

3,810
3
36
62

answered Jan 30 '14 at 12:04

Diego

271
2
2

Why is this upvoted? In both versions (2 & 3), it's `start/complete` and `begin/rollback/commit`. – akinuri May 14 '21 at 16:33

score 0 · Answer 6 · answered Mar 01 '19 at 07:23

For single insert or update record you can use affected_rows function

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


//Check if there is a record affected
if($this->db->affected_rows() > 0)
              {
                  return true;
              }
              else
              { 
                  // if not succeeded
                  // check your last query 
                  die($this->db->last_query());
              }

Codeigniter Transactions

6 Answers6

Linked