What happens to mysql synchronous replication when read replica can't respond?

Question

In the official doc

https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication.html It mentioned that if synchronous replication is required, use NDB instead

For scenarios where synchronous replication is required, use NDB Cluster (see MySQL NDB Cluster 7.5 and NDB Cluster 7.6).

Question 1. Does mysql not support synchronous replication, only mysql cluster support synchronous replication?

Question 2. Where is the doc for synchronous replciation for NDB I see multiple post mentioning that NDB replication is synchronous by default https://stackoverflow.com/questions/53149674/can-i-implement-synchronous-and-asynchronous-replication-with-the-mysql-cluster

But the official doc only mention asynchronous and semisynchronous replication https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-replication.html

NDB Cluster supports asynchronous replication, more usually referred to simply as “replication”. This section explains how to set up and manage a configuration in which one group of computers operating as an NDB Cluster replicates to a second computer or group of computers. We assume some familiarity on the part of the reader with standard MySQL replication as discussed elsewhere in this Manual. (See Chapter 19, Replication).

Question 3: If mysql or NDB supports synchronous replication, do they use 2PC? What happens during network partition, or replica nodes are not available? Does NDB sacrifice availability over consistency? Does NDB do leadership election?

This post say it does https://dev.mysql.com/blog-archive/2-phase-commit-in-ndbcluster/

But I can't find documentation on the behavior of the trade off on availability vs consistency during network partition or replica failure?

Answer 1

MySQL does not support synchronous replication. There will always be a delay because changes are not written to the binary log until you commit a transaction, then the replica must download the binary log and apply the changes on its instance.

The client that made changes on the primary instance does not wait for the same changes to be applied on the replica.

In the case of semi-synchronous replication, the client making changes on the primary waits on COMMIT until at least one replica has downloaded the respective binary log events — but the client still does not wait for those binary log events to be applied. It's enough assurance of redundancy to know that the replica has received the binary log.

This is still really asynchronous, in the sense that the change is not immediately available on the replica once the COMMIT has finished on the source.

NDB Cluster is no different with respect to replication. When copying data from one cluster to a remote cluster, it works via the binary log just as with any other MySQL storage engine, and it is always asynchronous (or at best semi-synchronous).

The documentation you saw that recommends "for scenarios where synchronous replication is required, use NDB Cluster" is misleading. NDB Cluster is not a replication solution, it's a storage engine.

What the writer of that documentation might have meant is that if your goal is to ensure that data is stored in more than one place, to protect against data loss in case of a storage failure, then NDB Cluster can be a solution because storing any data change copies the data synchronously to two fragment replicas. The fragment replicas are all in the same MySQL Server instance. No binary log is involved.

This is unfortunately overloading the term "replica" and that may be confusing. An NDB Cluster fragment replica is not a MySQL Server replica.

The NDB storage engine writes changes to two (or more) nodes to achieve redundancy. This does use two-phase commit, but it is handled internally in the NDB storage engine, not as part of SQL transaction semantics.

If a node of an NDB Cluster is unresponsive or a network partition occurs that make the node unreachable from the management node, the Cluster disables that node. I suggest you read about the arbitrator in an NDB Cluster.

---

Re your comment:

so read after write in read replica can possibly read stale data before binlog is applied in read replica

Yes, that's correct. You can't rely on the replica instance having applied binlog events immediately after COMMIT on the source instance. There is always a delay, even if it is small.

You can measure the delay (sometimes called replication lag) in various ways, which I assume you're aware of.

You can also read from a replica using a blocking function SOURCE_POS_WAIT(), so you are sure it has "caught up" i.e. applied at least up to a specific binlog position. If you know what the binlog position was on the source when you executed a given update, this allows you to wait until the replica's data includes that change.

Another definition of consistency (another overloaded term with multiple meanings) is that all constraints are satisfied before and after any given transaction. MySQL does support this by default. I.e. all transactions are committed atomically on a replica, even if there is a delay.