Invalid ThinkPHP multi table rollback

Today, for the first time, we used multi table rollback and encountered a pit. The error code is as follows:

try{
    $Member = D("Member");
    $Member->startTrans();
    $member_condition['id'] = 11641;
    $member_data['id'] = 10000;
    $member_res = $Member->where($member_condition)->save($member_data);

    if ($member_res === 1) {
        try{
            $User = D("User");
            $User->startTrans();
            $user_condition['account'] = '111111';
            $user_data['username'] = "4324";
            $user_res = $User->where($user_condition)->save($user_data);
            if ($user_res === 1) {
                $User->commit();
                $Member->commit();
                echo "All modified successfully";
            }
            else {
                $User->rollback();
                $Member->rollback();
                echo "User Table is not affected, rollback all!";
            }
        }catch (Exception $e){
            $User->rollback();
            $Member->rollback();
            echo "User Modify exception, rollback all!";
        }
    }
    else {
        $Member->rollback();
        echo "Member Table is not affected, rollback!";
    }
}catch (Exception $e){
    $Member->rollback();
    echo "Member Modification exception!";
}

My idea is to start transactions for Member and User respectively. If one of the tables fails to be modified, all of them will be rolled back. But the fact is that after two transactions are opened, neither of them can be rolled back. If only one transaction is opened, the transaction can be rolled back. No explanation was found in the official tp documents. The solution is as follows:

try{
    $Model = M();
    $Model->startTrans();
    $member_condition['id'] = 11641;
    $member_data['id'] = 10000;
    $member_res = $Model->table('party_member')->where($member_condition)->save($member_data);

    $user_condition['account'] = '111111';
    $user_data['username'] = "4324";
    $user_res = $Model->table('party_user')->where($user_condition)->save($user_data);

    if ($member_res === 1 && $user_res === 1) {
        echo "commit";
        $Model->commit();
    }
    else{
        echo "rollback";
        $Model->rollback();
    }
}catch (Exception $e){
    echo "exception occurred";
    $Model->rollback();
}

For multi table transactions, first instantiate an empty object with the M function, and use the table method to operate on multiple tables. If the operation is successful, it will be submitted, and if it fails, it will be rolled back.

Another point to note is that in some integrated environments, MySQL's default engine is MyISAM. If you want to provide transaction support, you need to change the database engine to InnoDB.

Tags: MySQL Database

Posted on Tue, 31 Mar 2020 00:19:34 -0700 by abhijeet