MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
Group Replication – Consistent Reads Deep Dive

On previous posts about Group Replication consistency we:

  1. introduced consistency levels;
  2. explained how to configure the primary failover consistency;
  3. presented how to configure transaction consistency levels to achieve the consistency required by your applications.

In blog 3. we presented the consistency levels: EVENTUAL, BEFORE, AFTER and BEFORE_AND_AFTER; their scopes: SESSION, GLOBAL; and their context: whether they only impact the ongoing transaction or all concurrent transactions.

On this post we extend that with a look at how each consistency level is implemented.

Consistency levels

  • EVENTUAL (the default)
    The transaction does not wait for preceding transactions to be applied before executing, nor does it wait for other members to apply its changes.
    This was the behaviour of Group Replication before 8.0.14.

Algorithm

  1. Transaction T1 starts on member M1.
  2. It is executed up to the commit point, at which point the transaction data is sent to all group members, including the one on which the transaction was executed (M1).
  3. On transaction delivery, every member will check for conflicts:
    1. If there is a conflict the transaction is rolled back;
    2. Otherwise, the transaction is committed on the member that executed the transaction (M1) and on the other members the transaction is queued to execute and commit.
  4. Transaction T2 starts on member M3 before M3 receives T1’s transaction data, T2 will execute before T1 is executed on M3, which may mean that a read from T2 is not up to date.

 

  • 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.

Algorithm

  1. Transaction T1, with EVENTUAL consistency, starts on member M1.
  2. It is executed up to the commit point, on which point the transaction data is sent to all group members, including the one on which the transaction was executed (M1).
  3. On transaction delivery, every member will check for conflicts:
    1. If there is a conflict the transaction is rolled back;
    2. Otherwise, the transaction is committed on the member that executed the transaction (M1) and on the other members the transaction is queued to execute and commit.
  4. Transaction T2, with BEFORE consistency, starts on member M3. Before the transaction execution, T2 will send a message to all members. That message will provide T2’s global order before execution (the first set of messages sent from T1 in the diagram).
  5. When that message is received and processed in-order, regarding the message stream, M3 will fetch the Group Replication applier RECEIVED_TRANSACTION_SET, which is the set of remote transactions that were allowed to commit (such transactions are included in this set whether or not they have actually been committed yet). This set gives us the remote transactions that exist before this point. We only need to track remote transactions since the server already ensures consistency for local transactions. Although the message providing T2’s global order was sent to all group members, only M3 needs to act on it, so the other members just discard it and take no action.
  6. Transaction T2 will only start executing on M3 after all the transactions in the Group Replication applier RECEIVED_TRANSACTION_SET are committed. This ensures that T2 will not read and execute on data that is out of date relative to its global order, which in this example is T1, T2. This wait only takes place on the server that executes the transaction with BEFORE consistency, on this case T2@M3. All others members are not affected by this wait.
  7. Once the execution of transaction T2 starts, the next steps are the ones described in 2) and 3).

 

  • 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.

Algorithm

  1. Transaction T1, with AFTER consistency, starts on member M1.
  2. It is executed up to the commit point, at which point the transaction data is sent to all group members, including the one on which the transaction was executed (M1).
  3. On transaction delivery, every member will check for conflicts:
    1. If there is a conflict the transaction is rollback;
    2. Otherwise, it goes to step 4).
  4. On the other members the transaction is queued to execute. Once the transaction is prepared – that is, data is finalized on the storage engines waiting for the commit instruction – it will send a acknowledgement (ACK) to all members.
  5. Once all members receive acknowledgements from all members – M1 implicitly has the acknowledgement since it already prepared the transaction – they all proceed to transaction commit.
  6. Transaction T2, with EVENTUAL consistency, starts on member M3. Since T1 is still being committed, T2’s execution will be held until T1’s commit is completed. This will ensure that any transaction after T1 will read T1’s data.
  7. Once the execution of transaction T2 starts, the next steps are the ones described for EVENTUAL consistency 2) and 3).

 

  • 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.

BEFORE_AND_AFTER consistency level combines BEFORE and AFTER together on the same 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 other transactions

The EVENTUAL consistency level does not provide any new guarantees to EVENTUAL transactions. It does not impose any restrictions to any transaction.

The BEFORE consistency level provides “backwards” data freshness guarantees to BEFORE transactions, meaning that a BEFORE transaction will observe the effects of all preceding transactions. It may impose a restriction on the beginning of BEFORE transactions, i.e. they may wait for all preceding transactions to be applied on the member before executing.

The AFTER consistency level provides “forward” data freshness guarantees to all succeeding transactions, meaning that an AFTER transaction’s effects will be observed by all succeeding transactions. It may impose a restriction on the beginning of all succeeding transactions, i.e. they may wait for the AFTER transaction to be applied on the member before executing.

Conclusion

I hope that this detailed view of how Group Replication consistency levels are implemented will help you decide how to tune HA properties in order to improve your systems!