The comment at [1] hints at a solution: in the response of the write request return the id of the transaction or its commit position in the TX log (LSN). When routing a subsequent read request to a replica, the system can either wait until the transaction is present on the replica, or it redirect to to the primary node. Discussed a similar solution a while ago in a talk [2], in the context of serving denormalized data views from a cache.
ProxySQL has a more simplified version of this, but it’s transparent to the client. Since it’s constantly monitoring the cluster, it knows what replica lag is at any given time. If you send a request to a read replica, it holds the request until enough time has passed for the replica to have caught up.
Comments
The comment at [1] hints at a solution: in the response of the write request return the id of the transaction or its commit position in the TX log (LSN). When routing a subsequent read request to a replica, the system can either wait until the transaction is present on the replica, or it redirect to to the primary node. Discussed a similar solution a while ago in a talk [2], in the context of serving denormalized data views from a cache.
[1] https://news.ycombinator.com/item?id=46073630 [2] https://speakerdeck.com/gunnarmorling/keep-your-cache-always...
ProxySQL has a more simplified version of this, but it’s transparent to the client. Since it’s constantly monitoring the cluster, it knows what replica lag is at any given time. If you send a request to a read replica, it holds the request until enough time has passed for the replica to have caught up.