Reducing Certification Conflicts in Hot Tables

This guide builds on the Write-Set Certification Process Explained and solves a design problem the parent guide only diagnoses: how do you actually re-shape a schema and its transactions so hot tables stop producing a storm of error 1213 certification conflicts? A conflict is not a bug to be tuned away with a server flag — it is the deterministic outcome of two nodes committing overlapping keys in the same certification window, and the only durable fixes are structural. Shorten the transactions, narrow the keys they touch, keep contended writes on one node, and stop funnelling every request through a single hot row. This page is the prescriptive counterpart to the parent’s mechanics: concrete schema patterns, transaction rules, and a production-grade retry wrapper for the conflicts you cannot design away.

Context: Why Hot Tables Certify Poorly #

Certification compares the primary-key, unique-key, and foreign-key references of each incoming write-set against every other write-set ordered but not yet applied. A “hot” table is simply one where many concurrent transactions across different nodes touch the same keys inside that narrow window, so their references overlap and the later-ordered write-set loses. The classic offenders are a running-total counter row, a status flag every worker updates, a sequence table hand-rolled in SQL, and a queue table where every consumer competes for the head. On a single MariaDB server these serialize behind row locks and merely wait; in a multi-master group they certify against each other across nodes and the loser is rolled back with a 1213, forcing an application retry. The relationship between global ordering and the winner is spelled out in how Galera handles concurrent writes in multi-master.

Two levers set the conflict rate, and both are under your control at design time. The first is the width of the key set a transaction touches — the more rows and indexes it modifies, the larger its certification footprint and the more write-sets it can collide with. The second is the duration the transaction stays open before COMMIT, because a write-set only competes with others ordered in the same window; a transaction held open across a user think-time or an external API call stretches that window and multiplies its collision surface. Shrinking both is the entire game.

Solution: Design Patterns That Cut the Conflict Rate #

Apply these in roughly the order of leverage — the first two remove conflicts, the rest reduce their frequency and blast radius.

1. Shard hot aggregate rows across a key space. A single counter, balance, or metrics row that every node increments is a guaranteed conflict. Replace it with N rows keyed by a shard column, write to the shard for the local node (or a hashed key), and reconstruct the value with SUM on read:

CREATE TABLE page_view_counter (
  page_id   BIGINT      NOT NULL,
  shard_id  TINYINT     NOT NULL,     -- e.g. hash(node_id) % 8
  views     BIGINT      NOT NULL DEFAULT 0,
  PRIMARY KEY (page_id, shard_id)
) ENGINE=InnoDB;

-- write path: each writer touches a distinct (page_id, shard_id) key
INSERT INTO page_view_counter (page_id, shard_id, views)
VALUES (42, 3, 1)
ON DUPLICATE KEY UPDATE views = views + 1;

-- read path: reconstruct the true total
SELECT SUM(views) AS total FROM page_view_counter WHERE page_id = 42;

Because each writer now touches a different primary key, the write-sets no longer overlap and certification passes on all of them.

2. Route contended writes to a single node (write affinity). Certification conflicts only arise across nodes; two transactions serialized on the same node wait on a normal row lock instead of racing in the total order. Pin all writes for a hot table — or a hot key range — to one designated node through your proxy or connection routing, and the conflict rate for that table drops to zero because there is no second node to collide with. This is the highest-value change for a workload with a few unavoidable hot rows, and it composes with the topology choices in designing multi-master topologies.

3. Keep transactions short and single-purpose. A write-set competes only with others ordered in its certification window, so a transaction that stays open longer collides with more. Never hold a transaction across user think-time, an external HTTP call, or a slow computation — gather the inputs first, then open the transaction, write, and commit immediately. Split a large batch UPDATE into chunks of a few thousand rows so no single write-set carries a wide key footprint that overlaps everything.

4. Use surrogate keys and avoid mutable natural keys. An AUTO_INCREMENT or UUID surrogate primary key with innodb_autoinc_lock_mode=2 spreads inserts across the key space instead of contending on a shared natural key. Prefer append-only inserts over in-place updates of a shared row where the data model allows it, and avoid UPDATEs that move a row’s unique-key value, since both the old and new key enter the certification set.

