Read-after-write consistency on a Galera cluster

Commit means certified, not applied

A Galera cluster runs a transaction on one node. At commit, that node broadcasts the write set to the cluster, every node certifies it against the write sets already in flight, and the commit returns to the client. From that moment the write is durable on the cluster and ordered identically everywhere.

What has not happened yet is the apply. Each node takes the certified write set and applies it to its own copy of the data in its own applier threads, slightly after the commit returned. Two clocks are running: certification is cluster-wide and part of the commit, application is local and comes after. On a healthy cluster the distance between them is tens to a few hundred milliseconds. It grows during write bursts, large transactions and schema changes, and it is visible as a non-zero wsrep_local_recv_queue on the node that is behind.

Where the read goes

A read-balanced cluster sends reads wherever the proxy decides. A request writes on node A; the next request from the same user, a few hundred milliseconds later, reads on node B. If node B has received but not yet applied the write set, it answers with the row as it was before the write. From node B's point of view the answer is correct: that is its committed state. Nothing is logged, nothing alarms, and the lag monitor stays quiet because it is built to catch sustained lag, not the first hundred milliseconds after one transaction.

That is why the symptom looks random. It depends on which node the balancer picked, how fast the user came back, and how busy the appliers were at that instant.

What it looks like in an application

The pattern is a write followed immediately by a read from the same user, landing on a different node. In a CMS it is the vanished paragraph: save, refresh, old text, retype, save, and the second save overwrites the first. In a shop it is an order that does not exist on the confirmation page. In an auth flow it is a token that was just issued and is not found.

Two other mechanisms produce a similar story and are worth ruling out first. A browser's back-forward cache can restore a stale copy of a form, and a re-save then writes that stale copy. A plugin can do a read-modify-write on the same row from two requests. Both are real and neither is the cluster. A loop that writes a timestamp on one node and reads it back on the others separates them in a minute.

The setting: wsrep_sync_wait

wsrep_sync_wait is a bitmask. With bit 1 set, a SELECT waits until the node has applied every write set it has received up to that moment before it runs. The read is then guaranteed to see anything committed anywhere in the cluster before it started. It can be set for one session, which is the right scope for an editor or checkout path, or globally when the whole workload is sensitive to read-after-write.

-- for one session: the request path that writes and then reads
SET SESSION wsrep_sync_wait = 1;

-- for the whole node: my.cnf, under [mysqld]
wsrep_sync_wait = 1

The alternative is routing: send the reads that follow a write to the node that wrote, with proxy query rules or in the application. It works for the paths it covers. One code path that nobody routed reopens the gap, which is why the setting is preferred where the cost can be paid: it addresses the mechanism, not one path through it.

What it costs and where to pay it

A read under wsrep_sync_wait waits for the local applier to catch up, so it pays the apply lag once per read that follows a write. On a healthy cluster that is tens to a few hundred milliseconds. Logged-in sessions, admin and editor paths and checkout are where that cost buys something. Anonymous pages served from an edge cache do not touch the database on a hit and are unaffected. The setting takes effect on the next statement and reverts the same way, so it can be measured in place with the real workload instead of estimated.

How to check your own cluster

Three readings settle it. Whether reads wait at all; how far behind a node runs during a write burst; and how often a cross-node read-after-write actually sees the past, from a loop that writes on one node and reads on the others.

SHOW GLOBAL VARIABLES LIKE 'wsrep_sync_wait';        -- 0 means reads do not wait
SHOW GLOBAL STATUS LIKE 'wsrep_local_recv_queue';    -- received, not yet applied

We run read-balanced Percona XtraDB clusters behind ProxySQL and HAProxy and can answer those three for any cluster we operate, including what the setting costs on that specific workload.