Write-Set Certification Process Explained

This guide builds on the replication model described in MariaDB Galera Core Architecture & Fundamentals, and solves one specific operational problem: understanding exactly how Galera decides — deterministically, and identically on every node — whether a transaction commits or is rolled back, so you can stop treating WSREP certification failed and spurious deadlocks as random noise and start controlling them. Certification is the consensus step that turns a set of independent MariaDB servers into a single logical database. When it works you never see it; when it degrades you get commit stalls, retry storms, and — if you disable the wrong safety check — silent data divergence. This page gives database administrators, DevOps engineers, and Python automation builders a precise mental model of the certification lifecycle, the handful of parameters that govern it, and the exact commands and code to verify, automate, and troubleshoot it in production.

Concept: What Certification Actually Decides #

Certification is the deterministic conflict-detection algorithm Galera runs on every node, in the same total order, to reach the same commit-or-abort verdict for every write-set without any node-to-node voting. It is the mechanism that lets a multi-master group provide the consistency guarantees unpacked in Understanding Galera Synchronous Replication, and it is the reason concurrent writes to the same rows behave the way they do, as detailed in how Galera handles concurrent writes in multi-master.

When a client issues a COMMIT, the originating node does not immediately apply the change to its InnoDB storage engine. Instead it builds a write-set — the set of rows the transaction modified, plus a certification key for every affected primary key, unique index, and foreign-key reference. That write-set is broadcast through the Group Communication System (GCS), which assigns it a global sequence number (seqno) and delivers it to every node in one identical total order. Each node then independently checks the write-set’s keys against the keys of every other write-set that was ordered in the same certification window but not yet applied locally. Because the input (the ordered stream of write-sets) and the algorithm are identical everywhere, every node reaches the same verdict with no acknowledgment round-trip: certification is voteless consensus.

Two properties fall directly out of this design:

• The verdict is positional, not chronological. A transaction that a client committed “first” in wall-clock time loses to another transaction that received a lower seqno in global order. The lower-seqno write-set certifies and applies; the higher one, if it touches an overlapping key, is aborted. This is why the winner of a concurrent-write conflict is decided by GCS ordering, not by which application called COMMIT first.
• Commit latency includes a network round trip. The originating client connection blocks from the moment it sends COMMIT until the write-set is ordered and certified, so the group-communication round-trip time is a floor on write latency that no amount of wsrep_slave_threads tuning can remove.

Figure: the certification decision path from COMMIT to apply or rollback.

The certification cache — the set of recently ordered write-set keys each node checks against — is the working memory of this process. It is bounded, and its depth is governed by flow control: if any node falls behind applying certified write-sets, it pauses the whole group so the cache stays consistent and no node certifies against a stale view.

Prerequisites & Environment Requirements #

Certification behaves predictably only when the fundamentals below are already correct on every node. Verify them before tuning any certification parameter.

• Software: MariaDB 10.6 LTS through 11.x with the bundled Galera 4 provider (libgalera_smm.so). All nodes must run the same provider protocol version — a mixed-version group can disagree on certification-key encoding and fail to form a Primary Component.
• Every table has an explicit primary key. Certification keys are derived from primary and unique keys. A table without a primary key forces Galera to synthesize a key, which is coarser, raises the false-conflict rate, and — with the wrong setting — can let divergence pass undetected. Enforce this in schema review, not at runtime.
• Network ports open bidirectionally between all members: TCP 4567 (GCS group communication) and 4568 (IST). Certification depends on the ordered GCS stream, so packet loss or asymmetric routing on 4567 directly inflates commit latency. Getting these rules right is covered in Network Security & Firewall Rules for Galera.
• A stable node identity block — wsrep_node_name, wsrep_node_address, and the provider-options string — belongs in a version-controlled drop-in, as described in the wsrep.cnf configuration deep dive.
• A monitoring account with USAGE and access to SHOW GLOBAL STATUS, so the verification probes below can run without elevated privileges.

Step-by-Step: Making Certification Observable and Predictable #

Each step explains the reasoning, because certification problems rarely fail at configuration time — they surface hours later as a retry storm or a throughput cliff.

1. Confirm every table can produce a clean certification key #

Before you touch any parameter, find the tables that force synthetic keys. Query the information schema for base tables that lack a primary key:

SELECT t.table_schema, t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.table_constraints AS c
  ON c.table_schema = t.table_schema
  AND c.table_name  = t.table_name
  AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
  AND c.constraint_name IS NULL;

Every row this returns is a table whose UPDATE/DELETE write-sets certify on a synthesized key. Add a primary key before tuning anything else — no parameter compensates for a schema that cannot express row identity.

2. Establish a baseline of the certification counters #

