phpmysqlcodeignitertransactionscodeigniter-3

Codeigniter Transactions


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

Solution

  • Codeigniter 03

    Using transactions means supporting databases in inserting 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 it's not an insert data with the model and Controller parallel. Transaction should be coded in the Model(I will write in Model in my answer).

    Load this stuff as well

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

    Assumptions

    You have used $data and $test in your code as arrays. So, I assume there are two arrays 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, you 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 run 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.

    Codeigniter 04

    This is the same as CI3 (Above but). They added a few changes in CI4 (v4).

    Keyword change in short

    Feature CodeIgniter 3 (CI3) CodeIgniter 4 (CI4)
    Start Transaction $this->db->trans_start(); $this->db->transBegin();
    Complete Transaction $this->db->trans_complete(); $this->db->transCommit();
    Rollback Transaction $this->db->trans_rollback(); $this->db->transRollback();
    Transaction Status $this->db->trans_status(); $this->db->transStatus();
    Error Handling Transaction automatically rolled back on error if trans_complete() returns FALSE. Transactions automatically rolled back on error if transStatus() returns FALSE.
    Exception Handling Not available $this->db->transException();

    For Error Handling

    You can use a regular

    if ($this->db->transStatus() === false) {
        
    }
    

    or try{}, catch {}

    use CodeIgniter\Database\Exceptions\DatabaseException;

    try {
        $this->db->transException(true)->transStart();
        $this->db->query('SQL QUERY...');
        $this->db->transComplete();
    } catch (DatabaseException $e) {
        // Automatically rolled back already.
    }
    

    To Disable transactions

    By default, it's on, as you know before. To set it off

    $this->db->transOff();
    ...
    

    For Testing

    $this->db->transStart(true); # set this to true
    

    This will roll back automatically.

    CI4 Transactions document