CODEWITHSUNDEEP
phpmysqllaravellaravel-5
lesssugar
lesssugar

Reputation: 16191

Laravel read and write connection not in sync

I'm using read and write MySQL connection setup in my Laravel 5.2 app:

'mysql' => [
    'write' => ['host' => env('DB_HOST_WRITE', 'localhost'),],
    'read'  => ['host' => env('DB_HOST_READ', 'localhost'),],
    'driver' => 'mysql',
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null
]

The website runs on multiple servers, using a load balancer.

Now, in the app, there are cases of read and write operations done one after another, e.g.

  1. insert new records into the database
  2. select some of the newly inserted records

With the current connection setup, it can happen that the select will return nothing, even if the records were inserted correctly.

What can be the reasons for this?

Upvotes: 2

Views: 3873

Answers (2)

Laurence
Laurence

Reputation: 60058

In Laravel 5.5 I've done a PR for this that introduces a "sticky" option.

It means your application will use the "read" connection whenever possible, but if you do a "write", then any subsequent "reads" on the same request cycle will also come from the write connection.

This ensures data integrity.

https://github.com/laravel/framework/pull/20445

Upvotes: 4

Paras
Paras

Reputation: 9465

This is not a Laravel specific problem. This is called master slave replication lag.

There are multiple prevention techniques but only one technique that "cures" the problem

First the Cures: Avoid read write combos:

Don't perform a select operation immediately after an insert (read-write combo). This might sound crazy at first, but if you can avoid it, avoid it!

If you cant do this but you can identify where the read-write combos would happen in your code, just use the master for both read and write ops

Prevention Techniques (if cure is not possible)

  1. Enable slave compressed protocol
  2. Disable binary logging on slave
  3. Optimize mysql queries (e.g. add indexes, etc.)
  4. Other ways: manual sleep between insert and select queries, wrapping both inserts and selects in a single transaction (again would be done using the master connection while slave is free)

Upvotes: 2

Related Questions