The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Scaling and distribution

Replication lag and CAP

I just saved it, but the query shows the old value

If you send writes to the source (primary) database and reads to a replica, you can spread out the read load. But it takes time for a change on the source to reach the replica. This time difference is called replication lag. The CAP theorem says that when the network is partitioned, a system has to choose between continuing to respond (availability) and refusing to respond with data that may be stale (consistency), and this lab shows both cases.

Conceptual model · A model that shows the principle

Concepts in this lab

Replication

Continuously copying the changes of an original database (the source, also called the primary) to another database (a replica) so that both keep the same data. It reduces load by spreading read requests across replicas, and if the source fails, a replica can be promoted to take its place and keep the service running. There is a small delay in the copying, so data that was just written may not be visible on a replica right away.

Eventual consistency

The guarantee that, although some nodes may show the old value right after data changes, all nodes end up with the same value over time if no further changes are made. It gives up immediate consistency and gains availability and scalability in return. It is commonly adopted in distributed databases and for synchronizing data between microservices.

CAP theorem

The theorem that a distributed data store cannot guarantee all three of the following at the same time: consistency (every node shows the same data), availability (every request gets a response), and partition tolerance (the system keeps working even when communication between nodes is cut). Network partitions cannot be avoided, so the real choice is whether to put consistency or availability first when a partition happens.

Read-only transaction (readOnly)

@Transactional(readOnly = true) is a setting that declares that the transaction does not change data. With JPA (Hibernate), optimizations apply, such as skipping the flush that writes changes to the DB, and when you have separately configured one DB for reads and one for writes, it also serves as the criterion for routing to the DB for reads. Put it on query methods to make the intent clear.

Try these

• Default settings — Replication takes 1s, and the replica is queried 0.3s after the save. The old value comes back.
• Only the query right after saving goes to the source — A user who has just written reads from the source.
• A query after some time has passed — Once replication has finished, the replica also returns the new value.
• When replication falls 5s behind — Even a read 2s later returns the old value.
• Network partition: keep responding — The service keeps working, but it returns the old value.
• Network partition: refuse to respond — It does not return a wrong value, but the query fails.

Running with the default settings

1. The member's nickname is "Alice", and the source and the replica have the same value.
2. 0s: The user changes the nickname to "Bob". It is saved on the source database and committed. The user gets a "saved" response. It has not yet been delivered to the replica.
3. 0.3s: The query is sent to the replica. Replication has not arrived yet, so the replica still has the old value "Alice". This time difference is the replication lag. The user sees a screen on which the nickname they just changed is unchanged.
4. 1s: Replication finally arrives, and the replica also becomes "Bob". Later queries see the new value.

Result of this run

• Replication is usually asynchronous, so there is a time lag between the source and the replica. Given time they become the same, but before that they can differ (eventual consistency). Ways to handle this are to read the data a user has just written from the source, or, on the screen right after a write, to use the value received in the response as it is.

Code

// A case where the data sources are set up separately so that readOnly transactions go to the replica and everything else goes to the source
@Transactional
public void changeNickname(long id, String name) {     // -> source
    memberRepository.findById(id).orElseThrow().setNickname(name);
}

@Transactional(readOnly = true)
public Member getMember(long id) {                      // -> replica
    return memberRepository.findById(id).orElseThrow();
}

Assumptions of this model

This assumes one source and one replica with asynchronous replication. The replication time was treated as constant at the set value, but in practice it fluctuates with load: it is usually under 0.1 seconds, and it can momentarily widen to several seconds or more. The CAP theorem is about the choice to make during a network partition, and real systems mix different choices depending on the kind of data.

Previous lab: Buffer pool and pages · Next lab: Sharding