Single-Primary vs Multi-Primary Modes, Conflict Detection, and Workload Constraints

Learning outcomes

The ServiceHub cluster is healthy in single-primary mode. A common next thought is: “If three servers are online, why not write to all three and triple throughput?” That conclusion skips the expensive part of distributed writing: transactions issued on different members can touch the same rows or related constraints before either member has observed the other's commit. Group Replication must certify those transactions and reject conflicts to preserve one history.

Single-primary: one write owner, several synchronized members

In single-primary mode—Group Replication's default—one member is read/write and the other group members are made read-only with super_read_only=ON. If the primary leaves unexpectedly and quorum remains, the group elects a new primary. This removes an entire class of concurrent cross-member write conflicts because normal application writes have one entry point.

SELECT @@global.group_replication_single_primary_mode,       @@global.group_replication_enforce_update_everywhere_checks;SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLEFROM performance_schema.replication_group_membersORDER BY MEMBER_ROLE, MEMBER_HOST, MEMBER_PORT;

Switch modes through AdminAPI

For an InnoDB Cluster, the supported control plane is MySQL Shell AdminAPI. Do not manually change group_replication_single_primary_mode on members behind AdminAPI's back. The cluster metadata and Group Replication configuration must remain coherent.

var cluster = dba.getCluster('ServiceHubHA')cluster.status({extended:1})cluster.switchToMultiPrimaryMode()cluster.status({extended:1})

All ONLINE members should now report a primary role in Group Replication terms and be writable. That does not mean every workload benefits. The group still certifies distributed transactions, and conflicting concurrent writes can be rolled back.

The certification mental model

Each transaction executes locally and builds the information Group Replication needs to identify the rows it modified. At commit, concurrent transactions are certified against transactions accepted by the group. If two transactions conflict, the group chooses a consistent outcome and one transaction can be rolled back. The losing application request must not assume “COMMIT always succeeds because UPDATE succeeded locally.”

CREATE DATABASE IF NOT EXISTS servicehub_ha_lab;CREATE TABLE IF NOT EXISTS servicehub_ha_lab.inventory_state (  asset_id BIGINT PRIMARY KEY,  available_parts INT NOT NULL,  version_no BIGINT NOT NULL DEFAULT 0) ENGINE=InnoDB;INSERT INTO servicehub_ha_lab.inventory_state(asset_id,available_parts,version_no)VALUES (1,10,0)ON DUPLICATE KEY UPDATE available_parts=10, version_no=0;

Conflict lab: two primaries update the same row

Open two clients against different writable members. The exact losing session is timing-dependent, which is itself part of the lesson: distributed certification is not a scriptable promise that “session B always loses.” The expected invariant is that both conflicting versions do not commit as independent truths.

SET autocommit=0;START TRANSACTION;UPDATE servicehub_ha_lab.inventory_stateSET available_parts=available_parts-1,    version_no=version_no+1WHERE asset_id=1;-- Keep the transaction open for the moment.

SET autocommit=0;START TRANSACTION;UPDATE servicehub_ha_lab.inventory_stateSET available_parts=available_parts-2,    version_no=version_no+1WHERE asset_id=1;-- Now COMMIT A and B close together from the two terminals.COMMIT;

One transaction may receive a certification/conflict rollback when the competing write is accepted. If your local timing makes both commits serialize cleanly, repeat with the transactions held open before nearly simultaneous commits. Do not invent a fake error number; record the exact server/client message your build returns.

SELECT * FROM servicehub_ha_lab.inventory_state WHERE asset_id=1;SELECT MEMBER_HOST, MEMBER_PORT, COUNT_TRANSACTIONS_IN_QUEUE,       COUNT_TRANSACTIONS_CHECKED, COUNT_CONFLICTS_DETECTEDFROM performance_schema.replication_group_member_stats;

AUTO_INCREMENT does not remove logical conflicts

In multi-primary mode, Group Replication can adjust auto_increment_increment and auto_increment_offset when those variables still have their normal defaults, using group_replication_auto_increment_increment to reduce duplicate generated identifiers. This helps with identifier allocation. It does not make two transactions that update the same business entity conflict-free.

