In Laravel's config/database.php, how do read and write hosts and the sticky option route queries, and why might a member not see points just transferred?
answer
- read and write arrays override base
- selects go to the read PDO
- open transaction uses the writer
- sticky: after a write this request
- useWritePdo and onWriteConnection
basics
~20 sSelects use a read host and writes use the write host. Without sticky, a read after a write in the same request can hit a lagging replica and miss the new points; reads inside a transaction always use the writer.
solid answer
~40 sA connection gains `read` and `write` sub-arrays (usually just `host`), and everything else is merged from the main connection config. Laravel then keeps two PDO handles: `select` queries use the **read** PDO, and inserts, updates, deletes and statements use the **write** PDO. When `host` lists several servers, they are shuffled and tried in turn until one connects. Reads switch to the write PDO in three cases: an **open transaction**, **`sticky => true`** after this connection has modified records, or an explicit `useWritePdo()` (which `lockForUpdate()` calls, and which `Model::onWriteConnection()` uses). Without `sticky`, a controller that transfers points and then redirects to a page reading the balance may read from a replica that has not replicated yet, although a redirect is a new request, so sticky helps only within one request.
code
php · 13 lines<?php
use App\Models\Member;
use Illuminate\Support\Facades\DB;
// Always read the fresh balance from the writer
$balance = DB::table('members')
->where('id', $memberId)
->useWritePdo()
->value('points');
// Eloquent equivalent
$member = Member::onWriteConnection()->find($memberId);go deeper
Recall that read and write hosts split selects from writes and that sticky sends reads to the writer after a write.
Explain the routing table: transactions, sticky after modification, useWritePdo, and how multiple hosts are shuffled and retried.
Diagnose stale reads after writes, knowing sticky ends with the request, and force writer reads where freshness is required.
Decide how much read traffic to push to replicas versus the freshness guarantees each page needs.
## The configuration Read/write splitting is configured per connection in `config/database.php`: ```php 'mysql' => [ 'driver' => 'mysql', 'read' => [ 'host' => ['10.0.0.11', '10.0.0.12'], ], 'write' => [ 'host' => ['10.0.0.10'], ], 'sticky' => true, 'database' => env('DB_DATABASE', 'laravel'), 'username' => env('DB_USERNAME', 'root'), 'password' => env('DB_PASSWORD', ''), // ... ], ``` Three keys are involved: - **`read`** and **`write`** override values from the main array for each side. Credentials, database name, charset and prefix are shared unless you override them. - **`sticky`** is optional and makes reads use the write host after this connection has written. When a `host` list has several entries, the connector shuffles them and tries each until one accepts the connection, so a dead replica is skipped rather than failing the request. The read PDO is created lazily, on the first read. ## How each query is routed | Situation | PDO used | |---|---| | `select` with no special conditions | read | | `insert`, `update`, `delete`, `statement` | write | | any `select` while a transaction is open | write | | `select` after this connection modified rows, with `sticky => true` | write | | builder with `useWritePdo()`, including `lockForUpdate()` | write | | Eloquent query started with `Model::onWriteConnection()` | write | The "modified rows" flag is set by an insert or any statement, and by an update or delete only when it affected at least one row. ## Why the member does not see the points Replication from the writer to a replica is asynchronous, so a replica can lag by some milliseconds or more. Consider two flows: 1. **Same request.** The controller transfers points, then reads the receiver's balance to show it. Without `sticky`, that read goes to a replica and may return the old balance. With `sticky => true`, it goes to the writer and sees the new value. 2. **Next request.** The controller redirects, and the balance page loads in a fresh request. Under PHP-FPM that is a new process state, so the sticky flag starts false again and the read uses a replica. `sticky` does not help here; you need a different approach, such as reading explicitly from the writer for that page, or showing the value the write already knew. Reads inside a `DB::transaction()` closure are always safe, because an open transaction routes every select to the writer. ## Long-running processes The sticky flag lives on the connection object. In a queue worker or under Octane that object outlives a single request or job. Octane registers a listener that resets the modification state between requests; in other long-lived processes, once a connection has written, its reads can keep going to the writer. `DB::forgetRecordModificationState()` clears it when you need to. ## Verifying the routing `DB::listen()` receives a `QueryExecuted` event whose `readWriteType` property is `read`, `write` or `direct`. Logging it in a local or staging environment shows which host each query used, which is quicker than guessing from replica lag. ## Choosing whether to enable `sticky` - **Turn it on** when requests commonly write and then read back the same data, such as forms that redisplay the saved record. - **Leave it off** when you want the replica to absorb as many reads as possible and your pages tolerate lag. - **Either way**, use `onWriteConnection()` or `useWritePdo()` for the specific reads that must never be stale.
- Does sticky => true make the balance page after a redirect read from the writer?No. The sticky flag is per connection object and, under PHP-FPM, each request starts with a fresh one whose flag is false. The redirect's GET request reads from a replica. For that page, read explicitly with `useWritePdo()` or `onWriteConnection()`, or pass the new balance through the session flash data.
- How can you check whether a query went to a replica or the primary?Register `DB::listen()` and log the `QueryExecuted` event's `readWriteType` together with `sql` and `connectionName`. Each query then shows `read` or `write`, making sticky behaviour and transaction routing visible without inspecting the database servers.
saying these in an interview costs you the question
- Believes sticky keeps reading from the writer across later requests
- Thinks reads inside a transaction can still go to a replica
- Expects the read and write arrays to need full credentials each
- Says any update, even affecting zero rows, makes reads sticky
- Assumes Laravel waits for replicas to catch up before reading