Laravel DB::transaction not rolling back on exception

Question

I'm having this small issue with Laravel 4.2 and DB::transaction. I was having an issue with transactions not being rolled back so I tried the easiest snippet of code and put it into routes.php for testing purposes:

routes.php:

DB::transaction(function(){

  $user = App::make("User");
  $user->save();
  throw new Exception("Should not create users");
});
...
...
... 
Some other code here 

Simply I try to create user within transaction closure and after user is created I throw exception to force rollback of transaction. My problem is that even thou exception is thrown, the transaction does not roll back. I'm getting new user in the database every time I refresh the app. The same code works as intended on the local machine, but on the server i'm planning to use for production it simply does not roll the transaction back. Do you have any ideas why ?

EDIT:

Server MySql: 5.1.73-cll - MySQL Community Server (GPLv2)

Server PHP: PHP 5.4.30 (cli) (built: Jul 19 2014 15:22:18)

Local PHP: 5.5.9

Local MySql: 5.6.16

Server is sitting on CentOs while local machine is Windows 7.

Answer 1

So I'm responding to my own question. InnoDb was not a default storage engine until MySql 5.5. In my case MYISAM was the default storage engine and did not support the transactions. What I had to do is enable InnoDB in my CPanel server installation of MySQL. Than I had to make sure each of the tables in my Laravel migrations was created with InnoDB engine. I did that by adding:

     $table->engine = "InnoDB"; 

to each migration file. Once all the tables were set up with InnoDB engine, transactions work as intended.

Answer 2

An alternative could be the settings in

/config/database.php

change value of engine from null to InnoDB

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => 'InnoDB',
        ],

Answer 3

If you are using multiple databases on your project, you need to specify in which database you want to commit and rollback.

DB::connection('name_of_database')->beginTransaction();
try {
    //Do something
    DB::connection('name_of_database')->commit();
} catch (Exception $e) {
    DB::connection('name_of_database')->rollback();
}

Answer 4

If transactions still aren't rolling back for you, and you are already using InnoDB, it could be due to the queries that you're running inside.

For example, there is a bug/feature of MySQL where TRUNCATE <table>; has an implicit commit, so the table is saved before you even try to roll back. The workaround is to use DELETE FROM <table>; followed by ALTER TABLE <table> AUTO_INCREMENT = 1; instead (if you wish to preserve the behavior of resetting the auto_increment id).

I'd recommend trying a basic query within php artisan tinker like:

DB::transaction(function () {
    //DB::statement('SET FOREIGN_KEY_CHECKS = 0;');

    $autoIncrement = DB::select("SHOW TABLE STATUS LIKE 'users'")[0]->Auto_increment;

    $id = DB::table('users')->insertGetId([
        'name' => 'Jane Doe',
        'email' => '[email protected]',
        'password' => 'password'
    ]);

    DB::table('users')->where('id', $id)->delete();

    // ALTER TABLE has implicit commit just like TRUNCATE, so prevents rollback:
    //DB::statement("ALTER TABLE `users` AUTO_INCREMENT = " . ((int) $autoIncrement));
    //DB::statement("ALTER TABLE `users` AUTO_INCREMENT = ?", [1]); // doesn't work
    //DB::statement("ALTER TABLE `users` AUTO_INCREMENT = :incrementStart", ['incrementStart' => 1]); // still doesn't work

    //DB::statement('SET FOREIGN_KEY_CHECKS = 1;');

    dd("User $id deleted, rolling back...");
});

Note the inconsistency in the statements, where it's 'users' in one place and `users` in another. Sometimes leaving a keyword unquoted works.

Note the danger around not being able to escape parameters in raw statements, where I had to typecast $autoIncrement to integer to be safe. If someone knows a workaround for that, please let us know!

I've included some more lines to play around with, because queries can fail due to foreign key constraints too.

Beware of errata, use cases that haven't been implemented yet, and inconsistencies between MySQL, PostgreSQL and other engines. Here be dragons.

More info:

transaction doesn't work on truncate

https://laracasts.com/discuss/channels/eloquent/db-transaction-doesnt-appear-to-rollback-properly

Difference between Laravel's raw SQL functions

Can I use transactions with ALTER TABLE?