SELECT @@global.group_replication_single_primary_mode,       @@global.group_replication_auto_increment_increment,       @@global.auto_increment_increment,       @@global.auto_increment_offset;

Isolation and constraint caveats

Group Replication's distributed certification cannot see every local lock concept that InnoDB uses. Gap locks are one example. For multi-primary groups, Oracle recommends READ COMMITTED unless the application depends on REPEATABLE READ semantics, because the reduced use of gap locking better aligns local behavior with distributed conflict detection. Strict “update everywhere” checks also reject some transaction classes such as SERIALIZABLE transactions and transactions involving cascading foreign-key constraints when those checks are enabled.

Tempting but wrong: route random writes to all members for “load balancing”

Random distribution ignores business contention. A workload with hot accounts, inventory rows, counters, or parent/child changes can spend more time certifying and retrying conflicts than a single-primary workload spends executing useful work. Multi-primary can be appropriate when write sets are naturally partitioned and conflict rates are demonstrably low, but that conclusion comes from workload evidence.

Return to the simpler mode

// Use an actual ONLINE member endpoint from cluster.status().cluster.switchToSinglePrimaryMode('icadmin@127.0.0.1:33151')cluster.status({extended:1})

The named instance becomes primary if eligible; the others return to secondary/read-only mode. Production teams should normally prefer the simplest topology that satisfies the workload and availability objective. Multi-primary is an advanced choice, not an upgrade badge.

What actually changes when you add writable entry points

In single-primary mode, the primary's InnoDB lock manager sees ordinary application write contention before commit. In multi-primary mode, two independent members can each execute a transaction locally because neither member owns the other member's local locks. Group Replication therefore needs a second, distributed conflict boundary at certification time. This is why a multi-primary application's error-handling contract is stricter: a transaction that reached the local COMMIT call can still lose certification against another transaction that the group accepted.

That distinction also explains why benchmark design matters. A test that inserts independent rows with random keys may show almost no conflicts, while the real ServiceHub workload may repeatedly update the same work-order, inventory, or scheduling records. The correct experiment therefore preserves business key skew, transaction size, think time, and retry behavior. “Transactions per second with unique synthetic keys” is not sufficient evidence for a multi-primary architecture.

Conflict classes: row overlap is only the easiest one to see

The same-row exercise is intentionally simple, but production contention is broader. Two transactions can overlap because they update the same row, because a unique key admits only one value, because parent/child changes interact, or because application invariants span several rows. Group Replication's certification information is built from transaction write sets; it does not understand the business meaning of “these two otherwise different rows represent the same appointment slot.” Database constraints and application transaction design still matter.

Retry design belongs in the mode decision

A certification failure is not permission to rerun arbitrary application code. A safe retry repeats the complete database transaction from a clean session/transaction boundary, re-reads current state, and is idempotent at the business boundary. External side effects—sending email, charging a card, calling another service—must not be duplicated merely because the database transaction was retried. Chapter 6 introduced bounded deadlock retries; multi-primary certification failures extend the same engineering principle to a distributed conflict boundary.

Before production adoption, instrument retry counts and final failure rates by transaction type. If one ServiceHub workflow repeatedly conflicts, the better fix may be routing that workflow to one writer, redesigning the transaction grain, or returning to single-primary mode—not increasing retry loops until the symptoms disappear.

Production judgment

Single-primary is a strong default for OLTP applications because write ownership is simple, failover behavior is easier to reason about, and ordinary application transactions do not arrive concurrently at independent primaries. Multi-primary is useful only when multiple writable entry points are a real requirement and conflict behavior, isolation, constraints, retries, and routing have been tested under realistic concurrency.

Next we move one level up: rather than manually manipulating Group Replication, we use AdminAPI to manage cluster lifecycle, recovery, rejoin, removal, and topology changes as one metadata-managed system.

Authoritative references

• Single-primary and multi-primary modes
• Group Replication limitations
• Group Replication system variables
• MySQL Shell Cluster API