How/Why are in-flight transactions lost in synchronous replication in contrast to semi-synchronous

Question

I'm reading through an article on why SQL databases are hard to scale and problems with ACID.

Here's the link to the short article: Link

Today's solution is usually post-write replication, where each transaction is executed first at some primary replica, and updates are propagated to other replicas after the fact. Basic master-slave/log-shipping replication is the simplest example of post-write replication, although other schemes which first execute each transaction at one of multiple possible masters fall under this category. In addition to the possibility of stale reads at slave replicas, these systems suffer a fundamental latency-durability-consistency tradeoff: either a primary replica waits to commit each transaction until receiving acknowledgement of sufficient replication, or it commits upon completing the transaction. In the latter case, either in-flight transactions are lost upon failure of the primary replica, threatening durability, or they are retrieved only after the failed node has recovered, while transactions executed on other replicas in the meantime threaten consistency in the event of a failure.

I'm assuming that by the former statement, it is pointing to the semi-synchronous replication and by latter it points to the asynchronous replication such that a commit is said to be successful as soon as the transaction is committed on the primary replica.

However, I didn't quite get what does it mean by losing the transaction upon failure and how are in-flight transactions lost in synchronous replication and not the semi-synchronous case? Can anyone help to explain?

Isn't a transaction saved to disk before it's being executed?

Answer 1

Isn't a transaction saved to disk before it's being executed?

With synchronous replication, new data in a transaction is:

1. written to the transaction log,
2. depending on the implementation, maybe also write to the data file,
3. sent to the secondary system, which
4. writes it to disk, and then
5. sends an acknowledgement back to the primary.
6. Only then does the primary COMMIT the data.
7. If it wasn't written to the data file in step 2, it's written there now.

If the primary goes down, then the secondary system (which has the data) should be promoted to be the new primary. Thus, no data loss.

In the latter case, either in-flight transactions are lost upon failure of the primary replica, threatening durability, or they are retrieved only after the failed node has recovered, while transactions executed on other replicas in the meantime threaten consistency in the event of a failure.

That's the well-known trade-off of async replication.