5. Replace hot queue rows with claim-by-key patterns. A job queue where every consumer runs UPDATE ... WHERE status='ready' ORDER BY id LIMIT 1 funnels all consumers onto the same head rows. Instead, have each consumer claim a distinct key range or use SELECT ... FOR UPDATE SKIP LOCKED so consumers grab disjoint rows and their write-sets stop overlapping.

Parameter & Design Reference #

wsrep_retry_autocommit only rescues single-statement autocommit transactions — a multi-statement transaction that loses certification is always returned to the application, which is why the retry wrapper below is mandatory rather than optional. Never disable wsrep_certify_nonPK to chase throughput; it makes keyless write-sets skip certification and silently diverge, as detailed in the write-set certification process parent guide.

Verification #

Prove the design change worked by watching the conflict counters’ rate, not their absolute totals. Baseline before, then compare after:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'wsrep_local_cert_failures',  -- write-sets this node failed to certify
  'wsrep_local_bf_aborts',      -- local txns aborted by an earlier-ordered write-set
  'wsrep_cert_deps_distance'    -- higher after sharding = more apply parallelism
);

A falling wsrep_local_bf_aborts delta after sharding a counter or adding write affinity is direct evidence the contention is gone. A rising wsrep_cert_deps_distance is a welcome side effect: spreading writes across more keys lets more write-sets apply in parallel. The full interpretation of these counters and their thresholds is the subject of interpreting wsrep certification-failure metrics.

For the conflicts you cannot design away, every write path needs a bounded retry. This Python 3.9+ decorator retries on the two conflict codes — 1213 (deadlock / certification failure) and 1205 (lock wait timeout) — with exponential backoff and jitter, rolling back first as the DB-API contract requires:

import functools
import random
import time
import mysql.connector
from mysql.connector import errorcode

RETRYABLE = {errorcode.ER_LOCK_DEADLOCK, errorcode.ER_LOCK_WAIT_TIMEOUT}  # 1213, 1205

def retry_on_conflict(attempts: int = 5, base: float = 0.05):
    """Retry a Galera write transaction that loses certification, with jittered backoff."""
    def decorator(fn):
        @functools.wraps(fn)
        def wrapper(conn, *args, **kwargs):
            for n in range(attempts):
                try:
                    result = fn(conn, *args, **kwargs)
                    conn.commit()
                    return result
                except mysql.connector.Error as exc:
                    conn.rollback()  # required after any error before reuse
                    if exc.errno in RETRYABLE and n < attempts - 1:
                        time.sleep(base * (2 ** n) + random.uniform(0, base))
                        continue
                    raise
        return wrapper
    return decorator

@retry_on_conflict()
def increment_counter(conn, page_id: int, shard_id: int) -> None:
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO page_view_counter (page_id, shard_id, views) VALUES (%s, %s, 1) "
            "ON DUPLICATE KEY UPDATE views = views + 1",
            (page_id, shard_id),
        )

Retry is the safety net, never the strategy: a workload that relies on it for a high percentage of writes still needs the schema change above, because every retry pays a full round-trip.

Edge Cases & Gotchas #

• INSERT ... ON DUPLICATE KEY UPDATE and blind UPDATE still conflict on the shared key. Sharding only helps if writers actually target different keys; if every node computes the same shard_id, you have simply renamed the hot row. Derive the shard from the writing node’s identity or a hash of a high-cardinality attribute so the keys genuinely spread.
• Write affinity fails open during failover. When the pinned node dies and traffic reroutes, the hot table briefly takes writes from a new node while stale connections drain, so a burst of conflicts is expected during the switch. Keep the retry wrapper in place precisely for these windows, and re-pin promptly — the failover mechanics are covered in automated failover and quorum recovery.
• Foreign keys widen the certification footprint invisibly. A write to a child row also certifies against its parent key, so a table that looks lightly contended can conflict through a shared parent every transaction references (a common lookup or tenant row). Audit the foreign-key graph, not just the table you are writing, when a conflict rate refuses to fall.

Related #

• Write-Set Certification Process Explained — the mechanics of the conflict these patterns prevent
• Interpreting wsrep Certification-Failure Metrics — reading the counters that prove a design change worked
• How Galera Handles Concurrent Writes in Multi-Master — why global ordering decides which writer loses
• Designing Multi-Master Topologies — placing write affinity within a resilient node layout
• Configuring wsrep_provider_options for Low Latency — provider tuning that complements the schema fixes