As we showed on the introduction 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 in terms of the 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 the following consistency levels:

• EVENTUAL (the default)
  The transaction does not wait for preceding transactions to be applied before executing, neither does it wait for other members to apply its changes.
  This was the behaviour of Group Replication before 8.0.14.
• 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, regardless of which member it is executed on.
• AFTER
  The transaction will wait until its changes have been applied on other members. This ensures that once this transaction completes, all following transactions read a database state that includes its changes, regardless of which member they are executed on.
• 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 read a database state that includes its changes, regardless of which member they are executed on.

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, who can use these options to setup the infrastructure; and 2) to developers who can use the consistency level that best suits the application’s 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, you 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, you 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, you 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, you 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, you should choose BEFORE_AND_AFTER.

Consistency scope

You have the freedom to choose the scope at which the consistency level will be enforced. This is important because consistency levels could have a negative impact on the performance if you set them at a global scope.
Therefore we ensured that the consistency level can be set using the group_replication_consistency system variable at the following scopes:

Session

Will enforce the consistency level on the current session.

Global

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 such as:

Scenario 6
A given system handles several instructions that do not require a strong consistency level, though one kind of instructions does: managing access permissions to documents. In this scenario, the system changes access permissions and it wants to be sure that all clients will see the correct permission.
You only need to SET @@SESSION.group_replication_consistency= ‘AFTER’, on those instructions and leave the others run with EVENTUAL.

Scenario 7
On the same system as described in Scenario 6, every day a instruction needs to do some analytical processing, and as such it requires to always read the most up-to-date data.
To achieve that, you only need to SET @@SESSION.group_replication_consistency= ‘BEFORE’ on that specific instruction.

So to summarize, you do not need to run all transactions with a specific consistency level if only some transactions actually 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 by issuing:

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 have on the other members.

The BEFORE consistency level, apart from being ordered on the transaction stream, only impacts the local member. That is, it does not require coordination with the other members and does not 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 levels do have side-effects on concurrent transactions executed on other members. These consistency levels will make the other members transactions wait – if the transactions with consistency level EVENTUAL do start while a transaction with AFTER or BEFORE_AND_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 impact all ONLINE members.

Example E1

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 waits for T1 to finish the commit and only then execute.

Safeguards

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

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

Conclusion

Since its initial release, Group Replication has been 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 where just a subset of the transactions do need stronger consistency.
Please do try it and send us your feedback.