Laravel database transactions aren't working

Question

I'm trying to setup a database transaction in Laravel 5.5, but it does not seem to work. I use MySQL 5.7.20, and all tables from the tools schema are InnoDB. I'm also running PHP 7.2.3.

I have this code:

DB::beginTransaction();
try {
    // checks whether the users marked for removal are already assigned to the list
    foreach ($removeStudents as $removeStudent) {
        if ($ls->allocatedStudents->find($removeStudent->id) === null) {
            throw new Exception('userNotAllocated', $removeStudent->id);
        } else {
            DB::connection('tools')->table('exercises_list_allocations')
                ->where('user_id', $removeStudent->id)
                ->where('exercises_list_id', $ls->id)
                ->delete();
        }
    }

    // checks whether the users marked for removal are already assigned to the list
    foreach ($addStudents as $addStudent) {
        if ($ls->allocatedStudents->find($addStudent->id) === null) {
            DB::connection('tools')->table('exercises_list_allocations')
               ->insert([
                   'user_id' => $addStudent->id,
                   'exercises_list_id' => $ls->id
               ]);
        } else {
            throw new Exception('userAlreadyAllocated', $addStudent->id);
        }
    }

    DB::commit();
} catch (Exception $e) {
    DB::rollBack();
    return response()->json(
        [
            'error' => $e->getMessage(),
            'user_id' => $e->getCode()
        ], 400
    );
}

And it does not rollback the transaction. If an exception is found after some deletes or inserts, they are not reverted.

At first I thought it coulde be an issue in MySQL, so I tried to manually run the following SQL queries:

START TRANSACTION;
DELETE FROM tools.exercises_list_allocations WHERE user_id = 67 AND exercises_list_id=308;
DELETE FROM tools.exercises_list_allocations WHERE user_id = 11479 AND exercises_list_id=308;
INSERT INTO tools.exercises_list_allocations (user_id, exercises_list_id) VALUES (1,308);
INSERT INTO tools.exercises_list_allocations (user_id, exercises_list_id) VALUES (2,308);
INSERT INTO tools.exercises_list_allocations (user_id, exercises_list_id) VALUES (3,308);
ROLLBACK;

And it rolls back all deletes and all inserts (as expected), no changes happening to the tools.exercises_list_allocations table. So, I ruled out the database server as being the problem.

So, I figured it should be something with the PHP code. I searched the web for issues similar to mine, and tried some reported solutions.

I tried to use the DB::transaction() method with an anonymous function instead of a try/catch block, to no success.

I tried to use the Eloquent ORM instead of the DB::insert() and DB::delete() methods, both trying the DB::transaction() method with an anonymous function and DB::beginTransaction(), DB::commit() and DB::rollBack() methods, to no success.

I tried disabling stric mode and forcing the engine to be InnoDB in config/database.php, to no success.

What am I doing wrong? I need all the deletes and inserts to be run in a single atomic transaction.

Answer 1

If you, as myself, have multiple database connections configured in your app, you must select a connection before invoking the transaction methods, as suggested by ThejakaMaldeniya's comment, if the queries you want to run aren't in the default connection:

DB::connection('tools')->beginTransaction();
DB::connection('tools')->commit();
DB::connection('tools')->rollBack();

And it works perfectly.