Database Internals: Isolation, MVCC, Locking, and Replication
Go beneath the SQL/PDO surface to understand how a relational database actually enforces isolation with MVCC, why deadlocks happen and how to recover from them, how composite and covering indexes change what EXPLAIN reports, and how connection pooling, read replicas, and consistency models shape a database's behavior under real production load.
The earlier SQL and PDO lessons taught you how to write correct queries, wrap multi-statement writes in transactions, and add basic indexes. This lesson is different in kind: it's a theory-heavy tour of what the database engine is actually doing underneath those statements — how it gives every transaction the illusion of exclusive access to the data via MVCC, what a deadlock really is and how the engine detects one, how a composite or covering index changes the physical work behind a query plan, and what happens once a single database server isn't enough and you're running a primary with one or more read replicas behind it. Several of these topics were touched on briefly in earlier lessons (isolation levels, locking, N+1 queries, migrations); this lesson cross-references those instead of repeating them, and spends its time on the mechanics underneath.
ACID and Isolation Levels, Revisited
As a quick recap: ACID stands for Atomicity (a transaction's writes all happen or none do), Consistency (a transaction moves the database from one valid state to another, respecting constraints), Isolation (concurrent transactions don't see each other's uncommitted work), and Durability (once committed, a write survives a crash). The earlier Transactions and ACID lesson covers all four in depth along with the three classic read anomalies — dirty reads, non-repeatable reads, and phantom reads. This section goes one level deeper into the *I* in ACID: what isolation levels actually cost, and how databases implement them without simply locking every row a transaction touches.
The SQL standard defines four isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE — as a strictly increasing ladder of guarantees, each preventing one more anomaly than the last, at the cost of more contention and lower concurrency. Where databases diverge from the standard (and from each other) is in exactly *how* they deliver those guarantees, and that's where MVCC comes in.
It's worth internalizing that 'stronger isolation' is not free: a database running everything at SERIALIZABLE gives you the strongest correctness guarantee (transactions behave as if they ran one at a time, in some order) but pays for it in reduced concurrency — more transactions block each other or get rolled back and retried under contention. Most production systems run at READ COMMITTED or REPEATABLE READ by default and reach for SERIALIZABLE only for specific operations that truly need it.
• ACID recap: Atomicity, Consistency, Isolation, Durability — full detail in the Transactions and ACID lesson. • Isolation levels form a ladder: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE. • Each step up prevents one more anomaly (dirty read → non-repeatable read → phantom read) at the cost of concurrency. • Different databases implement the same isolation-level *name* with different actual guarantees underneath — the name is a contract, not an implementation.
MVCC: How Isolation Actually Works Under the Hood
Multi-Version Concurrency Control (MVCC) is the mechanism most modern relational databases (PostgreSQL, MySQL/InnoDB, Oracle) use to deliver isolation without making every reader wait for every writer. Instead of a reader locking a row to read it, the database keeps multiple *versions* of each row — the version as it existed before an in-flight transaction's changes, and the version as it will exist after. A reader is handed the version that was current as of some reference point (the moment the reader's transaction began, or the moment its statement began, depending on isolation level and engine), while writers create new versions rather than overwriting in place. The practical effect: readers never block writers, and writers never block readers — only writer-vs-writer conflicts on the same row need real locking.
This is what makes REPEATABLE READ possible without holding a read lock on every row you've ever touched in the transaction: the transaction is handed a consistent *snapshot* of the database as of a fixed point in time, and it keeps reading from that same snapshot for its whole duration, regardless of what other transactions commit in the meantime. That's why re-reading the same row twice in one REPEATABLE READ transaction returns the same value even if another transaction committed a change to it in between — your transaction is still looking at its own frozen snapshot, not the live table.
Old row versions can't be deleted immediately, since some other in-flight transaction might still need them for its snapshot — the database has to garbage-collect them once no transaction can possibly need them anymore. In PostgreSQL this is the job of VACUUM; in MySQL/InnoDB it's the purge thread cleaning up the undo log. A long-running transaction that never commits is a classic operational hazard specifically *because* of MVCC: it forces the database to retain every row version created since that transaction's snapshot began, which can bloat storage and slow down maintenance work until the transaction finally ends.
• MVCC: the database keeps multiple versions of a row instead of locking it for readers. • A transaction reads from a consistent snapshot fixed at some point in time — not the live, constantly-changing table. • Readers don't block writers, and writers don't block readers, under MVCC — only concurrent writes to the same row need locks. • Old row versions are garbage-collected once no transaction's snapshot can still need them (VACUUM in PostgreSQL, purge in InnoDB). • A long-idle open transaction can bloat storage by forcing the database to retain otherwise-obsolete row versions.
Deadlocks: Causes and Resolution
A deadlock happens when two (or more) transactions each hold a lock the other one needs, and each is waiting for the other to release it — transaction A holds a lock on row 1 and wants row 2, while transaction B holds a lock on row 2 and wants row 1. Neither can proceed, and neither will ever release what it's holding until it either gets what it's waiting for or gives up, so without intervention both would wait forever. Deadlocks are almost always a consequence of two pieces of code acquiring the same set of row locks in a different order — the single most effective prevention is to always lock rows in a consistent order (e.g. always by ascending primary key) across every code path that touches more than one row.
Databases don't actually let this go on forever: they run a deadlock detector (InnoDB checks its wait-for graph continuously; PostgreSQL checks periodically on a timeout) that recognizes the circular wait and picks one transaction to abort — typically the one that has done the least work, or the one that acquired its locks most recently — rolling it back and returning an error to that transaction's connection (SQLSTATE 40001 on MySQL/MariaDB, 40P01 specifically on PostgreSQL) while letting the other proceed. The aborted transaction's application code is expected to catch that specific error and retry the whole transaction from the beginning.
A deadlock is different from a plain lock wait timeout: a lock wait is one transaction patiently waiting its turn for a lock another transaction will eventually release, and it resolves on its own once the holder commits or rolls back. A deadlock is a *genuine* impasse that can never resolve on its own — the database has to actively break it by force.
• Deadlock: transaction A waits on a lock B holds, while B waits on a lock A holds — a circular wait that can't resolve itself. • Most common cause: two code paths locking the same rows in different orders under concurrent load. • Prevention: always acquire multi-row locks in a fixed, consistent order (e.g. ascending primary key). • The database's deadlock detector picks a victim, rolls it back, and returns SQLSTATE 40001 (MySQL/MariaDB) or 40P01 (PostgreSQL). • Application code must catch that specific error and retry the whole transaction — deadlocks are expected and recoverable, not fatal bugs.
Locking Strategies and N+1, Briefly Revisited
Two locking strategies come up constantly once you're past basic transactions: optimistic locking (add a version column, condition the UPDATE on the version you read, and detect a lost update via a zero-row rowCount()) and pessimistic locking (SELECT ... FOR UPDATE to take a real row lock up front and make other transactions wait). Both are covered in full, worked-example depth in the Concurrency and Locking lesson — the short version is: reach for optimistic locking when conflicts are rare, and pessimistic locking when conflicts are common enough that retries would just thrash. This lesson's deadlock discussion above is really about what happens when pessimistic locking is used carelessly across multiple rows in inconsistent orders.
Similarly, the N+1 query problem — one query for a list of N rows, followed by N more queries fetching related data one row at a time inside a loop — is covered with its eager-loading fix in the Query Performance lesson. It's worth remembering here because it interacts with everything else in this lesson: N+1 queries mean N+1 separate round trips each acquiring and releasing their own tiny bit of MVCC snapshot/lock state, which multiplies connection and lock overhead exactly where a single well-indexed batched query would touch the lock manager once.
• Optimistic locking: version column + conditional UPDATE + check rowCount() — see the Concurrency and Locking lesson for full examples. • Pessimistic locking: SELECT ... FOR UPDATE inside a transaction — same lesson for the worked stock-decrement scenario. • N+1 queries: one query for a list, then N more in a loop — see the Query Performance lesson for the eager-loading fix. • This lesson goes deeper on what pessimistic locking and repeated round trips do to the lock manager and connection pool under real load.
Composite Indexes, Covering Indexes, and Reading EXPLAIN in Depth
The Database Indexes lesson introduces composite indexes and the leftmost-prefix rule. A covering index takes that one step further: it's an index that includes *every* column a query needs — both the columns in the WHERE/ORDER BY clause and the columns in the SELECT list — so the database can satisfy the entire query by reading the index alone, without ever touching the underlying table's row data. This is sometimes called an index-only scan. Since an index is typically much smaller than the full table (fewer columns, often already sorted), an index-only scan can be dramatically faster than the same query hitting the table for every matching row.
Reading an EXPLAIN plan in depth means looking past just 'is there a key value' and into several columns together: type tells you the join/access strategy for that table — const/eq_ref (near-instant, one row) and ref/range (an index narrows the search) are healthy, while ALL means a full table scan; rows is the engine's *estimate* of how many rows it will examine, which is what query cost is actually based on, not how many rows are ultimately returned; and the Extra column carries some of the most actionable information of all — Using index means a covering index satisfied the query with no table lookup, Using filesort means the engine had to sort results outside of index order (often fixable by adding the sort column to the index), and Using temporary means it built a temporary table mid-query (common with certain GROUP BY/DISTINCT patterns), both of which are red flags worth investigating on a hot query path.
It's easy to stop at 'there's a key, so it's using an index' and miss that the index being used isn't actually a good fit — a type of range scanning 400,000 estimated rows because the index only narrows things down a little is still a slow query, even though technically 'an index is being used.' Reading EXPLAIN well means comparing the estimated rows against how selective you'd expect the condition to actually be, not just checking that the key column isn't NULL.
• Covering index: includes every column the query needs (filter, sort, and SELECT columns) — enables an index-only scan. • EXPLAIN type: const/eq_ref/ref/range are healthy; ALL means a full table scan. • EXPLAIN rows: the engine's cost estimate of rows examined — compare it to how selective the condition should really be. • EXPLAIN Extra: 'Using index' = index-only scan (great); 'Using filesort' / 'Using temporary' = extra work worth investigating. • A non-NULL key column isn't proof of a fast query — a poorly-selective index can still scan huge row counts.
Connection Pooling, Read Replicas, and Replication Lag
Opening a new database connection isn't free — it means a TCP handshake, authentication, and (for TLS connections) a full handshake on top of that, all before the first query even runs. Connection pooling avoids paying that cost on every request by keeping a set of already-open connections around and handing one out to whichever request needs it, returning it to the pool when the request is done instead of closing it. PHP's traditional per-request lifecycle (a fresh process or worker per request, with no state carried between requests) means true application-level pooling usually happens outside PHP itself — a dedicated pooler process like PgBouncer (PostgreSQL) or ProxySQL (MySQL) sits between PHP and the database, maintaining the actual long-lived connections, while PHP connects to the local pooler quickly on every request. PDO's own PDO::ATTR_PERSISTENT option asks PHP to keep a connection alive and reuse it across requests handled by the *same* PHP worker process, which is a narrower, PHP-specific form of the same idea and needs care (state accidentally left on a persistent connection — like an open transaction — can leak into the next unrelated request that reuses it).
A read replica is a copy of the primary database that continuously applies the primary's write stream and serves read-only queries, letting you scale reads horizontally by adding more replicas without adding write capacity (there's still only one primary accepting writes). Replication is asynchronous in most common setups, which means there's always some replication lag — a small delay between a write committing on the primary and that same write becoming visible on a replica. Under normal conditions this lag is milliseconds, but it can grow under heavy write load or replica resource pressure, and an application that blindly reads from a replica right after writing to the primary can see stale (or even momentarily 'missing') data.
The practical rule of thumb: route writes and any read that must reflect the request's own just-written data to the primary, and route everything else — dashboards, listings, anything that can tolerate being a few hundred milliseconds behind — to a replica. Some setups go further and expose a replication-lag metric so the application (or the pooler) can route around a replica that's fallen too far behind, rather than serving obviously stale data.
• Connection setup (handshake, auth, TLS) has real cost — pooling avoids paying it on every request. • External poolers (PgBouncer, ProxySQL) sit between the app and the database and are the common approach given PHP's per-request lifecycle. • PDO::ATTR_PERSISTENT reuses a connection across requests on the same PHP worker — convenient, but leaked state can bleed into the next request. • Read replicas scale reads horizontally; there's still exactly one primary that accepts writes. • Replication lag: the delay before a primary's write is visible on a replica — usually small, but never guaranteed to be zero. • Route writes and 'must see my own write' reads to the primary; route everything tolerant of slight staleness to a replica.
Migrations and Consistency Models, Briefly
Schema versioning through migrations — numbered, ordered scripts that evolve the schema forward (and, ideally, back) in a reproducible, team-shared way — is covered fully in the Migrations and Schema Evolution lesson. The one detail worth adding here: a migration that adds a NOT NULL column or a new index to a large, busy table interacts directly with everything above in this lesson — it can hold locks that block concurrent writers for the duration, and on a replicated setup it also has to make it through the replication stream, meaning a slow migration on the primary is also a slow, lock-holding event replaying on every replica behind it.
Finally, a consistency model is the precise guarantee a distributed or replicated system gives you about what a read can see. Strong consistency means every read sees the most recent committed write, everywhere, immediately — which a single-primary database gives you for reads against the primary itself. Eventual consistency means reads may return stale data for a while, but the system guarantees that if no new writes occur, every replica eventually converges to the same value — this is exactly the guarantee a read replica gives you, with 'eventually' bounded by however much replication lag exists at that moment. Understanding which consistency model a given read path actually needs — and which one it's actually getting — is the difference between a replica-read that's a harmless performance optimization and one that's a subtle correctness bug.
• Migrations recap: ordered, versioned schema-change scripts — full treatment in the Migrations and Schema Evolution lesson. • A migration on a large table can hold locks and adds load to the replication stream — replicas replay it too. • Strong consistency: every read sees the latest committed write immediately (true for reads against the primary). • Eventual consistency: reads may be briefly stale but converge once writes stop — the guarantee a read replica actually gives you. • Pick the consistency model per read path deliberately, don't assume every read needs the strongest one available.
Setting an Isolation Level and Retrying on Deadlock
The isolation level is set per session/connection before the transaction begins — it applies to the next transaction that connection starts. SQLSTATE 40001 covers MySQL/MariaDB deadlocks and PostgreSQL serialization failures; PostgreSQL also uses 40P01 specifically for deadlock_detected. Any deadlock retry loop must cap its attempts — an unbounded retry against a persistently contended resource just becomes a slow failure instead of a fast one.
<?php
declare(strict_types=1);
$pdo->exec('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
$attempt = 0;
while (true) {
$attempt++;
$pdo->beginTransaction();
try {
// ... statements that must succeed or fail together ...
$pdo->commit();
break;
} catch (PDOException $e) {
$pdo->rollBack();
$sqlState = $e->errorInfo[0] ?? null;
$isDeadlock = $sqlState === '40001' || $sqlState === '40P01';
if (!$isDeadlock || $attempt >= 3) {
throw $e;
}
}
}Code Examples
Under REPEATABLE READ, MVCC hands this transaction a consistent snapshot the moment it starts reading, and every read within the transaction keeps seeing that same snapshot regardless of what other transactions commit in the meantime -- which is why $first and $second are guaranteed equal even though the row genuinely changed on disk between the two SELECTs. Expected result: bool(true)
$pdo->exec('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
Iterating the EXPLAIN result set the same way you would any other query lets you inspect type, key, rows, and Extra together -- a range scan on idx_status_created narrowing to a few hundred rows before an eq_ref join to customers is healthy; a NULL key with type=ALL and rows in the hundreds of thousands is the full-table-scan red flag to chase down. Expected result: table=o type=range key=idx_status_created rows=842 Extra=Using where table=c type=eq_ref key=PRIMARY rows=1 Extra=NULL
$row['type'], $row['key'], $row['rows'], $row['Extra'] Every column this query touches -- status and created_at (filter/sort), plus customer_id and total (the SELECT list) -- is present in idx_orders_covering, in an order that also satisfies the leftmost-prefix rule for the WHERE clause. EXPLAIN on this query reports 'Using index' in Extra: the engine never has to fetch the underlying orders row at all. Covering indexes trade write cost (one more index to maintain on every INSERT/UPDATE) for read speed -- worth it on hot, read-heavy queries; wasteful bloat on rarely-run ones.
CREATE INDEX idx_orders_covering ON orders (status, created_at, customer_id, total);
The two UPDATEs lock accounts.fromId then accounts.toId, in that order -- if a concurrent transfer between the same two accounts happens to run in the opposite order, the database's deadlock detector will eventually pick one transaction as the victim and abort it with SQLSTATE 40001/40P01. This function catches specifically that error, backs off briefly, and retries the whole transaction from scratch, up to a bounded number of attempts.
$isDeadlock = $sqlState === '40001' || $sqlState === '40P01';
Common mistake: Locking fromId and toId in a fixed order (e.g. always the smaller account id first) across every code path would prevent this particular deadlock outright -- retrying is the fallback for when that isn't practical, not a substitute for it.
<?php
declare(strict_types=1);
$pdo->exec('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
$pdo->beginTransaction();
$first = $pdo->query('SELECT balance_cents FROM accounts WHERE id = 7')->fetchColumn();
// ...meanwhile, on a different connection, another transaction commits
// an UPDATE that changes account 7's balance...
$second = $pdo->query('SELECT balance_cents FROM accounts WHERE id = 7')->fetchColumn();
var_dump($first === $second); // bool(true) -- still the same MVCC snapshot
$pdo->commit();<?php
declare(strict_types=1);
$stmt = $pdo->query(
"EXPLAIN SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC"
);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
printf(
"table=%-10s type=%-7s key=%-24s rows=%-8s Extra=%s" . PHP_EOL,
$row['table'],
$row['type'],
$row['key'] ?? 'NULL',
$row['rows'],
$row['Extra']
);
}<?php
declare(strict_types=1);
// CREATE INDEX idx_orders_covering
// ON orders (status, created_at, customer_id, total);
$stmt = $pdo->prepare(
'SELECT customer_id, total FROM orders
WHERE status = :status AND created_at > :since
ORDER BY created_at DESC'
);
$stmt->execute(['status' => 'pending', 'since' => '2026-01-01']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);<?php
declare(strict_types=1);
function transferWithDeadlockRetry(
PDO $pdo,
int $fromId,
int $toId,
int $amountCents,
int $maxAttempts = 3
): void {
$attempt = 0;
while (true) {
$attempt++;
$pdo->beginTransaction();
try {
$debit = $pdo->prepare(
'UPDATE accounts SET balance_cents = balance_cents - :amt WHERE id = :id'
);
$debit->execute(['amt' => $amountCents, 'id' => $fromId]);
$credit = $pdo->prepare(
'UPDATE accounts SET balance_cents = balance_cents + :amt WHERE id = :id'
);
$credit->execute(['amt' => $amountCents, 'id' => $toId]);
$pdo->commit();
return;
} catch (PDOException $e) {
$pdo->rollBack();
$sqlState = $e->errorInfo[0] ?? null;
$isDeadlock = $sqlState === '40001' || $sqlState === '40P01';
if (!$isDeadlock || $attempt >= $maxAttempts) {
throw $e;
}
usleep(random_int(10_000, 50_000) * $attempt);
}
}
}Isolation Levels vs. the Three Read Anomalies
The standard ladder of guarantees -- but always verify what your specific database actually delivers at each level.
Isolation Level Dirty Read Non-Repeatable Read Phantom Read
READ UNCOMMITTED Yes Yes Yes
READ COMMITTED No Yes Yes
REPEATABLE READ No No Yes *
SERIALIZABLE No No No
* InnoDB's REPEATABLE READ additionally blocks most phantoms via
MVCC snapshots + next-key locking -- stronger than the SQL
standard's minimum guarantee for this level, but engine-specific.Important Rules
MVCC lets readers avoid blocking writers (and vice versa) by handing each transaction a consistent snapshot instead of a lock.
A long-idle open transaction forces the database to retain old row versions it would otherwise garbage-collect -- close transactions promptly.
Always acquire multi-row locks in a fixed, consistent order across every code path to prevent most deadlocks outright.
Catch SQLSTATE 40001/40P01 specifically and retry with a bounded attempt count -- don't treat a deadlock as a generic fatal error.
A covering index (every column the query needs, in a leftmost-prefix-compatible order) enables an index-only scan -- look for 'Using index' in EXPLAIN's Extra column.
Route writes and 'must see my own write' reads to the primary; route replica-tolerant reads to a read replica.
Pick the consistency model (strong vs. eventual) deliberately per read path -- don't assume every read needs the strongest one available.
Where This Shows Up
Managed database services (Amazon Aurora, Google Cloud SQL, Azure Database) expose read replicas and replication-lag metrics as first-class features precisely because this tradeoff is universal at scale. Connection poolers like PgBouncer and ProxySQL are standard infrastructure in any PHP deployment handling meaningful traffic, and 'why is this query slow despite having an index' investigations almost always come down to reading EXPLAIN's type/rows/Extra columns together rather than just checking whether a key was used at all.
catch (PDOException $e) { $pdo->rollBack(); throw $e; } // treats every DB error, including a recoverable deadlock, as fatal catch (PDOException $e) { $pdo->rollBack(); if (($e->errorInfo[0] ?? null) === '40001' && $attempt < $maxAttempts) { /* retry */ } else { throw $e; } }
A deadlock is an expected, recoverable event under concurrent load, not a bug -- the database is explicitly asking one of the two conflicting transactions to retry. Treating it identically to a genuine fatal error (a constraint violation, a syntax error) means transient contention surfaces as a hard failure to the user instead of a transparent, automatic retry.
$stmt = $pdoReplica->query('SELECT * FROM comments WHERE post_id = ?'); // immediately after INSERT-ing the user's own new comment via $pdoPrimary
Route the read that must include the just-written row to $pdoPrimary (or the same connection the write used), and reserve the replica for reads that can tolerate a small lag.
Replication is asynchronous, so a read against a replica immediately after a write to the primary can miss that write entirely -- the user submits a comment and then doesn't see it on the very next page load. Reads that need to reflect the current request's own writes belong on the primary.
Under MVCC, why can two transactions read and write the same table concurrently without readers blocking writers? A. Readers acquire a shared lock that writers can still bypass B. The database keeps multiple versions of each row, and a reader is handed a consistent snapshot instead of taking a lock C. MVCC disables isolation entirely for performance D. Readers are always routed to a read replica automatically
B. The database keeps multiple versions of each row, and a reader is handed a consistent snapshot instead of taking a lock — MVCC's core idea is versioning rows instead of locking them for reads -- a transaction reads from a snapshot fixed at some point in time, so it never needs to wait for (or block) a concurrent writer creating a newer version.
What is the most effective way to prevent deadlocks between two code paths that each update the same two rows? A. Always wrap both updates in SERIALIZABLE transactions B. Catch the deadlock error and never retry, to fail fast C. Always acquire locks on the two rows in the same, fixed order across every code path D. Avoid transactions entirely for multi-row updates
C. Always acquire locks on the two rows in the same, fixed order across every code path — A deadlock requires a circular wait, which requires the two transactions to have acquired their first locks in opposite orders. Locking rows in a consistent, agreed-upon order (e.g. always by ascending id) removes the possibility of that circular wait.
In an EXPLAIN plan, what does 'Using index' in the Extra column specifically indicate? A. The query used no index at all B. The query performed a full table scan despite having an index available C. Every column the query needs was found in the index itself, so the engine never touched the underlying table row (an index-only / covering-index scan) D. The index needs to be rebuilt
C. Every column the query needs was found in the index itself, so the engine never touched the underlying table row (an index-only / covering-index scan) — 'Using index' means the index alone satisfied the query -- every referenced column (filter, sort, and SELECT list) was present in the index, so no table lookup was needed at all. This is the signature of a covering index doing its job.
A read immediately following a write to the primary returns data that doesn't include that write. What is the most likely cause? A. The write was never committed B. The read was served by a read replica that hasn't yet applied the write due to replication lag C. MVCC prevented the write from being visible to any transaction D. The isolation level was set too low
B. The read was served by a read replica that hasn't yet applied the write due to replication lag — Asynchronous replication means a replica can lag behind the primary by some amount, however small. A read routed to a lagging replica right after a write to the primary can genuinely miss that write -- the fix is routing 'must see my own write' reads to the primary.
You have a products table and run SELECT id, name FROM products WHERE category_id = ? AND in_stock = 1 ORDER BY name on a hot page. Design a single composite index that lets EXPLAIN report 'Using index' for this exact query, and explain why the column order you chose matters.
CREATE INDEX idx_products_covering ON products (category_id, in_stock, name, id); -- category_id and in_stock are equality filters and come first (leftmost-prefix), name matches the ORDER BY so the index is already in the right order for it, and id/name being present means the SELECT list is fully covered without touching the table.
Every column referenced anywhere in the query -- filter, sort, and SELECT list -- needs to be in the index for an index-only scan; equality-filtered columns should come before the sorted column.