MySQL read/write splitting is not complete when every statement beginning with SELECT is sent to a replica. The engineering problem is deciding which reads may tolerate lag, which reads become authoritative inputs to later actions, and how the application returns to the primary when routing or replica access fails. A design that optimizes distribution without defining consistency boundaries can publish stale cache entries, make decisions from old state, or silently break locking semantics.

This guide focuses on correctness for MySQL read/write splitting: conservative defaults, an explicit off switch, allow-listed read routing, primary-only authoritative reads after writes, and observable fallback. It uses a small .NET 10 model with synthetic SQL text. The reproduction does not connect to MySQL, does not use a real application configuration, and does not modify data.

What is the core consistency boundary in MySQL read/write splitting?

Treat the primary as the authoritative source and a replica as an optional source for lag-tolerant reads. Writes, locking reads, state decisions, reversals, and authoritative reads immediately after a write should remain explicitly bound to the primary.

MySQL documents traditional source-to-replica replication as asynchronous by default. Even semisynchronous replication does not mean that a transaction has been applied on the replica when the source receives acknowledgement. The acknowledgement can establish receipt of replication events without establishing query visibility on the replica. Application code must therefore separate “the write succeeded on the primary” from “a replica can already return the new value.”

This distinction matters first for control-flow data. Sign-in state, one-time validation records, authorization decisions, quotas, state transitions, and queries that determine a following write are not ordinary reporting reads. A stale result may lead the application to choose an action that contradicts the primary. The SQL may still be syntactically read-only, but its business effect is authoritative.

The same rule applies to cache and event publication. A common sequence writes to the primary, reloads an object, writes that object to a cache, and then publishes an event. If the reload uses a lagging replica, an older object can be labeled as the newest snapshot and distributed to other components. The incident is no longer a briefly stale screen. Stale data has crossed a publication boundary and may remain visible after the replica catches up.

Locking semantics are another hard boundary. SELECT ... FOR UPDATE starts with a read verb, yet InnoDB classifies it as a locking read. Its purpose is to protect rows for a related update in the same transaction. Routing it to a replica separates the lock from the writable state that the caller intends to protect. A classifier must inspect meaningful phrases, not only the first token.

The practical question is not simply, “Does this statement write?” Ask, “What will the caller do with this result?” If the result authorizes an operation, confirms a state change, seeds a cache, advances a workflow, or protects a subsequent update, the safer default is the primary.

How should a fail-safe SQL router classify reads?

Use an allow list instead of optimistic guessing. Route to a replica only when splitting is enabled, the original connection is the recognized primary, the statement begins with an approved read verb, and the full statement contains no write, lock, or procedure-call signal.

Model limitation: the regular expression below is a teaching model, not a production SQL safety classifier. A leading-verb allow list plus rejected keywords does not prove that arbitrary SQL is side-effect-free. FOR SHARE, multiple statements, function calls, session state, and transaction scope need additional handling. Production routing should rely on reviewed query identities or fixed templates and explicit consistency requirements, with a MySQL-aware parser where necessary. Unknown cases remain on the original connection, and commands within a transaction must not be moved to another connection. The model does not implement those production controls.

The first control is a master switch. When Enabled=false, behavior should be identical to the original single-primary path. This enables a zero-behavior-change deployment and provides a predictable operational fallback. If some helpers still choose a replica after the switch is disabled, the switch is not a reliable control.

The second control is source identity. Only a connection that originally targets the configured primary should be eligible for rewriting. Connections for another database, a maintenance store, or a specialized workload should remain untouched. Broad string replacement is risky because unrelated connection definitions can share text fragments. Prefer structured configuration and an explicit identity check.

The third control is statement classification. Reasonable leading verbs can include SELECT, SHOW, DESC, DESCRIBE, and EXPLAIN, but the router should still reject signals such as INSERT, UPDATE, DELETE, REPLACE, CALL, LOCK, UNLOCK, LAST_INSERT_ID, and FOR UPDATE. Matching must use word or phrase boundaries. A naive search for UPDATE can reject a harmless column named updatetime, while a first-token-only rule can incorrectly route SELECT ... FOR UPDATE.

Stored procedures deserve a separate policy. A procedure name rarely proves that the body is read-only. The procedure may contain writes, transaction controls, dynamic SQL, or locks. The conservative default is primary-only. Add a procedure to a read allow list only after reviewing its definition and running an isolated test. Losing some replica traffic is cheaper than sending an unknown side effect to the wrong server.

Cancellation and retry behavior must also be classified. A canceled operation is not proof of replica failure and should preserve cancellation semantics. A clearly retryable replica connection or read failure may justify one primary retry inside an explicit scope. Retrying every exception can swallow shutdown signals, user cancellation, and timeout policy, doubling work while hiding the original cause.

Finally, keep the router deterministic. Given the same SQL text, feature state, and source identity, it should produce the same target without network calls. Determinism makes the highest-risk boundaries easy to unit test before any database integration test is attempted.

Why are read_only and super_read_only not complete safety proofs?

read_only and super_read_only are valuable MySQL controls, but they do not independently prove topology, freshness, or correct application routing. Verify the server role, effective restrictions, replication state, and application behavior as separate concerns.

