Group Replication: Understanding Conflict Detection in Multi-Primary Setups

1. Certification-based conflict detection: the basic principle

Group Replication lets every node execute transactions locally without waiting for a network-wide lock upfront. Only at commit time does the transaction get distributed to all members through a Paxos-based group communication protocol and checked there against transactions already successfully certified. This optimistic approach avoids the latency cost of a synchronous locking round trip before every write, but shifts the conflict risk from write time to commit time.

Certification describes exactly this after-the-fact reconciliation: every node independently checks whether the transaction it received collides with rows that were already changed by another, faster-certified transaction between this transaction's start and its commit. Because every node applies the same deterministic certification logic, they independently arrive at the same result without any need for a central coordination instance.

2. How a write set is formed

A write set is a compact representation of every row a transaction changed, encoded as hash values of the affected rows' primary keys combined with their respective table. This is precisely why Group Replication mandates a primary key on every replicated table: without a unique key, no stable, comparable row identity can be formed for certification, and Group Replication rejects such transactions with an explicit error.

The write set is not built from the transaction's SQL text but from the rows actually affected after applying every WHERE condition. Two transactions with entirely different UPDATE statements syntactically, but that end up changing the same row, produce overlapping write sets and are therefore recognized as potentially conflicting, regardless of how the SQL itself was written.

3. The certification process step by step

At commit time, the executing node sends the write set together with the binlog events across the group communication protocol to every member, including itself. Every node compares that write set against a locally held history of already certified but not yet fully applied transactions. If the new write set does not overlap with any of those preceding transactions, the transaction is considered successfully certified and cleared for application.

If there is an overlap with a transaction already certified but delivered earlier, the newly arriving transaction gets rejected. What matters here is the order in which the group communication protocol delivers messages in a globally consistent way, not the actual wall clock time on the individual servers. That globally consistent delivery order is the actual core of the Paxos-based protocol and guarantees that every node independently reaches the same decision.

4. Typical conflict scenario: concurrent updates on two nodes

The classic example: two application instances connect to two different nodes in multi-primary mode and update almost the same instant the same stock record, for example UPDATE cataloginventory_stock_item SET qty = qty - 1 WHERE item_id = 4711. Both transactions read and change data locally without issue, both get committed locally and distributed to the group. Only one of the two survives certification, the other gets rolled back with a certification error after already having been signaled to its client as locally successful.

This behavior differs fundamentally from a classic deadlock within a single server, where the conflict is detected before the commit even happens. With Group Replication, the application only learns after an apparently successful commit that the transaction did not actually go through, which can easily push application code without appropriate handling into an inconsistent state.

-- Node A and node B execute almost at the same time:
START TRANSACTION;
UPDATE cataloginventory_stock_item SET qty = qty - 1 WHERE item_id = 4711;
COMMIT;
-- One of the two transactions fails only after the local COMMIT with:
-- ERROR 3101 (HY000): Plugin instructed the server to rollback the
-- current transaction (certification error)

5. What happens on a conflict: rollback and error handling

If certification fails, Group Replication performs an automatic rollback of the already locally committed transaction on the affected node. The client, who already received the original COMMIT as successful, has to detect this failure through a separate error later in the connection, typically error code 3101, and re-execute the entire transaction itself.

For application code this means every write transaction in multi-primary operation needs to sit inside a retry loop that recognizes this specific error code and retries the transaction with the same starting data. Without that retry logic, write operations under load appear to vanish seemingly at random, with the application itself never noticing an error, unless the original failure gets propagated correctly.

6. Single-primary versus multi-primary: the consequence for application architecture

In single-primary mode, Group Replication's default mode, only one node accepts write access, every other member automatically becomes read-only. That means certification conflicts between write transactions never occur, because there are no concurrent, competing writers across different nodes. Certification still exists as a mechanism, but practically never triggers as long as no failover with a briefly ambiguous primary role is happening.

In multi-primary mode that guarantee disappears entirely, every node can be a target of writes at the same time, which promises horizontal write scaling across several nodes but forces every application to handle certification conflicts as a normal operational case. For most Magento-adjacent use cases, where write load tends to be narrow but consistency critical, such as stock levels or order status, single-primary mode is in practice the far simpler choice to operate.

7. When multi-primary still makes sense

Multi-primary pays off above all when write load can be distributed geographically or at the application level so that different nodes mostly handle disjoint data ranges, for example separate customer groups or independent table ranges with no row overlap. In such cases the actual conflict rate stays low, even though the mode formally keeps every node writable.

Where that separation cannot be achieved, for example with central, frequently updated counters like stock levels, the certification conflict rate rises disproportionately with write frequency, because the probability of a genuine row overlap between two nodes working in parallel grows. Application code in that case not only needs retry logic, it also needs to favor idempotent write operations, such as relative rather than absolute updates, to make repeated execution after a rollback safe.

8. Monitoring certification conflicts

Group Replication logs conflict statistics directly in performance_schema.replication_group_member_stats, in particular in the COUNT_TRANSACTIONS_ROLLBACK_DUE_TO_PRIMARY_TRANSACTION_LIMIT_VIOLATION column and COUNT_CONFLICTS_DETECTED. A steadily rising COUNT_CONFLICTS_DETECTED value is a reliable early warning sign that multi-primary mode does not fit the current access pattern well, long before users notice error messages in the frontend.

For a production monitoring dashboard it pays to query these counters regularly per node, combined with an alert threshold relative to overall transaction rate, since an absolute conflict count without reference to throughput carries little meaning on its own. A rise in relative conflict rate beyond a few percent of overall transactions usually points at a structural access pattern problem that retry logic alone cannot fix.

SELECT MEMBER_ID, COUNT_CONFLICTS_DETECTED, COUNT_TRANSACTIONS_REMOTE_APPLIED
FROM performance_schema.replication_group_member_stats;

9. Practical recommendation for table design and retry patterns

For tables that might be written to in multi-primary mode, a design that reduces row overlap from the start pays off, for example partitioning by tenant or region at the business logic level instead of relying on a single central counter table. Where a central row remains unavoidable, such as a global stock level, the application should deliberately limit that write load to a single logical writer, even if the infrastructure formally runs multi-primary.

At the application level, a generic retry pattern has proven effective: catch the certification error code specifically, insert a short randomized wait, and re-execute the transaction with freshly read starting data instead of blindly rewriting the original, possibly stale values. This pattern not only lowers the error rate, it also prevents repeated retries from recreating the exact same conflict in an endless loop.

10. Summary

11. FAQ: Conflict Detection in Group Replication: Key Facts at a Glance