skip to content

In MySQL Group Replication, how does certification decide whether a transaction commits, and why is single-primary mode the default?

level: middleimportance: nice to knowfreq 25%

answer

  1. the group agrees on one order
  2. rows changed, broadcast at commit
  3. first in the order wins
  4. majority needed to decide
  5. multi-primary's extra restrictions

basics

~20 s

In MySQL Group Replication, a committing transaction broadcasts its changed rows and writeset; all members receive it in one agreed order, and certification rolls it back if an earlier concurrent transaction changed the same row. Single-primary mode avoids those conflicts.

solid answer

~40 s

When a read/write transaction commits on a Group Replication member, the member atomically broadcasts the changed rows and the transaction's writeset, the identifiers of the rows it touched. A majority of the group must agree on the order, so every member receives the same transactions in the same order. Certification then compares writesets: if two concurrent transactions from different members changed the same row, the one ordered first commits everywhere and the second is rolled back on its origin, a distributed first-commit-wins rule. Read-only transactions commit without coordination. `group_replication_single_primary_mode` defaults to `ON` because with one writer, conflicts between members essentially disappear and the secondaries run with `super_read_only=ON`. Multi-primary mode accepts writes everywhere but refuses `SERIALIZABLE` transactions, does not support cascading foreign keys and risks undetected conflicts from concurrent DDL and DML on different members.

go deeper

for a junior

Recall that Group Replication keeps several MySQL servers agreeing on every write and that one primary takes writes by default.

for a middle

Explain the broadcast, the global order and row-level certification, and why the transaction ordered first wins a conflict.

for a senior

Show you know what bites in production: retries after certification failures, flow control, the transaction size limit, and majority loss blocking writes.

for a principal

Judge whether multi-primary mode is ever worth its restrictions for your workload, and how many members and locations a group should span.

## What Group Replication is **MySQL Group Replication** is a plugin that turns a set of MySQL servers into a **group** that agrees on every write. It builds on a Paxos-based group communication engine and the usual binlog machinery, and it adds requirements that ordinary replication does not have: - data in **InnoDB** only, because conflicting transactions are rolled back; - a **primary key** or a non-null unique key on every replicated table, so that rows can be identified exactly; - `binlog_format=ROW` (the 8.4 default) and `gtid_mode=ON` with `enforce_gtid_consistency=ON` (not the defaults); - a group of at most **nine** members on a low-latency network. Because decisions need a **majority**, a group tolerating `f` failures needs `n = 2f + 1` members; three members survive the loss of one. ## How a commit is decided The steps for a read/write transaction, as the manual describes them: 1. The transaction executes locally on the member that received it, optimistically, without asking the group. 2. At commit, that member **atomically broadcasts** the changed row values and the **writeset**, the unique identifiers of the rows it updated. Either all members receive the message or none do. 3. The group communication layer gives every transaction a place in **one global order**, and every member receives the same transactions in that order. 4. **Certification** runs on every member in that order. If two concurrent transactions that executed on different members changed the same row, the one ordered **first** commits on all members and the later one is **rolled back** on its originating member and dropped by the others. 5. Certified transactions are applied on the remote members; the client on the origin gets its result. Read-only transactions skip all of this and commit immediately. The rule in step 4 is a **distributed first-commit-wins** rule, applied at row level. The manual's advice for transactions that conflict often is to run them on the same member, where they wait on the local lock manager instead of being rolled back by certification. ## Single-primary versus multi-primary | | Single-primary (default) | Multi-primary | |---|---|---| | `group_replication_single_primary_mode` | `ON` | `OFF` | | Who accepts writes | one elected primary | every member | | Secondaries | `super_read_only=ON` | all writable | | Certification conflicts | rare | normal under contention | | Extra restrictions | few | see below | Multi-primary mode adds restrictions the single-primary default avoids: - `SERIALIZABLE` is not supported by default; a transaction at that level is refused at commit. - Tables with **cascading foreign keys** are not supported, because the cascaded changes can conflict without being detected. - **Concurrent DDL and DML** on the same object from different members is not supported, since conflicting changes might not be detected. With one writer, the application sees ordinary MySQL behaviour, and certification still guarantees that every member has the same history. ## Consistency and failover Group Replication is **eventually consistent** while traffic flows: a member can externalize a transaction slightly before others. `group_replication_consistency` tunes this per session or globally. Its 8.4 default, `BEFORE_ON_PRIMARY_FAILOVER`, makes a newly elected primary hold new transactions until it has applied the backlog from the old primary, so clients never read stale data from the new primary; `EVENTUAL`, `BEFORE`, `AFTER` and `BEFORE_AND_AFTER` trade latency for stronger guarantees. ## Moving the primary and adding members - **Planned switchover.** `group_replication_set_as_primary()` hands the primary role to a chosen member in single-primary mode; it waits for running transactions, including DDL such as `ALTER TABLE`, to complete before the new primary is elected. - **Joining members.** A new member catches up through **distributed recovery**, copying state from an existing member. In 8.4 it is marked `ONLINE` only after it has received, certified and applied every transaction that happened before it joined, so a freshly joined member does not serve stale reads as if current. ## Limits worth remembering - **Flow control.** `group_replication_flow_control_mode` defaults to `QUOTA`, throttling writers when members fall behind on certification or apply. - **Large transactions.** `group_replication_transaction_size_limit` defaults to 150000000 bytes; a bigger transaction is rolled back by the member that ran it and never broadcast to the group. - **Losing the majority.** A minority partition cannot commit writes; it blocks rather than diverge. - **InnoDB Cluster.** It is Group Replication packaged with MySQL Shell's AdminAPI for setup and MySQL Router for client routing; the certification mechanics are the same.

  • What does a MySQL application see when its transaction loses Group Replication certification?
    Its commit fails and the transaction is rolled back on the member where it ran, even though every statement inside it succeeded locally. The application must be ready to retry the whole transaction. In single-primary mode this is rare because only one member takes writes; in multi-primary mode, contended rows make it routine, which is why the manual suggests sending conflicting work to the same member.
  • Why does MySQL Group Replication require a primary key on every replicated table?
    Certification detects conflicts by comparing writesets, the identifiers of the rows each transaction changed. Without a primary key or a non-null unique key there is no exact row identity, so the group could not tell whether two transactions touched the same row. Group Replication runs its own built-in check for that key rather than relying on `sql_require_primary_key`.

Certification works like a shared booking ledger read by several desks in the same order: two desks may each pencil in seat 14C, but only the booking that reaches the ledger first stands, and the other desk has to tell its customer the seat is gone.

  • Every member must lock a row before any member can change it.
  • In a certification conflict, the member with the lowest server_id wins.
  • Group Replication works with any storage engine, including MyISAM.
  • Multi-primary mode supports every feature that single-primary mode supports.
  • A two-member group survives losing either member and keeps accepting writes.
  • Read-only transactions also go through certification before returning.

Assumes MySQL 8.4 LTS, where group_replication_consistency defaults to BEFORE_ON_PRIMARY_FAILOVER and ROW is the default binlog format that Group Replication requires.