[Dave3of5]

That's actually like what was done on the project in question. If you use a transaction it was set to always use the primary that way you can read and write all you want in your queries.

For this ACK that you talk about. It's AWS aurora mysql specifically here do you know if that's a setting you can setup?

[danpalmer]

Sorry no idea about AWS Aurora, but I think MySQL will do this itself.

[evanelias]

Unfortunately, it won't. Traditional MySQL replication does not provide a built-in option for fully synchronous behavior.

You can optionally use MySQL's "semi-sync" replication feature, in which replicas ACK receipt of transactions. This is purely for durability though, not consistency: it ensures commits are durable even if the primary DB has an unrecoverable hardware failure, but without having the massive latency penalty of fully-synchronous replication.

With semi-sync, the replicas are essentially just confirming they've queued up the transaction in their local relay log, rather than acknowledging execution of the transaction. The replicas may still be lagging behind the primary, in terms of what transactions they've applied locally; this means they'll return stale reads.

That said, the story is completely different in AWS Aurora, which (by default) uses proprietary physical (storage-level) replication. Within one region, AWS docs say their lag is "usually much less than 100 milliseconds", but they also note lag depends on transaction volume, so it's unclear what sync/async tradeoffs they're making behind the scenes.

In any case, for a nice third-party implementation of read-after-write consistency in MySQL, ProxySQL has a really powerful feature: https://proxysql.com/blog/proxysql-gtid-causal-reads/