Group Replication – Consistent Reads

Like presented on theintroduction post, in MySQL 8.0.14 Group Replication, was once again, improved. Now the developer can specify which is the consistency level of all group transactions or even of a single transaction.

Note that this is about consistency w.r.t. global synchronization of transactions in the group. Then semantics for the different transaction isolation levels apply on each server.

The aforementioned post details that there are two synchronization points from which the developer can choose: on read or on write. Actually, these names are a simplification, the correct terms are: before and after transaction execution.

Consistency levels

The developer may choose between:

• EVENTUAL (the default)
  The transaction shall not wait for preceding transactions to be applied before executing, neither shall it wait for other members to apply its changes.
  This is the behaviour we have had so far in Group Replication.
• BEFORE
  The transaction will wait until all preceding transactions are complete before starting its execution. This ensures that this transaction will execute on the most up-to-date snapshot of the data, independently of on which member it is executed.

• AFTER

  The transaction will wait until its changes have been applied on other members. This ensures that once this transaction completes, all following transactions shall read a database state that includes its changes, independently of on which member they are executed.

• BEFORE_AND_AFTER

  This transaction will wait until : 1) all preceding transactions are completed before starting its execution; and 2) its changes have been applied on other members. This ensures that: 1) this transaction will execute on the most up-to-date snapshot of the data; and 2) once this transaction completes, all following transactions shall read a database state that includes its changes, independently of on which member they are executed.

As you may already have realized, the BEFORE consistency level can be both used on read and write transactions. The AFTER consistency level is a “no operation” (no-op) for read only transactions, since they will not generate changes.

These different consistency levels provide flexibility: 1) to the DBA, which can use these option to setup the infrastructure: and 2) to the developer to use the consistency level that best adapts to her/his application requirements.

Scenario 1

I want to load balance my reads without worrying about stale reads, my group write operations are much less than my group read operations.

On that case, I should choose AFTER .

Scenario 2

I have a data set that is subjected to a lot of writes and I want to do occasional reads without having to worry about managing/reading stale data.

On that case, I should choose BEFORE .

Scenario 3

I want specific transactions in my workload to always read up-to-date data from the group, so that whenever that sensitive data is updated (such as credentials for a file

or similar data) I will enforce that reads shall read the most up to date value.

On that case, I should choose BEFORE .

Scenario 4

I have a group that has predominantly read-only (RO) data, I want my read-write (RW) transactions to be applied everywhere once they commit, so that subsequent reads are done on up-to-date data that includes my latest writes and I do not pay the synchronization on every RO transaction, but only on RW ones.

On that case, I should choose AFTER .

Scenario 5

I have a group that has predominantly read-only data, I want my read-write (RW) transactions to always read up-to-date data from the group and to be applied everywhere once they commit, so that subsequent reads are done on up-to-date data that includes my latest write and I do not pay the synchronization on every read-only (RO) transaction, but only on RW ones.

On that case, I should choose BEFORE_AND_AFTER .

Consistency scope

On top of that, the developer has the freedom to choose the scope on which the consistency level will be enforced.

The consistency level can be set on the system variable group_replication_consistency on the following scopes:

Session

SET @@SESSION.group_replication_consistency= 'BEFORE';

Will enforce the consistency level on the current session.

Global

SET @@GLOBAL.group_replication_consistency= 'BEFORE';

Will enforce the consistency level on all sessions.

The possibility of setting the consistency level on specific sessions, allows the developer to take advantage of scenarios like:

Scenario 6

A given system handles several instructions that do not require a strong consistency level, though one kind of instructions does: manage access permissions to documents; the system changes access permissions and it wants to be sure that all will see the correct permission.

The developer only needs to SET @@SESSION.group_replication_consistency= ‘AFTER’ , on those instructions and leave the others run with EVENTUAL .

Scenario 7

On the same system of Scenario 6, every day a instruction needs to do some analytical processing, as such it requires to always read the up-to-date data.

To achieve that, the developer only needs to SET @@SESSION.group_replication_consistency= ‘BEFORE’ on that specific instruction.

So to summarize, the developer does not need to run all transactions with a consistency level if only some transactions do require it.

Though, let me highlight that, all read-write transactions are totally ordered in Group Replication, so even when you set the consistency level to AFTER to the current session:

SET @@SESSION.group_replication_consistency= 'AFTER';

this transaction will wait that its changes are applied on all members, which means wait for this and all precedent transactions that may be on the secondaries queues. In practice, the consistency level AFTER is wait for everything until and including this transaction.

Consistency context

We can also classify the consistency levels in terms of impact on the group, that is, the repercussions that they cause on the other members.

The BEFORE consistency level, apart from being ordered on the transaction stream, only has impact on the local member. That is, it does not require coordination with the other members neither have repercussions on their transactions.

In other words, BEFORE only impacts the transactions on which it is used.

The AFTER (and BEFORE_AND_AFTER ) consistency level do has side-effects on concurrent transactions executed on other members, it will make the other members transactions to wait – if the transactions with consistency level EVENTUAL do start while the one with AFTER is executing – until the AFTER transaction is committed on that member, even if the other members transactions have EVENTUAL consistency level.

In other words, AFTER (and BEFORE_AND_AFTER ) do impact all ONLINE members.

Example E1

# Group with 3 members: M1, M2 and M3
# on M1 a client executes:
 > SET @@SESSION.group_replication_consistency= AFTER;
 > BEGIN;
 > INSERT INTO t1 VALUES (1); # T1
 > COMMIT;
# concurrently, while T1 is being committed, on M2 a client executes:
 > SET SESSION group_replication_consistency= EVENTUAL;
 > SELECT * FROM t1; # T2

Note that even if T2 consistency level is EVENTUAL, if it starts executing while T1 is already in the commit phase at M2, then T2 shall wait for T1 to finish the commit and only then execute.

Safeguards

The developer can only use the consistency levels BEFORE , AFTER and BEFORE_AND_AFTER on ONLINE members, attempt to use them on other member states will cause a session error.

Transactions whose consistency level is not EVENTUAL will hold execution until wait_timeout value is reached, which default is 8 hours. If the timeout is reached error ER_GR_HOLD_WAIT_TIMEOUT is thrown.

Conclusion

Since its initial release, Group Replication is being continuously and steadily improved. MySQL 8.0.14 includes one of most requested features, the possibility to specify the consistency level of the group, or even allow more complex scenarios on which just a subset of the transactions do need stronger consistency.

Please do try it and send us your feedback.

230 total views, 132 views today