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.
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.
| Choice | What it buys | What 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;
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | 30 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()
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Global 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}")
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Eliminates 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
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Saves 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.
Read Your Own Writes Consistency Patterns — Normal → Load / Fault → Recovery
Combine a short Redis sticky-primary window (ryow:user:{id}) with GTID-verified reads (WAITFOREXECUTEDGTIDSET) and write-through cache invalidation.
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.
Putting Solving Read-Your-Own-Writes Replica Lag Together
- After primary
COMMIT, storegtidinryow:user:{user_id}withEX 5. - On
GET, fetchryow:user:{user_id}from Redis with a 5 ms timeout. - If
gtidexists, runSELECT WAIT_FOR_EXECUTED_GTID_SET(:gtid, 0.03)on the replica; if result != 0, use the primary session.
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.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
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.