Certification exposes its behavior through wsrep_ status variables. Capture a baseline under normal load so you can tell a genuine regression from routine variation:

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',    -- avg parallelism available to apply threads
  'wsrep_cert_index_size',       -- keys currently held in the certification index
  'wsrep_flow_control_paused',   -- fraction of time the group was paused for flow control
  'wsrep_local_recv_queue_avg'   -- avg depth of the received-but-unapplied queue
);

wsrep_local_cert_failures and wsrep_local_bf_aborts are cumulative counters — their rate of change, not their absolute value, is the signal. wsrep_cert_deps_distance tells you how many write-sets can be applied in parallel on average; a value near 1 means the workload is effectively serial and adding apply threads will not help.

3. Start apply parallelism conservatively, then scale #

wsrep_slave_threads controls how many write-sets a node applies in parallel. Higher parallelism drains the receive queue faster and keeps flow control from engaging, but Galera only applies non-conflicting write-sets concurrently, so parallelism above the natural wsrep_cert_deps_distance of your workload buys nothing. Begin at 1 for a clean baseline, then raise it while watching the receive queue:

[mysqld]
wsrep_slave_threads = 4

Restart is not required for this variable — set it live with SET GLOBAL wsrep_slave_threads = 4; and then persist it to the drop-in.

4. Decide the non-primary-key policy deliberately #

wsrep_certify_nonPK (ON by default) tells Galera to synthesize a certification key for tables without a primary key so their write-sets can still be certified:

[mysqld]
wsrep_certify_nonPK = ON

Leave it ON. Turning it OFF does not make those tables faster in any safe way — it makes their UPDATE/DELETE statements skip certification, which is exactly how two nodes silently diverge. Treat the presence of tables that rely on this setting as a schema debt to pay down (step 1), not as a knob to disable.

5. Size the provider options for your latency budget #

The low-level certification behavior — parallel-apply strategy and flow-control depth — lives in the wsrep_provider_options string. The single most impactful certification-side option is optimistic parallel apply:

[mysqld]
wsrep_provider_options = "cert.optimistic_pa=YES; gcs.fc_limit=64; gcs.fc_factor=0.8"

cert.optimistic_pa=YES lets apply threads begin work on write-sets before the certification interval fully closes, cutting apply latency when your workload rarely conflicts; on a heavily-conflicting workload it can increase rework, so measure both ways. The full precedence rules, syntax, and low-latency tuning of this string are covered in configuring wsrep_provider_options for low latency.

Parameter Deep-Dive #

These are the knobs that most directly shape certification throughput, conflict rate, and commit latency. Values are production starting points to tune against measured load, not universal constants.

wsrep_retry_autocommit only rescues single-statement autocommit transactions — a multi-statement transaction that loses certification is always returned to the application as a deadlock, which is why application-level retry (below) is mandatory rather than optional.

Verification & Health Checks #

Certification is healthy when conflict counters stay flat under normal load and flow control almost never engages. Check the whole picture in one query:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'wsrep_local_state_comment',   -- must be 'Synced'
  'wsrep_flow_control_paused',   -- should stay well below 0.05
  'wsrep_local_cert_failures',   -- watch the delta, not the total
  'wsrep_local_bf_aborts',       -- watch the delta, not the total
  'wsrep_cert_deps_distance',    -- apply parallelism available
  'wsrep_local_recv_queue_avg'   -- should sit near your apply-thread count or below
);

Interpretation: wsrep_flow_control_paused is the fraction of the interval the node spent paused; anything above 0.05 (5%) means the certification/apply pipeline is a bottleneck and every writer is being throttled. A steadily climbing wsrep_local_bf_aborts delta means real key contention — application transactions are colliding on hot rows.

A minimal Python probe makes the same check enforceable in CI and orchestration. It targets Python 3.9+ and PyMySQL, and explicitly handles the two wsrep conflict codes an automation caller must expect — 1213 (deadlock / certification failure) and 1205 (lock wait timeout):

import sys
import pymysql
from pymysql.constants import ER

FC_PAUSE_MAX = 0.05  # alert threshold for flow-control pause ratio

