Safely increase counting columns on your DB, on a concurrent environment

Think of the following case. You have a View model that counts views for different pages on your web application. You need to increase the views count when someone visits a page. How would you implement that? That sounds easy but it could be tricky if you do not care.

Problem occurs when concurrent requests are coming in your application. In that case, it really matters you how increase your views count.

Below an example:

We can leverage Laravel's Concurrency facade (available on latest version), to demonstrate the problematic situation.

Watch this block of code:

public function handle()
{
    $view = View::query()->first();

    $callbacks = [];

    for($i=0; $i<20; $i++) {
        $callbacks[] = fn () => $view->update(['views_count' => $view->views_count + 1]);
    }

    Concurrency::run($callbacks);

    $view->refresh();

    dd($view->views_count);
}

It increases the 'views_count' column, concurrently. Callbacks are being executed 20 times, so the 'views_count' at the end should be 20, right?

Unfortunately that's not the case. This happens because multiple processes try to read the same 'views_count' column, at the same time. As a result, our views do not reflect the reality. That's a serious bug, ok maybe not for some views, but views is just an example. It could be a lot worse.

A safer way to do this is, instead of using:

$view->update(['views_count' => $view->views_count + 1]

is to use Eloquent's built-in increment method

$view->increment('views_count')

This will ensure that the increment is atomic, because it does not read the views_count on PHP, but immediately executes the increment query on SQL level.

update
  `views`
set
  `views_count` = `views_count` + 1,
  `views`.`updated_at` = '2024-09-22 20:23:12'
where
  `id` = 1

That should give you the expecting result. If not, you really need to watch out your DB's isolation level configuration and figure out why Atomicity doesn't work.

Just something to keep in mind, when running queries on a concurrent environment.