The first time a Galera cluster genuinely confused me, the application team had just gone active-active across two MariaDB nodes behind a load balancer. Reads were fine. Writes were fine. Then a nightly batch job started running against both nodes at once and the logs filled with deadlock errors on transactions that, as far as InnoDB was concerned, had already done all their work. Those were not InnoDB deadlocks. They were Galera certification conflicts, and that week permanently changed how I design anything that runs on MariaDB Galera Cluster.
This post is the explanation I wish I had back then: what certification actually is, why the error arrives at COMMIT rather than at the statement that touched the contested row, which status counters prove it is happening, and the design patterns that keep conflicts rare instead of chronic.
How Galera certification actually works
Galera is a certification-based replication layer, and it is optimistic. Your transaction executes entirely on the node you connected to, holding ordinary InnoDB row locks, with no cluster-wide coordination while it runs. Only at COMMIT does Galera step in: the node packages everything the transaction changed into a writeset — essentially the affected row keys plus the data needed to replay the change — and broadcasts it to every node in the cluster.
Each node then certifies the writeset against a certification index built from recently committed writesets. The question is simple: did this transaction touch rows that another concurrently committed transaction also changed? If not, the writeset is certified, enters the global total order, and is applied everywhere, including on the node where it originated. If yes, exactly one of the conflicting transactions wins. The winner is whichever entered the global order first. The loser is told to roll back.
Two properties fall out of this design. First, every node agrees on the commit order, which is how Galera can promise that committed data is identical everywhere. Second, the losing transaction finds out at the very end, after doing all of its work. That is why the failure surfaces at COMMIT, and why it masquerades as a deadlock: MariaDB maps it to ER_LOCK_DEADLOCK, error 1213, "Deadlock found when trying to get lock; try restarting transaction."
What a conflict storm looks like
The classic incident goes like this. Two application servers are pinned to two different Galera nodes. A nightly job on both recomputes usage counters for the same tenants. Each transaction takes local locks without complaint. When the jobs overlap, whichever node certifies first wins, and the other node's transaction dies at COMMIT with error 1213. The application retries blindly, sometimes succeeds, sometimes collides again, and cluster latency spikes because aborted work is pure waste: the loser burned CPU, lock manager time, and network round trips for nothing.
The cluster reports all of this honestly if you know where to look. The originating node counts its lost transactions in wsrep_local_cert_failures. A sibling counter, wsrep_local_bf_aborts, records the mirror image: a local transaction aborted because an applier thread needed the rows to apply a certified writeset. Galera calls that a brute force abort — certified writesets always win against local locks.
SELECT variable_name, variable_value
FROM information_schema.global_status
WHERE variable_name IN (
'wsrep_local_commits',
'wsrep_local_cert_failures',
'wsrep_local_bf_aborts',
'wsrep_local_replays'
);Sample these on an interval and compute failures as a fraction of wsrep_local_commits. Occasional conflicts are the normal price of optimistic replication, and I would not alert on single digits. A chronic ratio, or a step change after a deploy or a new cron job, means two writers really are racing on the same rows — find them before your peak traffic does. More than one shop has learned from this ratio that their "active-active" topology was actually "active-active on the same fifty hot rows."
Why the same rows on two nodes always lose
The window of vulnerability is the replication gap: the time between a transaction committing on node A and its writeset being certified and applied on node B. On a healthy LAN cluster that window is milliseconds, which is why low-contention workloads never notice it. But probability compounds. If both nodes steadily update the same inventory row, the same account balance, or the same status flag, every write on node B that overlaps an in-flight write from node A becomes a coin flip at commit time.
The uncomfortable conclusion is that Galera multi-primary is really multi-primary with certification boundaries. The cluster lets you write anywhere, but it cannot make two concurrent writers to the same row both succeed. A single InnoDB instance settles that race with the row lock: the second writer waits, then commits after the first, and both succeed. Across nodes there is no shared lock manager to serialize them, so Galera's answer is harsher — one of the two is thrown away at certification. What multi-primary actually buys you is failover symmetry and write scaling across disjoint data, not magic concurrency on shared rows.
Designing writes so conflicts stay rare
The fixes are architectural, not tunable. There is no Galera setting that makes hot-row multi-node writes safe; you change how the application writes.
- Route writes by affinity. Pin each tenant, shard, or account range to one node at a time and use the remaining nodes for reads and failover. You keep multi-primary's operational benefits without paying the conflict tax.
- Make error 1213 retryable everywhere. Any transaction that can hit a certification conflict must be idempotent and retried with backoff. The wsrep_retry_autocommit setting retries single autocommit statements automatically, but it does nothing for explicit multi-statement transactions; those retries are on you.
- Shard hot counters. Replace one global counter row with a handful of shard rows and sum them at read time. Ten shards drop the collision probability roughly tenfold.
- Shorten transactions. Every statement and every network round trip inside an open transaction widens its conflict window. Build the data first, then write it.
- Single-writer batch jobs. Pin bulk jobs to one node. Serialization is cheaper than abort-and-retry storms.
CREATE TABLE counter_shards (
counter_name varchar(64) NOT NULL,
shard tinyint unsigned NOT NULL,
value bigint NOT NULL DEFAULT 0,
PRIMARY KEY (counter_name, shard)
);
INSERT INTO counter_shards (counter_name, shard, value)
VALUES ('downloads', FLOOR(1 + RAND() * 10), 1)
ON DUPLICATE KEY UPDATE value = value + 1;
SELECT counter_name, SUM(value) AS total
FROM counter_shards
WHERE counter_name = 'downloads'
GROUP BY counter_name;None of this is a workaround for a broken product; it is the intended operating model. Once writes have affinity, certification conflicts fall to background noise, and what remains of multi-primary does what you actually wanted: rolling maintenance without downtime and instant, symmetric failover targets.
When conflicts are a symptom, not the disease
Treat a rising conflict ratio as telemetry about your data model. The times I have chased one down, the cause was almost always one of three things: a new feature that writes a shared row on every request, a second writer introduced by accident (a health check doing an UPDATE, a migration script pointed at two nodes), or cron jobs scheduled identically on every app server. All three are cheap to fix once wsrep_local_cert_failures tells you they exist. It is also worth remembering that single-node engines solve the same anomaly class differently — a single PostgreSQL instance makes the second writer wait on a row lock at its default isolation, and raises serialization failures under the stricter REPEATABLE READ and SERIALIZABLE levels — and the engine comparison pages map those differences if your stack spans both worlds.
Watching MariaDB with MonPG (coming soon)
A disclosure that belongs in every post in this series: MonPG is built for PostgreSQL today, and MariaDB support is coming soon rather than shipped. Galera telemetry sits high on the design list — certification failure ratio trended against commit rate, brute-force aborts surfaced as their own series, flow-control pauses pinned to the write bursts that triggered them. The MariaDB monitoring (coming soon) page carries the current status. Until it ships, the counters in this post plus anywhere durable to trend them will get you most of the way. And if PostgreSQL is also in your stack, that side is covered today — start with the PostgreSQL overview.