I keep watching the same movie. A component gets added for speed. Speed needs durability, so a persistence mechanism gets bolted onto it. The bolt-on is what fails. And the proposed fix is another component.
Whenever I find myself in that audience again, I run a private exercise: design the system from scratch with the dullest tools I trust, and log every decision — not just what I picked, but what each option would have poisoned me with. Usually the log stays in my notes. This time I'm publishing it, because the design ended up small enough to be embarrassing.
The problem. One synchronous HTTP request arrives. Split it into N independent segments — say 100. Process them in parallel on a fleet of workers, where one segment costs seconds of real compute (machine translation is my running example; any splittable job fits). Return whatever finished by the deadline, mark the rest timed out. Bill for what was done.
The rule I imposed on myself. Every component must justify its existence in one sentence naming the condition that requires it. No sentence, no component.
Stack: Java 21 (virtual threads) on Tomcat, MySQL 8 / InnoDB. Numbers like "100 workers" and "500 ms" are design values, not benchmarks.
Decision 1 — Pull, not push
The push design: a dispatcher assigns segments to workers. To assign, it must know which workers are idle — so somewhere there is a live ledger of worker state. That ledger wants to be fast and shared, and that is exactly the shape of the in-memory KV store from the opening movie. It doesn't get added out of stupidity; it gets added because push requires it.
The pull design: workers come to the work. An idle worker asks "anything for me?" — and the ledger doesn't get replaced by something better. It stops existing. Nobody needs to know who is idle.
Traded: workers now poll, and a poll can come back empty. Got: one HA-critical component deleted before it was born.
Rule: the cheapest component is the one whose job disappears.
Decision 2 — The queue is a table
What does queue middleware actually give you? Strip away the features you won't use, and the essence is one primitive: exclusive takeout — N consumers, each item handed to exactly one. SQL has had that primitive for years:
SELECT id FROM segments
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
SKIP LOCKED is the whole trick: a row someone else is holding is skipped, not waited on, so a hundred workers grab a hundred different rows with zero lock contention.
The objection writes itself: "MySQL will be slower than a purpose-built queue." Per operation, true. And irrelevant — each segment costs seconds of compute, so even 100 workers in a tight loop ask the database for a few hundred tiny index operations per second. InnoDB's ceiling is orders of magnitude above that. The bottleneck is the work, not the takeout. A throughput objection has to name the actual bottleneck, or it's an aesthetic.
Traded: headline RPS I will never use. Got: transactions, durability, and a storage system I already operate.
Decision 3 — Lease, not held lock
This is the decision I got wrong first.
The held-lock design: claim a row with FOR UPDATE SKIP LOCKED and keep the transaction open for the whole job. It has a beautiful crash story — worker dies, connection drops, lock releases itself, the next worker picks the row up. No flags, no janitor. I almost kept it for the elegance alone.
Then the poisons. A transaction held open for tens of seconds makes InnoDB's history list grow and purge lag behind. Concurrency gets capped by connection count — free back-pressure if you're feeling generous, your connection pool doing scheduling if you're not.
And the killer: cleanup. When a request is cancelled and you DELETE ... WHERE request_id = ?, that statement blocks on every in-flight row. SKIP LOCKED is a SELECT modifier; DELETE has no such escape.
The lease design: claim in a millisecond transaction.
START TRANSACTION;
SELECT id INTO @id FROM segments
WHERE status = 'pending'
OR (status = 'processing' AND lease_until < NOW())
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;
UPDATE segments
SET status = 'processing', owner_id = ?, lease_token = ?, -- fresh UUID
lease_until = NOW() + INTERVAL 30 SECOND
WHERE id = @id;
COMMIT; -- lock held for milliseconds, not for the job
While the job runs, nothing is locked. "Processing" is just data — which is why any other transaction, cleanup included, can act on the table without waiting.
Lease has its own poison and it isn't small: at-least-once. A slow worker whose lease expires gets its segment re-claimed by someone else, and now two workers hold the same job. So every write-back must be fenced:
UPDATE segments SET status = 'done', result = ?
WHERE id = ? AND lease_token = ?; -- 0 rows: you lost the lease; drop your result
And expired leases need reaping — which the claim query above already does, in its WHERE clause.
Traded: exactly-once, and the elegant auto-release. Got: short transactions, cleanup that never blocks, and crash recovery expressed as a query condition instead of a connection artifact.
Rule: pick the poison you can operate, not the story that sounds elegant.
Decision 4 — Fan-in by polling, not notification
Now the collecting side. The request thread is parked on Tomcat, waiting for 100 results or a deadline.
I wrote the event-driven version first. Workers call back the owning node; a CountDownLatch(N) wakes the request thread the instant the last segment lands; not a single wasted read. As Java it's lovely — latch.await(deadline) answers "all done, or timed out?" in one blocking call, virtual threads make one-thread-per-request free, and the latch's happens-before even hands you memory visibility on the result slots for nothing.
Then the topology bill arrives. The worker must reach a specific instance behind the load balancer. That means instance addressing, a security-group edge from workers to app nodes, service discovery for tasks whose IPs churn — and an infrastructure team that, correctly, hates every item on that list. (Postgres would dissolve this with LISTEN/NOTIFY — the database itself routes the wake-up to whoever is listening — but I'm on MySQL, and adopting a different database to avoid a polling query fails my own one-sentence test, badly.)
The polling version: every 500 ms, the owning node runs one lock-free, indexed SELECT —
SELECT index_no, status, result FROM segments WHERE request_id = ?;
— and counts terminal rows. The waste is a range scan over ~100 rows, twice a second. The request's total latency is dominated by its slowest segment, which is seconds; 500 ms of granularity is noise on that tail.
Here's what I didn't expect: choosing polling deleted things in bulk. The latch — gone. The callback endpoint — gone. The direct network path, the SG rule, the discovery mechanism — gone. "Reduce the hops," taken seriously, terminated at the database is the only rendezvous.
Traded: sub-second wake-up and a satisfying event-driven diagram. Got: a topology with no arrows that bypass the load balancer.
Rule: "no wasted work" and "no extra paths" are different goals. When they conflict, price each path by what it costs to operate, not what it costs to run.
Decision 5 — Cancellation stops the future, not the present
Requests get a master row: requests(id, status, deadline, ...) with active / cancelled / completed. A user reset is one statement — UPDATE requests SET status = 'cancelled' WHERE id = ? — and because the claim query joins on r.status = 'active', cancelled requests instantly stop being claimable. That includes expired-lease re-claims: no zombie segments of a dead request coming back to life.
What cancellation deliberately does not do is preempt in-flight segments. A running segment has already spent most of its cost; killing it mid-flight would save a tail while requiring cancellation plumbing all the way into the compute call. So in-flight work runs to completion and writes through the fence. Yes, there's a race — a segment claimed in the same instant as the cancel still runs. That waste is bounded to one claim window per worker, and I accept it.
Rule: cancellation is cheap where work hasn't started and expensive where it has. Put the boundary exactly there.
Decision 6 — Settlement is a batch, because billing is a business function
Someone has to count input/output characters, write the metering record, and delete the rows. The hot path is the wrong someone. Its SLA is "fast, and losing an in-flight request in a crash is survivable." Billing's SLA is "accurate, auditable, never lost." Two SLAs don't share a process.
So a batch loops forever, claiming settled requests with the same trick as Decision 2, one level up:
SELECT r.id FROM requests r
WHERE r.status IN ('completed','cancelled')
AND NOT EXISTS (SELECT 1 FROM segments s
WHERE s.request_id = r.id
AND s.status = 'processing'
AND s.lease_until >= NOW())
ORDER BY r.id LIMIT 1
FOR UPDATE SKIP LOCKED;
The NOT EXISTS (live lease) is the handshake with Decision 5: a cancelled request isn't settled until its in-flight stragglers finish or their leases die. Then, in one transaction: sum the done segments, insert the metering record (idempotent on request_id), delete segments and request. Record first, delete second; a crash in between just re-runs.
The billing policy fits in one clause:
... FROM segments WHERE request_id = ? AND status = 'done'
Cancelled requests still pay for finished segments — cancelling is the user's prerogative, not a refund mechanism. If that policy ever changes, it changes in one place.
A property I only noticed after drawing the sequence diagram: you can stop the batch server entirely and translation keeps flowing. That is what "different SLA" looks like when it's real.
Decision 7 — The in-memory itch
Last temptation: the segments table is transient by design — shouldn't it live in RAM?
MySQL's MEMORY engine fails mechanically before it fails philosophically: table-level locks only (there goes SKIP LOCKED), no TEXT columns (there go the results), and a restart wipes every in-flight request. Disqualified in three sentences.
And the itch itself is misdiagnosed. A small, hot InnoDB table effectively is in memory — the buffer pool serves the reads; disk sees sequential redo writes. If measurement ever shows commit fsyncs as the bottleneck, the honest lever is innodb_flush_log_at_trx_commit = 2 — but that variable is instance-global, and the metering table must stay durable, so "fast segments, durable billing" would force a second MySQL instance. Adding a component to accelerate something that isn't the bottleneck: rejected by the same test as everything else.
Rule: before making storage faster, check whether storage is in the top two costs at all.
The test, and when it flips
Final inventory: Tomcat, workers, one MySQL, one batch. MySQL passes the one-sentence test three times over — it is the exclusivity primitive (Decision 2), the fan-in channel (Decision 4), and the source of truth for billing (Decision 6). One component, three jobs. Nothing else in the candidate pile could produce a sentence.
None of this argues that queues, caches, and notification systems are bad. It argues they are answers to conditions: a worker fleet large enough that empty polls get expensive; an asynchronous outer API that makes pending state worth persisting independently of any node; a dequeue rate that actually approaches the store's ceiling. When a condition shows up, add the component — and write the sentence down, so future-you knows when to delete it again.
Until then, the row lock you already own is the best middleware you'll never install.
Built with Claude (Opus).
Top comments (0)