Laravel Database Transactions: 3 Practical Examples

Database transactions are very useful for multiple database operations, and Laravel has functionality for them. But what would be the practical examples of WHEN you should use them?

In short, transactions are useful for multiple database operations, when you want to make sure that if either of them fails, all of them would be rolled back automatically.

In this article, I will show three typical examples, in Laravel:

  • Creating a new record with many-to-many related records
  • Deleting multiple records for a user
  • Updating summary table after a new record

Let's get practical.


Example 1. Many-to-Many with Transaction.

Take a look at this typical Controller code:

public function store(StoreUserRequest $request) {
$user = User::create($request->validated());
$user->roles()->attach($request->input('roles'));
 
return redirect()->route('users.index');
}

As you can see, there's a new User record, and then multiple roles are attached to the User. But what if something goes wrong in the second sentence?

Let's imagine that $request->input('roles') is passed not as array but as an invalid string. What happens then?

Laravel Database Transaction Error

And the worst part is not about the error, but the fact that the User record has been actually saved to the database.

In the case of users, it may have a bad consequence of email being already taken, although the registration hasn't actually been finished, as the users.email field is unique on the database level.

That's why it's beneficial to use a Database Transaction here:

use Illuminate\Support\Facades\DB;
 
// ...
 
public function store(StoreUserRequest $request) {
DB::transaction(function() use ($request) {
$user = User::create($request->validated());
$user->roles()->attach($request->input('roles'));
}
 
return redirect()->route('users.index');
}

Notice: Keep in mind that you need to pass use ($request) or any other external variable that you need to use inside of the transaction function.

Now, don't get me wrong: you will still get the same error "Incorrect integer value: 'abcd' for column 'role_id' at row 1". But the User creation statement will be rolled back, and you won't see the user in the database.


Example 2. Deleting Multiple Records for User

Let's imagine you want to delete the record which has a lot of hasMany/belongsToMany relationships. You need to delete them as well, right? If you haven't set the cascadeOnDelete() on the DB level in migrations, you need to do it manually.

Something like this:

$profile->avatar->forceDelete();
MediaTag::whereProfileId($profile->id)->delete();
StatusHashtag::whereProfileId($profile->id)->delete();
DirectMessage::whereFromId($profile->id)->delete();
FollowRequest::whereFollowingId($profile->id)
->orWhere('follower_id', $profile->id)
->forceDelete();
Follower::whereProfileId($profile->id)
->orWhere('following_id', $profile->id)
->forceDelete();
Like::whereProfileId($profile->id)->forceDelete();
 
// ... only then delete the profile itself:
$profile->delete();

Imagine what happens if some middle sentence in this code snippet fails. So we have deleted something but not everything?

Of course, compared to the previous example, the consequence isn't as harsh, because, well, we still want to delete those records anyway, we would just do it later.

But still, the profile would remain active but wouldn't see some of their data, like username without avatar. Not cool, right?

Just add a few lines of code:

DB::transaction(function() use ($profile) {
$profile->avatar->forceDelete();
MediaTag::whereProfileId($profile->id)->delete();
StatusHashtag::whereProfileId($profile->id)->delete();
DirectMessage::whereFromId($profile->id)->delete();
FollowRequest::whereFollowingId($profile->id)
->orWhere('follower_id', $profile->id)
->forceDelete();
Follower::whereProfileId($profile->id)
->orWhere('following_id', $profile->id)
->forceDelete();
Like::whereProfileId($profile->id)->forceDelete();
 
$profile->delete();
});

Example 3. Updating "Summary" Table

Imagine a project with users and financial operations. Usually, they would be called "Transactions", but to avoid confusion with the subject of the article, I will call them just "Expenses".

You need to track all Expenses and also the current balance of each user. So, after every purchase, you would do something like this:

Expense::create($expenseDetails);
$user->decrement('balance', $expenseDetails->amount);

Sounds trivial, but in more complex scenarios, you would also need to update some more summary data in other tables, for some reporting.

Here, the consequence of not using DB transactions is huge: users would have more money to spend than they should.

Let's fix this:

DB::transaction(function() use ($expenseDetails, $user) {
Expense::create($expenseDetails);
$user->decrement('balance', $expenseDetails->amount);
});

So, these are just three simple examples of DB Transactions. I hope they will push you towards making your data correct all the time.

avatar

this is good summary for using db transaction. thank you.

two things, can i know how to use the DB transaction without the function closure. whenever i use without function closure, it often doesn't save in the db.

secondly, how to handle if the code within the db transaction function closure doesnt execute?

avatar

Hi Samuel, not sure about "often doesn't save in the db" or "doesn't execute", would need to debug the exact code and exact case, to answer that.

avatar

Good day, Samuel. Fortunately, you can utilise a DB transaction without closure it.

try { DB::beginTransaction();

// Do something and save to the db...

// Commit the transaction

DB::commit();

} catch (\Exception $e) {

// An error occured; cancel the transaction...

DB::rollback();

// and throw the error again.

throw $e;

}

avatar

Hey Povilas, great rework, love the new design. Is there a way to 👍🏻 or ❤️ an article to show appreciation/engagement instead of cluttering comments?

Ps> this artile was 👍🏻. just the thing i needed.

avatar

Hi, I don't believe in the Likes on the articles anymore, looked at Dev.to articles with 1-3 likes. The best engagement/appreciation is on social media, so the best way would be to share the article somewhere on your profile on social networks.

P.S. Glad the article helped!

avatar

thank you Povilas for all the work and contribution that you do for this community! i can't say that enough!

👍 1
avatar

by the way Povilas, how does one edit his profile picture here? i see that some have their profile picture set up already.

avatar

Good question, I don't know to be honest. I use laravel-comments.com by Spatie here and I think they use Gravatar to take the pictures from, by your email.

avatar
Enrique De Jesus Robledo Camacho

Good info thanks. I was wondering in nested transactions is that a wrong thing to do? or does it works at all?

avatar

I kinda defeats the purpose - open another transaction while the current one isn't finished?

Not sure what you're trying to accomplish, but it doesn't feel logical to me.

avatar

Thanks for this article. For the section "Deleting Multiple Records for User", let's suppose you want to delete the user account with all his informations and activities like the posts, articles he created since years.

As this single "user account deletion" action may take time, let's imagine that you delete the "user record" in databse and you delegate the rest of deletion to a job to optimize the response duration.

let's say we have something like this :

DB::transaction(function() use ($expenseDetails, $user) {
    $user->delete();
		
		// Mass deletion are applied inside these jobs
   casadeDeletingUserInformationsJob::dispatch($user); 
	 casadeDeletingUserArticlesJob::dispatch($user);
});

if something went wrong, will the queries executed inside the jobs be rolled back ?

avatar

Not really, because your jobs are usually async (this means that they are taken away by another process and do not impact the main one). For your case, it would be better to either delete everything on the same call or add handlers for job failures. So for example, if a job failed - you revert all the previous changes and cancel all other jobs.

ps. You should use job chains for this, as they would then run one after another if everything is success

avatar

Thanks for the reply. Is job chaining the same as jobs queuing ?

avatar

Job chaining is on top of jobs queue. It just makes sure that all the jobs in the chain are handled in order, rather than handled by different workers.

Imagine you have: A -> B -> C

These jobs have to run in order, so chain helps to ensure that

Like our articles?

Become a Premium Member for $129/year or $29/month
What else you will get:
  • 55 courses (987 lessons, total 46 h 42 min)
  • 77 long-form tutorials (one new every week)
  • access to project repositories
  • access to private Discord

Recent Premium Tutorials