The MySQL reference explains that enabling super_read_only also enables read_only, while disabling read_only disables super_read_only. It also documents privileged exceptions to general read-only behavior. An application should not infer that every write must fail merely because a host is described as a replica, and it should not infer that current data is available merely because a read-only variable is enabled.

A useful acceptance model asks four different questions. Is the server actually configured as a replica? Is it receiving replication events? Has it applied the transaction required by this request? Can the application identity perform a write despite the intended restrictions? These questions can have different answers. Compressing them into one Boolean produces fragile deployment checks.

This is also why a shared development database should not be used for a destructive “prove that writes fail” experiment. If its configuration differs from the assumption, the test write can succeed and participate in replication. The reproduction in this article deliberately tests only a pure routing function. It sends no SQL to a server. A real topology test belongs in a disposable sandbox with synthetic records and an explicit cleanup boundary.

Operational monitoring should go beyond the percentage of reads sent to replicas. Useful signals include primary fallback count, authoritative-read count, unknown-procedure rejection count, replica apply lag, retry outcomes, and any observed read-after-write mismatch. A high replica hit rate is not a success metric if correctness defects are hidden inside it.

The off switch should be monitored as well. Operators should know whether splitting is enabled in the effective runtime configuration, not merely in a repository file. If configuration is loaded once at startup, the release procedure must state that a restart is required. A switch that appears changed but is not active can mislead incident response.

How should read-after-write and cache publication work?

If a reload will update a cache, publish an event, decide another write, or become the authoritative API response, bind that read to the primary. Make this decision visible in code instead of relying on an invisible connection-string rewrite.

One approach is an explicit scope or consistency parameter, such as UsePrimaryDb(), ReadConsistency.Authoritative, or a dedicated primary query context. The exact name is less important than review visibility. A maintainer should be able to see that a particular SELECT has a stronger consistency requirement than an ordinary list page.

Avoid ending a write method with a generic GetById() call when that generic method normally routes to a replica. Use a named authoritative reload method or pass a consistency option. A name such as LoadAuthoritativeSnapshotAsync documents the promise better than LoadAgainAsync, which can be casually redirected during later refactoring.

For cache publication, preserve a disciplined order: commit the primary write, reload the authoritative snapshot from the primary, validate a version or concurrency condition, and only then update the cache. Do not advance a cache version marker before storing content that may have come from a lagging replica. A newer marker proves publication order, not content freshness.

Primary routing does not solve every concurrency problem. An existing transaction snapshot does not automatically advance because the connection targets the primary; define the commit and subsequent-read boundaries when committed visibility is required. Locking reads need an appropriate transaction scope. A slower publisher can still overwrite a newer cache value after reading the primary, so version checks and cache writes need an appropriate atomic conditional update or ordering mechanism, not a separate check followed by an unconditional write.

Fallback also needs truthful result semantics. If a lag-tolerant replica read fails, the application may log the reason and retry once on the primary. If both attempts fail, return an explicit failure. Do not convert the failure into an empty collection and report “no data.” Empty data and failed retrieval can trigger completely different business behavior.

Expensive queries need controlled fallback. If a replica outage causes every request to run the same large query on the primary, the recovery path can create a second load spike. Bounded concurrency, a short circuit-breaker window, or single-flight request coalescing can control the fallback volume. These mechanisms manage pressure; they must not redefine failed or stale data as successful data.

A safe design also records why the primary was chosen: splitting disabled, non-primary source, unsafe SQL token, stored procedure not allow-listed, explicit authoritative scope, or replica fallback. Those reason codes make routing behavior auditable without logging full query values.

Verification and reproduction with a pinned .NET 10 routing lab

This isolated lab pins .NET SDK 10.0.400 and verifies SQL classification plus the authoritative-read rule. It was executed in the scheduler environment without a database connection and without creating, updating, or deleting any database record.

  1. Create an empty directory, add a global.json that pins SDK 10.0.400, and create a net10.0 console project with warnings treated as errors.
  2. Save the following code as Program.cs; it evaluates synthetic SQL strings and has no database provider or connection definition.
  3. Run dotnet --version, require 10.0.400, then run dotnet run -c Release --nologo from the project directory.
  4. Pass only when all four PASS lines and the final ALL PASS marker appear and the process exits with code zero. Any exception, missing marker, nonzero exit, or different SDK fails the check.

global.json

{
  "sdk": {
    "version": "10.0.400",
    "rollForward": "disable"
  }
}

ReadRoutingLab.csproj


  
    Exe
    net10.0
    enable
    enable
    true

Program.cs

using System.Text.RegularExpressions;

enum DatabaseTarget { Primary, Replica }

static class ReadWriteRouter
{
    private static readonly Regex LeadingReadVerb = new(
        @"^\s*(SELECT|SHOW|DESC|DESCRIBE|EXPLAIN)\b",
        RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);

    private static readonly Regex UnsafeToken = new(
        @"\b(INSERT|UPDATE|DELETE|REPLACE|CALL|LOCK|UNLOCK|LAST_INSERT_ID)\b|\bFOR\s+UPDATE\b",
        RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);

