PHP MariaDB transaction sometimes it doesn't work properly

Part of my code is like this:

`$db = mysqli_connect("localhost", "root", "", "test");
mysqli_set_charset($db, "utf8mb4");
$user_email = mysqli_real_escape_string($db, $_POST['user_email']);
$user_name = mysqli_real_escape_string($db, $_POST['user_name']);
$display_name = mysqli_real_escape_string($db, $_POST['display_name']);
$user_token = md5(openssl_random_pseudo_bytes(32));
$created_at = date('Y-m-d H:i:s');
mysqli_begin_transaction($db, MYSQLI_TRANS_START_READ_WRITE);
$result = mysqli_query($db, "INSERT INTO users (user_email, user_name, display_name, user_status, user_token, created_at) VALUES ('$user_email', '$user_name', '$display_name', 1, '$user_token', '$created_at')");
if ($result) {
    $uid = mysqli_insert_id($db);
    $user_agent = mysqli_real_escape_string($_SERVER['HTTP_USER_AGENT']);
    $result = mysqli_query($db, "INSERT INTO usermeta (user_id, meta_key, meta_value) VALUES ($uid, 'user_logins', json_array(json_object('date_at', $created_at, 'ua' , '$user_agent')))");
    if ($result) {
        mysqli_commit($db);
        echo 'Commit.';
    }
}
if (!$result) {
    mysqli_rollback($db);
    echo 'Rolled back.';
}
mysqli_close($db);`

But sometimes it doesn’t work properly, I also use InnoDB engine, I read other articles, but it didn’t help my problem.

Can anyone solve my problem?

I also use InnoDB engine, I read other articles, but it didn’t help my problem.

>Solution :

In the first step, make sure again that all your Tables use the InnoDB engine.
If the Boolean value returned during Insert is TRUE, it does’nt necessarily mean the correct execution of the query, you can read more information in the PHP Documentation.

Therefore, make the following changes in your codes, then check the correct operation again:

if (mysqli_affected_rows($db) > 0) {
    $uid = mysqli_insert_id($db);
    $user_agent = mysqli_real_escape_string($_SERVER['HTTP_USER_AGENT']);
    mysqli_query($db, "INSERT INTO usermeta (user_id, meta_key, meta_value) VALUES ($uid, 'user_logins', json_array(json_object('date_at', $created_at, 'ua' , '$user_agent')))");
    if (mysqli_affected_rows($db) > 0) {
        mysqli_commit($db);
        echo 'Commit.';
    }
}
if (mysqli_affected_rows($db) <= 0) {
    if (mysqli_connect_errno()) {
        echo mysqli_connect_errno().": ".mysqli_connect_error();
    }
    mysqli_rollback($db);
    echo 'Rolled back.';
}

If the problem still persists, run your code once outside the test environment.