CODEWITHSUNDEEP
laravellaravel-5transactionslaravel-5.3
Sadat
Sadat

Reputation: 3501

Laravel 5.3 DB:transaction is committing even some queries failed

I have a User model for basic user info, then a Role model and RoleUser model to associate roles with user. On user edit form, additional role can be added to that user. So, here two DB operations are done within a DB::transaction, 

1) Update User info into User model
2) Add role to user

The issue is, if "Add role to user" fails, it doesn't Rollback changes in "User" model which already updated successfully.

Here is my sample code-

In Controller:

$response =
DB::transaction(function() use($user_data, $input) {
    //Update changes in basic user info using "User" Model
    $response = User::updateUser($user_data['user']);

    if ($response->status===FALSE) {//not updated
        return $response;
    }
    if (!empty($user_data['roles'])) {
        $roles = [];
        foreach ($user_data['roles'] as $role) {
            $roles[] = ['role_id' => $role, 'user_id' => $user_data['user']['id'], 'created_by' => $this->curr_user->id, 'updated_by' => $this->curr_user->id];
        }
    //Add new roles to the user using "RoleUser" Model
        $response3 = RoleUser::createRoleUser($roles);
        if ($response3->status===FALSE) {//failed to add
            return $response3;
        }                            
    }

    return $response;
}, 5);

//source of createRoleUser method in RoleUser model

try {
    DB::table($table)->where('id', $id)->update($changes);
} catch (\Illuminate\Database\QueryException $qe) {
    return (object) ['status' => FALSE, 'error' => $qe->errorInfo];
} catch (\Exception $e) {
    return (object) ['status' => FALSE, 'error' => [$e->getCode(), 'non-DB', $e->getMessage()]];
}
return (object) ['status' => TRUE, 'data' => $changes + ['id' => $id]];

//source of createRoleUser method in RoleUser model

try {
    $new_rec_id = DB::table('role_users)->insertGetId($new_data);
    $new_rec = FALSE;
    if ($new_rec_id) {
        $new_rec = DB::table($table)->where('id', $new_rec_id)->first();
    }
} catch (\Illuminate\Database\QueryException $qe) {
    return (object) ['status' => FALSE, 'error' => $qe->errorInfo];
} catch (\Exception $e) {
    return (object) ['status' => FALSE, 'error' => [$e->getCode(), 'non-DB', $e->getMessage()]];
}
return (object) ['status' => TRUE, 'data' => $new_rec];

Upvotes: 0

Views: 377

Answers (1)

patricus
patricus

Reputation: 62248

You have to throw an exception from within the transaction closure in order for the transaction to trigger the rollback. If no exception is thrown, the transaction will commit.

Keeping this in mind, that means the call to the transaction function needs to be wrapped in a try/catch, as the code that handles the rollback will rethrow the exception after the rollback for your application code to handle.

So, your code would look something like:

try {
    $response = DB::transaction(function() use($user_data, $input) {
        //Update changes in basic user info using "User" Model
        $response = User::updateUser($user_data['user']);

        if ($response->status===FALSE) {//not updated
            // throw exception to trigger rollback
            throw new \Exception($response->error);
        }
        if (!empty($user_data['roles'])) {
            $roles = [];

            foreach ($user_data['roles'] as $role) {
                $roles[] = ['role_id' => $role, 'user_id' => $user_data['user']['id'], 'created_by' => $this->curr_user->id, 'updated_by' => $this->curr_user->id];
            }

            //Add new roles to the user using "RoleUser" Model
            $response3 = RoleUser::createRoleUser($roles);
            if ($response3->status===FALSE) {//failed to add
                // throw exception to trigger rollback
                throw new \Exception($response3->error);
            }                            
        }

        // return without exception to trigger commit
        return $response;
    }, 5);
} catch (\Exception $e) {
    echo 'uh oh: '.$e->getMessage();
}

Upvotes: 3

Related Questions