Why Codeigniter 4 failed the transaction, but insert some data in DB?

Question

Please Help! I'm can't finish transaction success.

When I passed in form wrong data and send the form, my transaction start, the first table skipped, because of failed validation, but the transaction continue working and some data insert in two and three tables. Why it's happening?

I'm validating data in the model, here my code

namespace App\Models;

use CodeIgniter\Model;

class Users extends Model
{
    protected $DBGroup              = 'default';
    protected $table                = 'users';
    protected $primaryKey           = 'user_id';
    protected $useAutoIncrement     = true;
    protected $insertID             = 0;
    protected $returnType           = 'array';
    protected $useSoftDelete        = false;
    protected $protectFields        = true;

    protected $allowedFields        = [
        'full_name', 'phone_user', 'inn_user', 'hospital_code', 'check_priz', 'winner', 'banned',
    ];

    // Dates
    protected $useTimestamps        = true;
    protected $dateFormat           = 'datetime';
    protected $createdField         = 'created_at';
    protected $updatedField         = 'updated_at';

    protected $validationRules      = [
        'full_name'     => 'required|min_length[3]|max_length[255]',
        'phone_user'    => 'required|is_unique[users.phone_user]|min_length[11]|max_length[11]|regex_match[/^((8|\+7)[\- ]?)?(\(?\d{3}\)?[\- ]?)?[\d\- ]{7,10}$/]|numeric',
        'inn_user'      => 'required|numeric|is_unique[users.inn_user]|min_length[12]|max_length[12]',
        'hospital_code' => 'integer|max_length[5]'
    ];
    protected $validationMessages   = [];
    protected $skipValidation       = false;

}

And this my function with the transaction

public function addNewUserAndCode($data)
    {
        $userModel = new UserModel;
        $CodeModel = new CodeModel;
        $CodeUserModel = new CodeUserModel;

        $db = \Config\Database::connect();
        $db->transBegin();

        //create user
        $id = $userModel->insert($data);

        //add code to user
        $promoCodeUserModel->insert([
            'user_id' => $id,
            'code' => $data['code']
        ]);

        //check used code
        $promoCodeModel->update($data['code'], ['used' => 1]);

        $db->transComplete();
        if ($db->transStatus() === FALSE) {
            $db->transRollback();
            log_message('debug', 'fail transaction');
            return $this->fail('Failed add user.', 400);
        } else {
            $db->transCommit();
            log_message('debug', 'success transaction');
        }
    }

Sorry for my English. :) Thank you very much!)

Answer 1

Hello Kiril and welcome to StackOverflow

The main purpose of transactions in the context of databases is to preserve data integrity in the event of unexpected situations where something bad happens on the level of DBMS or below which includes (but not limited to) the following situations:

• Power outage during the storage
• Hardware component(s) failure
• The operating system which hosts the DBMS crashes or freeze, or maybe the DBMS itself has crashed

The reason of "partial data commitment" in your case is that because:

1. You use insert() method to insert data
2. The method validates against the specified $validationRules
3. The validation process failed before anything ever hits the DBMS

So there's absolutely no problem on the DBMS level because nothing happens there, why should the transaction fail ?

The insert() method returns FALSE if the insertion fails for whatever reason, you could use this to solve the problem

Since you're running the transaction manually, all you need to do is to additionally CHECK IF $id EQUALS FALSE, so the condition become like:

if ($db->transStatus() === FALSE || $id === FALSE) {
    $db->transRollback();
    log_message('debug', 'fail transaction');
    return $this->fail('Failed add user.', 400);
} else {
    $db->transCommit();
    log_message('debug', 'success transaction');
}

Another proposed solution is to do the validation inside the Controller and check if the validation process fails, don't even start the transaction:

$validationRules = [
    'full_name'     => 'required|min_length[3]|max_length[255]',
    'phone_user'    => 'required|is_unique[users.phone_user]|min_length[11]|max_length[11]|regex_match[/^((8|\+7)[\- ]?)?(\(?\d{3}\)?[\- ]?)?[\d\- ]{7,10}$/]|numeric',
    'inn_user'      => 'required|numeric|is_unique[users.inn_user]|min_length[12]|max_length[12]',
    'hospital_code' => 'integer|max_length[5]'
];

$validator = \Config\Services::validator(); // Obtain an instance of the validator
$validator->setRules($validationRules); // Bind the validation rules to the validator
$isValid = $validator->withRequest($this->request)->run(); // Read the input from HTTP request and validate it against the rules, then check if they have a valid form

if ($isValid) {
    // Ok, you're good, you can now start the transaction here
    // Write your logic here ...
    if ($db->transStatus() === FALSE) {
        $db->transRollback(); // Restore the database to its original state, because a transaction should preserve data integrity whether it has failed or succeeded
        // Send HTTP 500 Internal Server Error response
    }
    else {
        // Everything went fine, you can peacefully commit
        $db->transCommit();
    }
}
else {
    // Malformed input, send HTTP 400 Bad Request response
}

Further info about validation can be found here: http://codeigniter.com/user_guide/libraries/validation.html