Search code examples
laraveltransactionslaravel-5

transaction doesn't work on truncate

I'm trying Laravel transaction for the first time... I do most of my queries with Eloquent and since there no transaction there I have to do a mixture of Eloquent and query builder.

Here is my code:

DB::beginTransaction();

try{
    Setting::truncate();
    Setting::insert($data);
    DB::commit();
    jok('all ok');

}
catch (\Exception $e)
{
    DB::rollback();
    jerror('some error accorded! ');
}

So I've tied to add some invalid data to Settings and i got the some error accorded error as expected but the query before INSERT Setting::truncate(); was executed anyway and I ended up with a empty table.

So either I'm doing something wrong or transaction doesn't work on truncate.

Solution

  • TRUNCATE TABLE in a database transaction will cause a implicit COMMIT action. DELETE does not have that same behavior, so you can safely run

    DELETE FROM tblname

    This will clear your table and still keep the desired rollback feature of a transaction.

    See the MySQL documentation on TRUNCATE. The bullet points explain this functionality http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html