CODEWITHSUNDEEP
phplaravel
wesside
wesside

Reputation: 5740

Laravel Eloquent ORM Transactions

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?

Upvotes: 135

Views: 166325

Answers (8)

Amir Hosseinzadeh
Amir Hosseinzadeh

Reputation: 8458

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
}

Upvotes: 1

Chris
Chris

Reputation: 58182

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.

Upvotes: 52

Jürgen Paul
Jürgen Paul

Reputation: 15007

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();
}

Upvotes: 122

Md. Saidur Rahman Milon
Md. Saidur Rahman Milon

Reputation: 2911

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. */
}

Upvotes: 6

Laurence
Laurence

Reputation: 60048

You can do this:

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

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

Upvotes: 223

Imal Hasaranga Perera
Imal Hasaranga Perera

Reputation: 10029

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();
}

Upvotes: 22

Aditya Kresna Permana
Aditya Kresna Permana

Reputation: 12099

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();
        });

Upvotes: 43

dmmd
dmmd

Reputation: 3009

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.

Upvotes: 12

Related Questions