def check_certification(host: str, user: str, password: str) -> bool:
    wanted = (
        "wsrep_local_state_comment",
        "wsrep_flow_control_paused",
        "wsrep_local_recv_queue_avg",
    )
    try:
        conn = pymysql.connect(host=host, user=user, password=password,
                               connect_timeout=5, read_timeout=5)
    except pymysql.err.OperationalError as exc:
        print(f"[FAIL] {host}: cannot connect: {exc}", file=sys.stderr)
        return False

    try:
        status = {}
        with conn.cursor() as cur:
            for var in wanted:
                cur.execute("SHOW GLOBAL STATUS LIKE %s", (var,))
                row = cur.fetchone()
                status[var] = row[1] if row else None
    except pymysql.err.OperationalError as exc:
        code = exc.args[0]
        if code in (ER.LOCK_DEADLOCK, ER.LOCK_WAIT_TIMEOUT):  # 1213, 1205
            print(f"[RETRY] {host}: transient wsrep conflict {code}", file=sys.stderr)
        else:
            print(f"[FAIL] {host}: {exc}", file=sys.stderr)
        return False
    finally:
        conn.close()

    synced = status["wsrep_local_state_comment"] == "Synced"
    fc_ok = float(status["wsrep_flow_control_paused"] or 0.0) < FC_PAUSE_MAX
    ok = synced and fc_ok
    print(f"[{'OK' if ok else 'FAIL'}] {host}: {status}")
    return ok

if __name__ == "__main__":
    healthy = check_certification(sys.argv[1], "monitor", "monitor-pass")
    sys.exit(0 if healthy else 1)

Automation Integration #

Because certification failures surface as ordinary deadlock errors, every automation layer that writes to Galera needs a conflict-aware strategy rather than a naive “assume the write succeeded” path.

• Application retry decorator (Python): wrap every write transaction so a certification abort is retried with exponential backoff and jitter. The Python Database API Specification v2.0 requires a rollback after any error, which aligns with Galera’s need to reset session state after an abort:

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

RETRYABLE = {1213, 1205}  # cert-failure/deadlock, lock-wait timeout

def retry_on_conflict(attempts=4, base=0.05):
    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()
                    if exc.errno in RETRYABLE and n < attempts - 1:
                        time.sleep(base * (2 ** n) + random.uniform(0, base))
                        continue
                    raise
        return wrapper
    return decorator

• CI/CD schema gate: run the primary-key query from step 1 as a pipeline check and fail the build if any migration introduces a table without a primary key, so synthetic-key certification never reaches production.
• Ansible: template wsrep_slave_threads and the wsrep_provider_options certification flags from inventory variables, keeping the rendered drop-in idempotent so a re-run never needlessly restarts a healthy node — the discipline covered in Automating Node Provisioning with Ansible.
• Continuous telemetry: scrape wsrep_flow_control_paused, wsrep_local_cert_failures, and wsrep_local_bf_aborts on an interval and alert on their deltas, wiring the probe into the broader pattern in monitoring Galera cluster state with Python and automated node health monitoring.

Troubleshooting #

Symptoms below are specific to certification, each with the exact next action.

WSREP: certification failure ... this transaction has been aborted in the error log, and the client got a 1213 deadlock. Two transactions on different nodes modified an overlapping key and the higher-seqno one lost. This is normal under concurrent writes to hot rows — the fix is not a server setting but an application retry (see the decorator above) plus reducing contention on the hot key. A rising wsrep_local_bf_aborts delta confirms this is genuine key contention.

Commits are slow and wsrep_flow_control_paused sits above 0.1. A node cannot apply certified write-sets fast enough, so it is halting the whole group. Raise wsrep_slave_threads toward wsrep_cert_deps_distance, confirm the slow node shares a low-latency path with its peers, and check for a single lagging node (often on a slower link or disk) that is the true bottleneck.

wsrep_local_cert_failures climbs steadily even under light load. Look for tables without primary keys forcing coarse synthetic keys (run the step 1 query), or for long-running multi-statement transactions holding a wide key range. Break large transactions into smaller ones so each certifies against a narrower key set.

Throughput improved after setting wsrep_certify_nonPK=OFF, but nodes now report different row counts. Disabling non-primary-key certification let UPDATE/DELETE write-sets on keyless tables skip conflict detection and diverge. Set it back to ON, then reconcile the divergence by resyncing the affected node with a full SST, as covered in Initial Data Synchronization Methods.

A newly joined node triggers a burst of bf_aborts right after reaching Synced. The joiner briefly certified against an incomplete view while catching up. Confirm it reached Synced before the load balancer routed writes to it; gate traffic on wsrep_local_state_comment=Synced, as sequenced in graceful node join and leave procedures.

Related #

• MariaDB Galera Core Architecture & Fundamentals — the parent guide to components, the write-set lifecycle, and baseline parameters
• Understanding Galera Synchronous Replication — the synchronous commit path certification sits inside
• How Galera Handles Concurrent Writes in Multi-Master — why global ordering decides the winner of a conflict
• Configuring wsrep_provider_options for Low Latency — tuning the certification and flow-control provider flags
• Designing Multi-Master Topologies — how node placement changes the certification conflict rate