If your Laravel database transactions aren’t working, this might be the problem

If it has happened to you as it did to me, that you are quietly trying out new things in Laravel 8 and suddenly realise that, even though you define them manually, transactions don’t work, and even if you trigger a rollback on all your operations they still get written to the database, with code similar to this:

        DB::beginTransaction();
        try {
            $tabla->update($datos);
            DB::rollback();
        } catch (PDOException $e) {
            DB::rollback();
        }

The problem lies in MySQL and in the table engine you have defined. If your tables use the MyISAM engine, it does not support transactions, so you will need to change all your tables to the InnoDB engine and then you will be able to use transactions in Laravel.

To do this, you have to follow these steps:

1.- Open your app’s config/database.php file and, in your connection, add the following:

‘engine’ => ‘InnoDB’,

2.- Go to your database and check which tables use the MyISAM engine and which use InnoDB. To change all the tables that are on the wrong engine, the best option is to run this query:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'nombre_esquema'
AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE';

where nombre_esquema is the name of your database schema.

With the result of this query, you will get a series of SQL commands; if you copy them into the database and run them, you will see that the engine has changed from MyISAM to InnoDB on all the tables in your database.

3.- Go into your Laravel application and test it: now you will see that the rollback works correctly and all your transactions are working properly.

I hope this short article solves the problem for you as it did for me, after searching for quite a while for why on earth transactions weren’t working in Laravel.