Race conditions in Laravel when using split "read" and "write" database connections

Question

I have a Laravel application which uses a lot of AJAX POST and GET requests (Single Page Application). Once an item is saved via POST, a GET request is sent to reload parts of the page and get any new data.

After enabling split read and write database connections using the Laravel connection configuration, the application runs incredibly quickly (never thought this would be a problem!). It saves and then requests so quickly that the RO database (reporting just 22ms behind) doesn't get chance to update and I end up with old information.

I have enabled the sticky parameter in the database configuration which I thought would mitigate the problem, but the POST and GET requests are separate so the stickiness gets lost.

I could rewrite a large portion of the application POST requests respond with the correct data, but this doesn't work for reloading many components at once and is an enormous job so I see this as a last resort.

Another idea I had was to modify the getReadPdo(){...} method and $recordsModified value inside the Database Connection class so that the stickiness is saved on the user's session for up-to 1 second. I was unsure if this would cause any further issues with speed or excessive session loading that it would cause more problems.

Has anyone experienced this before or have any ideas on how to tackle the problem?

Thanks in advance.

Answer 1

I've published as a package!

• mpyw/laravel-cached-database-stickiness: Guarantee database stickiness over the same user's consecutive requests

Installing

composer require mpyw/laravel-cached-database-stickiness

The default implementation is provided by ConnectionServiceProvider, however, package discovery is not available. Be careful that you MUST register it in config/app.php by yourself.

<?php

return [

    /* ... */

    'providers' => [

        /* ... */

        Mpyw\LaravelCachedDatabaseStickiness\ConnectionServiceProvider::class,

        /* ... */

    ],

    /* ... */
];

Thats all! All problems will be solved.

Answer 2

Thought I'd update and answer this in case anyone else came across the same issue.

This isn't a perfect solution but has worked well over the last week or so.

Inside the AppServiceProvider boot() method, I added the following

            DB::listen(function ($query) {
                if (strpos($query->sql, 'select') !== FALSE) {
                    if (time() < session('force_pdo_write_until')) {
                        DB::connection()->recordsHaveBeenModified(true);
                    }
                } else {
                    session(['force_pdo_write_until' => time() + 1]);
                }
            });

In a nutshell, this listens to every DB query. If the current query is a SELECT (DB read), we check to see if the "force_pdo_write_until" key inside the user session has a timestamp that is more than the current time. If it is, we trick the current DB connection into using the ReadPDO by utilizing the recordsHaveBeenModified() method - this is how the core Laravel sticky sessions are normally detected

If the current query is not a SELECT (most likely a DB Write), we set the session variable for "force_pdo_write_until" for 1 second in the future.

Any time a POST request is sent, if the next GET request is within 1 second of the previous query, we can be sure that the current user will be using the RW DB connection and get the correct results.

---

Update (09/12/19):

It turns out the solution above doesn't actually modify the DB connection at all, it was just adding a few milliseconds of processing time to any request so looked like it was working about 75% of the time (because the DB replica lag fluctuates depending on load).

In the end I decided I'd go a bit deeper and override the DB connection class directly and modify the relevant functions. My Laravel instances uses MySQL, so I overrode the Illuminate\Database\MySqlConnection class. This new class was registered through a new service provider, which in turn is loaded through the config.

I've copied the config and files I used below to make it easier for any new developers to understand. If you're copying these directly, make sure you also add the 'sticky_by_session' flag to your connection config as well.

config/database.php

    'connections' => [
        'mysql' => [
            'sticky' => true,
            'sticky_by_session' => true,
             ...
        ],
    ],

config/app.php

    'providers' => [
        App\Providers\DatabaseServiceProvider::class
        ...
    ],

app/Providers/DatabaseServiceProvider.php

<?php

namespace App\Providers;

use App\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        if (config('database.connections.mysql.sticky_by_session')) {
            Connection::resolverFor('mysql', function ($connection, $database, $prefix, $config) {
                return new MySqlConnection($connection, $database, $prefix, $config);
            });
        }
    }
}

app/Database/MySqlConnection.php

<?php

namespace App\Database;

use Illuminate\Database\MySqlConnection as BaseMysqlConnection;

class MySqlConnection extends BaseMysqlConnection
{
    public function recordsHaveBeenModified($value = true)
    {
        session(['force_pdo_write_until' => time() + 1]);
        parent::recordsHaveBeenModified($value);
    }

    public function select($query, $bindings = [], $useReadPdo = true)
    {
        if (time() < session('force_pdo_write_until')) {
            return parent::select($query, $bindings, false);
        }
        return parent::select($query, $bindings, $useReadPdo);
    }
}

Inside of recordsHaveBeenModified(), we just add a session variable for later use. This method is used by the normal Laravel sticky session detection, as mentioned previously.

Inside of select(), we check to see if the session variable was set less than a second ago. If so, we manually force the request to use the RW connection, otherwise just continue as normal.

Now that we're directly modifying the request, I haven't seen any RO race conditions or effects from the replica lag.