Codeigniter Framework Update Transaction (transaction) BUG and Its Solution

Because the ci transaction judges that the condition of error rollback is whether the statement is executed successfully, and when updating the operation, even if the number of affected statements is 0, the result of sql statement execution is still 1, because it is executed successfully, but the number of affected statements is 0.

Here are some ways to solve this problem:

For transactions that execute many statements at a time

You only need to decide whether to roll according to whether the number of affected items is 0 under the update operation. Let's assume that the second statement is an update operation.

// Adopt  Codeigniter  Manual mode of transactions 

    $this->db->trans_strict(FALSE);

    $this->db->trans_begin();

        

    $this->db->query('SELECT ...');//SELECT  Operation does not require special processing 

    $this->db->query('INSERT ...');//INSERT  There will be mistakes  Codeigniter  Automatic processing 

        

    $this->db->query('UPDATE ...');

    if (!$this->db->affacted_rows()) {// Above  UPDATE  Failure rolls back 

        $this->db->trans_rollback();

        //@todo  Exception handling part 

        exit();// Need to terminate or jump out to avoid the following  SQL  Code continues to execute! 

    }

        

    $this->db->query('DELETE ...');

    if (!$this->db->affacted_rows()) {// Above  DELETE  Failure rolls back 

        $this->db->trans_rollback();

        //@todo  Exception handling part 

        exit();// Need to terminate or jump out to avoid the following  SQL  Code continues to execute! 

    }

        

    $this->db->query('SELECT ...');//SELECT  Operation does not require special processing 

    $this->db->query('INSERT ...');//INSERT  There will be mistakes  Codeigniter  Automatic processing 

        

    if ($this->db->trans_status() === TRUE) {

        $this->db->trans_commit();

    } else {

        $this->db->trans_rollback();

        //@todo  Exception handling part 

    }

If there are not many statements executed at one time, you can make 1 judgment at the end to decide to roll back

If there is no update operation in the statement, automatic transaction can be used.