Transaction in Laravel rolled back incompletely-CodePudding

I have an array of Organization model. Each Organization contains an array of User model.

There is a many-to-many relationship between them using the OrganizationUser model.

If I have problems inserting an Organization, an User, or a relationship between them, I want to roll everything back.

But I also use "try catch" blocks to see at which stage the error occurred. To test this, I explicitly made a mistake in the OrganizationUser relationship object. I'm using an User id that can't be in the Organization.

This rolls back the Organization, relationships, and first User, but all other Users are added to the database.

DB::beginTransaction();
$org->save();
foreach ($org->user_list as $user) {
    try {
        $user->save();
    } catch (\Exception $exception) {
        DB::rollback();
        Log::error('Error user insert : '. $user->inn .' '. $exception->getMessage());
    }

    try {
        $orgs_user = OrganizationUser::create([
            'user_id' => 80,
            'org_id' => $org->id,
        ]);
    } catch (\Exception $exception) {
        DB::rollback();
        Log::error('Insert user_org relationship : ' . $exception->getMessage());
    }
}

When I didn't use the "try catch" block it worked well

CodePudding user response：

DB::rollback does not stop the execution of the code.

If you are in a catch block the transaction will be rolled back but the next block (as well as the rest of the iteration) will continue.

Use a break; after the rollback to break out of the loop completely like below:

DB::beginTransaction();
$org->save();
foreach ($org->user_list as $user) {
    try {
        $user->save();
    } catch (\Exception $exception) {
        DB::rollback();
        Log::error('Error user insert : '. $user->inn .' '. $exception->getMessage());
        break;
    }

    try {
        $orgs_user = OrganizationUser::create([
            'user_id' => 80,
            'org_id' => $org->id,
        ]);
    } catch (\Exception $exception) {
        DB::rollback();
        Log::error('Insert user_org relationship : ' . $exception->getMessage());
        break;
    }
}

You can also return; or just exit() depending on how you want to handle the error

CodePudding user response：

When you call DB::rollBack() (by the way it's rollBack with a capital B), that ends the transaction. The rest of the queries will execute as normal.

The safest way to use transactions is to put the entire thing in a single try catch. The \Exception itself should contain enough information to know what caused it in its message or stack trace.

try {
    DB::beginTransaction();
    $org->save();
    foreach ($org->user_list as $user) {
        $user->save();
        $orgs_user = OrganizationUser::create([
            'user_id' => 80,
            'org_id' => $org->id,
        ]);
    }
    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
}

It is a bit verbose, but you can also use the DB::transaction() method.

DB::transaction(function () use ($org) {
    $org->save();
    foreach ($org->user_list as $user) {
        $user->save();
        $orgs_user = OrganizationUser::create([
            'user_id' => 80,
            'org_id' => $org->id,
        ]);
    }
});