Laravel Eloquent ORM Transactions

Question

The Eloquent ORM is quite nice, though I'm wondering if there is an easy way to setup MySQL transactions using innoDB in the same fashion as PDO, or if I would have to extend the ORM to make this possible?

Answer 1

You can do this:

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

Everything inside the Closure executes within a transaction. If an exception occurs it will rollback automatically.

Answer 2

If you don't like anonymous functions:

try {
    DB::connection()->pdo->beginTransaction();
    // database queries here
    DB::connection()->pdo->commit();
} catch (\PDOException $e) {
    // Woopsy
    DB::connection()->pdo->rollBack();
}

---

Update: For laravel 4, the pdo object isn't public anymore so:

try {
    DB::beginTransaction();
    // database queries here
    DB::commit();
} catch (\PDOException $e) {
    // Woopsy
    DB::rollBack();
}

Answer 3

If you want to avoid closures, and happy to use facades, the following keeps things nice and clean:

try {
    \DB::beginTransaction();

    $user = \Auth::user();
    $user->fill($request->all());
    $user->push();

    \DB::commit();

} catch (Throwable $e) {
    \DB::rollback();
}

If any statements fail, commit will never hit, and the transaction won't process.

Answer 4

If you want to use Eloquent, you also can use this

This is just sample code from my project

        /* 
         * Saving Question
         */
        $question = new Question;
        $questionCategory = new QuestionCategory;

        /*
         * Insert new record for question
         */
        $question->title = $title;
        $question->user_id = Auth::user()->user_id;
        $question->description = $description;
        $question->time_post = date('Y-m-d H:i:s');

        if(Input::has('expiredtime'))
            $question->expired_time = Input::get('expiredtime');

        $questionCategory->category_id = $category;
        $questionCategory->time_added = date('Y-m-d H:i:s');

        DB::transaction(function() use ($question, $questionCategory) {

            $question->save();

            /*
             * insert new record for question category
             */
            $questionCategory->question_id = $question->id;
            $questionCategory->save();
        });

Answer 5

I'm Sure you are not looking for a closure solution, try this for a more compact solution

 try{
    DB::beginTransaction();

    /*
     * Your DB code
     * */

    DB::commit();
}catch(\Exception $e){
    DB::rollback();
}

Answer 6

For some reason it is quite difficult to find this information anywhere, so I decided to post it here, as my issue, while related to Eloquent transactions, was exactly changing this.

After reading THIS stackoverflow answer, I realized my database tables were using MyISAM instead of InnoDB.

For transactions to work on Laravel (or anywhere else as it seems), it is required that your tables are set to use InnoDB

Why?

Quoting MySQL Transactions and Atomic Operations docs (here):

MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB and BDB transactional storage engines. InnoDB provides full ACID compliance. See Chapter 14, Storage Engines. For information about InnoDB differences from standard SQL with regard to treatment of transaction errors, see Section 14.2.11, “InnoDB Error Handling”.

The other nontransactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called “atomic operations.” In transactional terms, MyISAM tables effectively always operate in autocommit = 1 mode. Atomic operations often offer comparable integrity with higher performance.

Because MySQL Server supports both paradigms, you can decide whether your applications are best served by the speed of atomic operations or the use of transactional features. This choice can be made on a per-table basis.

Answer 7

If any exception occurs, the transaction will rollback automatically.

Laravel Basic transaction format

    try{
    DB::beginTransaction();

    /* 
    * SQL operation one 
    * SQL operation two
    ..................     
    ..................     
    * SQL operation n */


    DB::commit();
   /* Transaction successful. */
}catch(\Exception $e){       

    DB::rollback();
    /* Transaction failed. */
}

Answer 8

The best and clear way:

DB::beginTransaction();

try {
    DB::insert(...);
    DB::insert(...);
    DB::insert(...);

    DB::commit();
    // all good
} catch (\Exception $e) {
    DB::rollback();
    // something went wrong
}