Skip to main content
← PROBLEMS / ARCHITECTURE
An end-to-end domain learning journey

Solving Read-Your-Own-Writes
Replica Lag.

Your Identity & User Settings service runs on MySQL 8.0 (1 primary writer + 4 asynchronous GTID read replicas) serving 8,500 RPS. When users update their MFA phone number, billing address, or RBAC permissions via PATCH /v1/users/{id}, the frontend immediately reloads GET /v1/users/{id}. During analytics exports or schema maintenance, Seconds_Behind_Source fluctuates between 400 ms and 3.2 s, causing users to see their old MFA number and resubmit the form multiple times—or worse, experience stale RBAC permissions. Design a Read-Your-Own-Writes (RYOW) architecture that eliminates stale self-reads across all user settings.
Start the journey5 stages
Loading diagram…
Solving Read-Your-Own-Writes Replica Lag architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

Your Identity & User Settings service runs on MySQL 8.0 (1 primary writer + 4 asynchronous GTID read replicas) serving 8,500 RPS. When users update their MFA phone number, billing address, or RBAC permissions via `PATCH /v1/users/{id}`, the frontend immediately reloads `GET /v1/users/{id}`. During analytics exports or schema maintenance, `Seconds_Behind_Source` fluctuates between 400 ms and 3.2 s, causing users to see their old MFA number and resubmit the form multiple times—or worse, experience stale RBAC permissions. Design a Read-Your-Own-Writes (RYOW) architecture that eliminates stale self-reads across all user settings.

From patterns to decisions.

Your Identity & User Settings service runs on MySQL 8.0 (1 primary writer + 4 asynchronous GTID read replicas) serving 8,500 RPS. When users update their MFA phone number, billing address, or RBAC permissions via `PATCH /v1/users/{id}`, the frontend immediately reloads `GET /v1/users/{id}`. During analytics exports or schema maintenance, `Seconds_Behind_Source` fluctuates between 400 ms and 3.2 s, causing users to see their old MFA number and resubmit the form multiple times—or worse, experience stale RBAC permissions. Design a Read-Your-Own-Writes (RYOW) architecture that eliminates stale self-reads across all user settings.

01
ARCHITECTURE · STAGE 01L4 · FUNDAMENTALS

Implementation & Guardrails (1).

Seconds_Behind_Source is wall-clock-based and only measures the lag between the relay log's last fetched event and applied event; it misses un-fetched binlog events and clock skew.

ChoiceWhat it buysWhat it costs
Trade-off 1`GTID_SUBSET` is deterministic and immune to NTP clock drift.

Step 1

Inspect SHOW REPLICA STATUS for Retrieved_Gtid_Set vs Executed_Gtid_Set.

Step 2

Compare @@GLOBAL.gtid_executed on the primary against the replica using GTID_SUBSET.

Alternative 1

Use WAIT_FOR_EXECUTED_GTID_SET(gtid, 0.03) to wait up to 30 ms for catch-up.

Failure Mode 1

Passing a huge gtid_executed string with thousands of ranges; use OWN_GTID single-transaction GTIDs instead.

SELECT GTID_SUBSET(
  '3e11fa47-71ca-11e1-9e33-c80aa9429562:94821',
  @@GLOBAL.gtid_executed
) AS is_caught_up;
02
ARCHITECTURE · STAGE 02L5 · APPLIED DESIGN

Check Redis for a recent.

Check Redis for a recent user GTID; if present, try replica with a 30 ms GTID wait and fall back to primary on timeout.

ChoiceWhat it buysWhat it costs
Trade-off 130 ms max wait avoids unnecessary primary fallback for normal 10 ms lag while bounding tail latency.

Step 1

After primary COMMIT, store gtid in ryow:user:{user_id} with EX 5.

On `GET`, fetch `ryow

user:{user_id}` from Redis with a 5 ms timeout.

Step 3

If gtid exists, run SELECT WAIT_FOR_EXECUTED_GTID_SET(:gtid, 0.03) on the replica; if result != 0, use the primary session.

Alternative 1

Skip WAIT_FOR_EXECUTED_GTID_SET entirely and route directly to primary while ryow:user:{user_id} exists.

Failure Mode 1

Not closing rep_sess cleanly before returning primary_pool() leaks replica connections.

async def get_read_session(user_id: str, redis: Redis, primary_pool, replica_pool):
    gtid = await redis.get(f"ryow:user:{user_id}")
    if not gtid:
        return replica_pool()
    async with replica_pool() as rep_sess:
        res = await rep_sess.execute(
            text("SELECT WAIT_FOR_EXECUTED_GTID_SET(:g, 0.03)"), {"g": gtid}
        )
        if res.scalar() == 0:
            return rep_sess
    return primary_pool()
03
ARCHITECTURE · STAGE 03L6 · SCALE & TRADE-OFFS

RBAC revocations affect a target.

RBAC revocations affect a target user even when initiated by a different admin user, so per-writer RYOW alone is insufficient—use versioned write-through Redis invalidation.

