0

I want to insert a parent item and all of it's childs into a database. If any of the childs fails to create I'd like to abort the entire transaction.

I've tried using the following snippet:

DB::beginTransaction();

try
{
    // Create parent item
    $one = new ModelOne();
    $one->key = 'Parent Key';
    $one->title = 'Parent title';
    $one->save();

    // Create child items
    foreach ($items as $item) {
        $two = new ModelTwo();
        $two->parent_id = $one->id;
        $two->content = $item->content;
        $two->status = $item->status;
        $two->save();
    }

    DB::commit();
    return response(['message'=>'ALL GOOD'], 200);
}
catch (\ Illuminate\Database\QueryException $e)
{
    DB::rollBack();
    return response(['message'=>'FAILURE'], 500);
}

However, when an exception is thrown (say because a key is duplicated among the childs) ModelOne ($one) is saved in the database even though DB::commit() is never called.

What am I missing?

I'm using Laravel 5.1.

appa yip yip
  • 1,404
  • 1
  • 17
  • 38

2 Answers2

1

Turns out the issue has nothing to do with the code I provided in the question.

The problem was that my tables were being created using MyISAM which does not support transactions.

Using InnoDB instead makes the code work as expected. To do so add $table->engine = 'InnoDB'; to your migrations.

appa yip yip
  • 1,404
  • 1
  • 17
  • 38
0

I use this code for transactions and it works:

DB::transaction(function () { 
});

With this code you do not need to commit or rollback. They are called automatically.

In your case you will have:

try {
    DB::transaction(function () use ($items) ({ 
        // ... your inserts
    });
catch () {}
Giacomo M
  • 4,450
  • 7
  • 28
  • 57