Laravel 4.2 - Transaction rollback issue with multiple databases

Question

I am experiencing issues in rolling back a transaction involving tables from multiple databases. The primary table rollback behaves as expected, but the child row remains, and is now orphaned.

public function devUserCreateTest()
{
    DB::beginTransaction();

    try {

        $childUser  = new ChildUser; // Exists in database B
        $parentUser = new User; // Exists in database A

        $parentUser->setEmailAttribute('[email protected]');

        $parentUser->save();

        $childUser->parent_user_id = $parentUser->id;

        $message = sprintf('Parent user id: %s', serialize($childUser->id));

        $childUser->save();

        $message = sprintf('Core user id: %s | hta user id: %s', serialize($parentUser->id), serialize($childUser->id));

        throw new Exception('Testing....');

        DB::commit();

    } catch (Exception $e) {

        Log::warning(sprintf('Exception: %s', $e->getMessage()));

        DB::rollback();
    }

    return $this->buildResponse(array('message' => $message));
}

Looks like this works:

public function devUserCreateTest()
{
    $dboA = DB::connection();
    $dboB = DB::connection('b_database');

    $dboA->beginTransaction();
    $dboB->beginTransaction();

    try {

        $childUser  = new ChildUser; // Exists in database B
        $parentUser = new User; // Exists in database A

        $parentUser->setEmailAttribute('[email protected]');

        $parentUser->save();

        $childUser->parent_user_id = $parentUser->id;

        $message = sprintf('Parent user id: %s', serialize($childUser->id));

        $childUser->save();

        $message = sprintf('Core user id: %s | hta user id: %s', serialize($parentUser->id), serialize($childUser->id));

        throw new Exception('Testing....');

        $dboA->commit();
        $dboB->commit();

    } catch (Exception $e) {

        Log::warning(sprintf('Exception: %s', $e->getMessage()));

        $dboA->rollback();
        $dboB->rollback();
    }

    return $this->buildResponse(array('message' => $message));
}

Answer 1

You would have to set a transaction on database B also.

Since you are not posting ChildUser code, here is an example :

app/models/ChildUser.php :

class ChildUser extends Eloquent
{
    protected $connection = 'some_connection'; // as defined in app/config/database.php
}

Then your code

public function devUserCreateTest()
{
    DB::beginTransaction();
    DB::connection('some_connection')->beginTransaction(); // same as the one used in model ChildUser

    try {

        $childUser  = new ChildUser; // Exists in database B
        $parentUser = new User; // Exists in database A
        $parentUser->setEmailAttribute('[email protected]');
        $parentUser->save();
        $childUser->parent_user_id = $parentUser->id;
        $message = sprintf('Parent user id: %s', serialize($childUser->id));
        $childUser->save();
        $message = sprintf('Core user id: %s | hta user id: %s', serialize($parentUser->id), serialize($childUser->id));
        throw new Exception('Testing....');
        DB::commit();
    } catch (Exception $e) {
        Log::warning(sprintf('Exception: %s', $e->getMessage()));

        DB::rollback();
        DB::connection('some_connection')->rollback();
    }
    return $this->buildResponse(array('message' => $message));
}