    public static DatabaseTarget Route(string sql, bool enabled, bool sourceIsPrimary)
    {
        if (!enabled || !sourceIsPrimary) return DatabaseTarget.Primary;
        return LeadingReadVerb.IsMatch(sql) && !UnsafeToken.IsMatch(sql)
            ? DatabaseTarget.Replica
            : DatabaseTarget.Primary;
    }

    public static DatabaseTarget AuthoritativeReadAfterWrite() => DatabaseTarget.Primary;
}

static class Check
{
    public static void Equal(T expected, T actual, string name) where T : notnull
    {
        if (!EqualityComparer.Default.Equals(expected, actual))
            throw new InvalidOperationException($"FAIL: {name}; expected={expected}; actual={actual}");
        Console.WriteLine($"PASS: {name}");
    }
}

static class Program
{
    public static void Main()
    {
        Check.Equal(DatabaseTarget.Replica,
            ReadWriteRouter.Route("SELECT updatetime FROM sample WHERE id = 7", enabled: true, sourceIsPrimary: true),
            "identifier containing update is not mistaken for UPDATE");

        Check.Equal(DatabaseTarget.Primary,
            ReadWriteRouter.Route("SELECT id FROM sample WHERE id = 7 FOR UPDATE", enabled: true, sourceIsPrimary: true),
            "locking read stays on primary");

        Check.Equal(DatabaseTarget.Primary,
            ReadWriteRouter.Route("SELECT id FROM sample", enabled: false, sourceIsPrimary: true),
            "disabled routing preserves primary behavior");

        Check.Equal(DatabaseTarget.Primary,
            ReadWriteRouter.AuthoritativeReadAfterWrite(),
            "authoritative read after write uses primary");

        Console.WriteLine("ALL PASS");
    }
}

Expected output (also observed in the Editor rerun):

PASS: identifier containing update is not mistaken for UPDATE
PASS: locking read stays on primary
PASS: disabled routing preserves primary behavior
PASS: authoritative read after write uses primary
ALL PASS

This is deliberately a narrow unit model. Returning Primary when sourceIsPrimary=false does not authorize redirecting another connection to the primary: the caller must exclude non-primary sources and preserve their original connection before invoking this model. That integration layer is absent here. AuthoritativeReadAfterWrite() only demonstrates a constant primary-selection policy; no write, reload, or freshness assertion occurs. Four passing checks do not prove coverage of every locking syntax or end-to-end application consistency.

The real execution used SDK 10.0.400. It passed four checks: an identifier containing update was not confused with the UPDATE verb; SELECT ... FOR UPDATE remained on the primary; disabling splitting preserved primary behavior; and the authoritative read-after-write path selected the primary. The run ended with ALL PASS and exit code zero.

This result validates only the deterministic routing model. It does not prove a MySQL deployment topology, a maximum replication delay, a throughput improvement, or production readiness. Those claims require a separate database integration lab and workload-specific evidence.

A fuller sandbox reproduction can create synthetic rows, introduce controlled replica apply delay, demonstrate that an ordinary replica read may remain stale, and demonstrate that the authoritative path still reads the primary. Docker was not available in the scheduler environment, so those database-level steps are reader-reproducible steps; not executed in the scheduler environment. No latency or performance number is inferred from the unit test.

Release checklist and conclusion

Maintainable MySQL read/write splitting gives every read an explicit consistency promise, preserves a dependable off switch, exposes fallback outcomes, and prevents stale or failed reads from being mislabeled as authoritative success.

Before release, confirm that disabling the feature preserves the original route; only the recognized primary connection can be rewritten; SQL checks use word and phrase boundaries; locking reads and unknown procedures stay on the primary; cancellation is not retried as failure; fallback occurs at most once; failed retrieval is not returned as empty data; and cache publication consumes an authoritative primary snapshot.

Review the runtime configuration rather than assuming the repository value is active. Document whether changes require restart. Record the basis for every stored-procedure allow-list entry. Document which pages accept eventual consistency and which operations require the primary. An undocumented exception is likely to be removed or widened during a future refactor.

Also test the failure path under bounded load. A replica outage should not turn into an uncontrolled burst against the primary. The system should emit route reason, fallback result, and terminal failure signals without exposing sensitive query values. Operators need enough evidence to decide whether to disable splitting, reduce traffic, or investigate replication.

The final rule is simple: classify the purpose of the data before selecting its source. Reporting views, lists, and explicitly lag-tolerant pages may use a replica. Sign-in, authorization, locks, reversals, read-after-write reloads, cache publication, and state decisions should use the primary. This boundary keeps performance work inside the limits of correctness rather than trading correctness for an attractive replica-hit percentage.

Official references and claim scope

The replication manual supports the acknowledgement boundary; the InnoDB manual supports locking and transaction requirements. Read-only privilege exceptions and variable interactions belong to Server System Variables, not merely the replica startup-options page. read_only permits exceptions for users with CONNECTION_ADMIN or legacy SUPER; super_read_only additionally restricts those client updates, while replication threads can still apply data.