transaction doesn't work on truncate

Question

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.

i have the same case on server side – CodeToLife Oct 06 '20 at 07:08 — CodeToLife, Oct 06 '20 at 07:08

score 16 · Accepted Answer · answered Aug 07 '16 at 11:33

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

score 2 · Answer 2 · answered Oct 08 '21 at 13:51

2

A better solution to do delete is to use Model method:

Setting::query()->delete();

answered Oct 08 '21 at 13:51

Riccardo Venturini

400
2
6

Why is this a better solution? – Jeremy Caney Oct 08 '21 at 20:55
1

Hi, for two reason: 1. it use Eloquent classes instead of raw SQL, 2. truncate is not rolled back in a transaction, but delete is – Riccardo Venturini Oct 11 '21 at 08:12

transaction doesn't work on truncate

2 Answers2

Linked