Does the Laravel `increment()` lock the row?

Question

Does calling the Laravel increment() on an Eloquent model lock the row?

For example:

 $userPoints = UsersPoints::where('user_id','=',\Auth::id())->first();
 if(isset($userPoints)) {
      $userPoints->increment('points', 5);
 }

If this is called from two different locations in a race condition, will the second call override the first increment and we still end up with only 5 points? Or will they add up and we end up with 10 points?

It's a simple update query, so it depends on database implementation, but I think it should be 10 by the default config - meaning locking the table for each transaction — George G, Jul 21 '16 at 13:36
Alright, I'll go with that assumption, since the chances of that happening are rare enough as it is. — Pat, Jul 21 '16 at 13:39
OKay bro, see something similar: http://stackoverflow.com/questions/30527318/mysql-concurrent-updates-through-threads-on-a-simple-table — George G, Jul 21 '16 at 13:42
this is also great post http://stackoverflow.com/questions/20148/myisam-versus-innodb?rq=1 — George G, Jul 21 '16 at 13:44

score 3 · Accepted Answer · answered Jul 21 '16 at 13:53

To answer this (helpful for future readers): the problem you are asking about depends on database configuration.

Most MySQL engines: MyISAM and InnoDB etc.. use locking when inserting, updating, or altering the table until this feature is explicitly turned off. (anyway this is the only correct and understandable implementation, for most cases)

So you can feel comfortable with what you got, because it will work correct at any number of concurrent calls:

-- this is something like what laravel query builder translates to
UPDATE users SET points += 5 WHERE user_id = 1

and calling this twice with starting value of zero will end up to 10

To be clear (from my understanding): a *single query* would be atomic and those locks are internally taken care of by the database engine, but it becomes another story when you mix multiple SELECT+UPDATE statements together. So yes the `SET points += 5` will always work correctly (speaking for InnoDB, I dont know the specifics of MyISAM). — Flame, Apr 16 '22 at 00:30

score 2 · Answer 2 · answered Apr 16 '22 at 00:25

The answer is actually a tiny bit different for the specific case with ->increment() in Laravel:

If one would call $user->increment('credits', 1), the following query will be executed:

UPDATE `users`
SET `credits` = `credits` + 1
WHERE `id` = 2

This means that the query can be regarded as atomic, since the actual credits amount is retrieved in the query, and not retrieved using a separate SELECT.

So you can execute this query without running any DB::transaction() wrappers or lockForUpdate() calls because it will always increment it correctly.

To show what can go wrong, a BAD query would look like this:

# Assume this retrieves "5" as the amount of credits:
SELECT `credits` FROM `users` WHERE `id` = 2;

# Now, execute the UPDATE statement separately:
UPDATE `users`
SET `credits` = 5 + 1, `users`.`updated_at` = '2022-04-15 23:54:52'
WHERE `id` = 2;

Or in a Laravel equivalent (DONT DO THIS):

$user = User::find(2);
// $user->credits will be 5.
$user->update([
    // Shown as "5 + 1" in the query above but it would be just "6" ofcourse.
    'credits' => $user->credits + 1
]);

Now, THIS can go wrong easily since you are 'assigning' the credit value, which is dependent on the time that the SELECT statement took place. So 2 queries could update the credits to the same value while the intention was to increment it twice. However, you CAN correct this Laravel code the following way:

DB::transaction(function() {
    $user = User::query()->lockForUpdate()->find(2);
    $user->update([
        'credits' => $user->credits + 1,
    ]);
});

Now, since the 2 queries are wrapped in a transaction and the user record with id 2 is READ-locked using lockForUpdate(), any second (or third or n-th) instance of this transaction that takes place in parallel should not be able to read using a SELECT query until the locking transaction is complete.

Does the Laravel `increment()` lock the row?

2 Answers2