Hot-row contention

The guarantee: correct by default

Concurrent writes to the same row have a read-modify-write dependency: each must see the previous committed value. Twill DB enforces snapshot isolation with first-committer-wins — if two transactions modify the same row concurrently, the first to commit wins and the other is rejected with a write-conflict error rather than silently overwriting it. A lost update is never reachable; the cost of contention is latency and the occasional retry, never wrong data.

Lever 1 — retry the conflict (correct, just slower)

A naive contended increment will, under concurrency, sometimes return a write-conflict (ENGINE_ERR_CONFLICT / SQLSTATE 40001-style serialization failure). The fix is a short retry loop: on conflict, re-run the statement — it re-reads the freshly committed value and reapplies the change. This requires nothing structural and is safe under any SQL.

-- The contended write. Correct under concurrency; may need a retry.
UPDATE counter SET n = n + 1 WHERE id = 1;

// Retry the first-committer-wins conflict; the loser re-reads and reapplies.
function increment(db: Database) {
  for (;;) {
    try { db.exec("UPDATE counter SET n = n + 1 WHERE id = 1"); return; }
    catch (e) { if (isConflict(e)) continue; throw e; }
  }
}

This is the floor: because correctness is guaranteed regardless of SQL quality, the worst outcome for any tool is "slow," never "wrong." Retry handles the occasional race; the next two levers handle sustained contention.

Lever 2 — shard the hot row (turn one row into many)

If one row is hammered hard enough that retries pile up, split it so writers stop colliding. Both patterns convert "same row" into "different rows," which commit in parallel.

Sharded counter

Keep N sub-counter rows; each writer increments one shard, reads sum across all of them.

CREATE TABLE counter_shards (
  counter_id TEXT    NOT NULL,
  shard      INTEGER NOT NULL,           -- 0 .. N-1
  n          INTEGER NOT NULL,
  PRIMARY KEY (counter_id, shard)
);

-- WRITE: pick a shard (app-side; spread writers across N different rows)
UPDATE counter_shards SET n = n + 1 WHERE counter_id = 'video:42' AND shard = 7;

-- READ: aggregate the shards into the logical total
SELECT SUM(n) AS total FROM counter_shards WHERE counter_id = 'video:42';

Event log + aggregate on read

Never update a count at all: append one immutable row per event (always a different row, never contends) and fold them on read.

CREATE TABLE counter_events (
  id         INTEGER PRIMARY KEY,
  counter_id TEXT    NOT NULL,
  delta      INTEGER NOT NULL
);

-- WRITE: a pure INSERT — never contends
INSERT INTO counter_events (counter_id, delta) VALUES ('video:42', 1);

-- READ: fold the events (roll up into a snapshot row periodically if it grows)
SELECT SUM(delta) AS total FROM counter_events WHERE counter_id = 'video:42';

Lever 3 — route the irreducible outlier to Postgres

One shape cannot be helped by retry or sharding: same-row, same-database, high-rate, latency-critical writes. The contention is genuinely sequential, batching cannot add lanes, and each contended handoff pays the network round-trip. This is the mandatory safety net: move that one tool to a coupled Postgres and keep the disaggregated engine for everything else. The platform is not all-or-nothing — benchmark the boundary once, then place each tool on the correct side.

Measure it: the contention metrics

You don't have to guess which tool is a hot-row outlier — the engine surfaces the signal. Every write serializes through one write lane per database, and the lane counts how often a writer had to wait behind another (a serialized handoff) and for how long. Pull the counters in-band over pgwire:

SHOW twill.stats;   -- or: SELECT * FROM twill_stats;

The counters are cumulative — sample them twice and take the difference. Two derived numbers drive the decision: the handoff rate (handoff / acquire) and the mean wait (wait_us / handoff). A healthy tool shows acquisitions climbing while handoffs stay flat. A hot row pushes both the handoff rate and the mean wait up.

Runbook. When a tool's handoff rate and mean wait stay high across several windows under real load (a single spike is not a flag): first apply Lever 2 (shard / event-log) and re-measure — most red workloads go green there. If it stays contended, apply Lever 3 and route only that tool to Postgres; every other tool is untouched. The decision is per-tool and reversible — if the contention later falls, the same metrics show it and the tool routes back.

Diagnose by dependency, not by rate

A million inserts a second into a million distinct rows is not a hot-row problem — that is throughput, solved by more lanes (separate databases) and batching. A thousand updates a second to one row is a hot-row problem at any volume. Diagnose by whether writes depend on each other's value, never by request rate. Spreading independent write streams across separate databases gives each its own writer lane and keeps unrelated writes from ever queuing behind each other.

Related