ChoiceWhat it buysWhat it costs
Trade-off 1Global cache invalidation guarantees cross-user consistency for security-critical permissions.

Step 1

Increment users.auth_version on any role/MFA change.

Immediately update/delete `rbac

user:{target_id} in Redis post-commit and embed auth_version` in JWTs or session lookup.

Alternative 1

Route all /auth/check queries to the primary or an in-memory Redis-backed policy store.

Failure Mode 1

Deleting Redis before COMMIT lets a concurrent request repopulate Redis with the old role.

async def revoke_role(admin_id: str, target_user_id: str, db, redis):
    await db.execute(text(
        "UPDATE users SET role='VIEWER', auth_version=auth_version+1 WHERE id=:id"
    ), {"id": target_user_id})
    await db.commit()
    await redis.delete(f"rbac:user:{target_user_id}")
04
ARCHITECTURE · STAGE 04L6 · SCALE & TRADE-OFFS

Every pinned request pays an.

Every pinned request pays an extra 30 ms penalty before falling back to primary; a circuit breaker on replica lag should bypass the wait immediately.

ChoiceWhat it buysWhat it costs
Trade-off 1Eliminates the 30 ms wasted wait per request during prolonged replica stalls.

Step 1

Track replica timeout rate in a 10-second sliding window.

Step 2

When timeout rate > 20%, open the circuit for that replica and route pinned reads directly to primary (0 ms wait).

Alternative 1

Remove the lagging replica from the ProxySQL/HAProxy read pool automatically via lag health checks.

Failure Mode 1

Keeping a stalled replica in the load balancer pool continues to degrade p99 latency.

replica_circuit_breaker:
  timeout_threshold_pct: 20
  window_seconds: 10
  open_duration_seconds: 30
  fallback: primary_or_write_through_cache
05
ARCHITECTURE · STAGE 05L7 · STAFF / PRINCIPAL

OWN_GTID returns only the single.

OWN_GTID returns only the single GTID of the transaction just committed inside the MySQL protocol OK packet, eliminating an extra SQL round-trip and shrinking the token size.

ChoiceWhat it buysWhat it costs
Trade-off 1Saves 1 DB round-trip per write and keeps Redis RYOW values under 64 bytes.

Step 1

Set session_track_gtids = OWN_GTID in MySQL my.cnf or session initialization.

Step 2

Read the tracked GTID from the driver's session-state metadata after COMMIT.

Alternative 1

Use SELECT @@SESSION.gtid_next or @@GLOBAL.gtid_executed if the Python driver lacks session-tracker support.

Failure Mode 1

Parsing gtid_executed when it contains hundreds of gapped ranges inflates Redis memory and GTID_SUBSET CPU.

SET SESSION session_track_gtids = 'OWN_GTID';
-- Driver receives single GTID e.g. 3e11fa47-...:94821 in OK packet

Theory & Tips and Tricks

Practical heuristics & interview traps

  • 01Interview anchor: Combine a short Redis sticky-primary window (ryow:user:{id}) with GTID-verified reads (WAIT_FOR_EXECUTED_GTID_SET) and write-through cache invalidation.
  • 02Trade-off tip: Global cache invalidation guarantees cross-user consistency for security-critical permissions.
  • 03Watch out for: Keeping a stalled replica in the load balancer pool continues to degrade p99 latency.
Behavioral Progression · Read Your Own Writes Consistency Patterns

Read Your Own Writes Consistency Patterns — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Combine a short Redis sticky-primary window (ryow:user:{id}) with GTID-verified reads (WAITFOREXECUTEDGTIDSET) and write-through cache invalidation.

Synthesis / One connected design

Putting Solving Read-Your-Own-Writes Replica Lag Together

Combine a short Redis sticky-primary window (`ryow:user:{id}`) with GTID-verified reads (`WAIT_FOR_EXECUTED_GTID_SET`) and write-through cache invalidation.

A candidate architecture, not a universal answer

Putting Solving Read-Your-Own-Writes Replica Lag Together

Step 1
Step 2
Step 3
  1. After primary COMMIT, store gtid in ryow:user:{user_id} with EX 5.
  2. On GET, fetch ryow:user:{user_id} from Redis with a 5 ms timeout.
  3. If gtid exists, run SELECT WAIT_FOR_EXECUTED_GTID_SET(:gtid, 0.03) on the replica; if result != 0, use the primary session.
Every pinned request pays an extra 30 ms penalty before falling back to primary; a circuit breaker on replica lag should bypass the wait immediately.
Interview Readiness Check

Every pinned request pays an extra 30 ms penalty before falling back to primary; a circuit breaker on replica lag should bypass the wait immediately.

Walk through bottlenecks, back-of-the-envelope math, and failure recovery.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

Solving Read-Your-Own-Writes Replica Lag

Combine a short Redis sticky-primary window (ryow:user:{id}) with GTID-verified reads (WAIT_FOR_EXECUTED_GTID_SET) and write-through cache invalidation.