The Bug That Hid Behind 227 Green Tests
Part 7 of the "Behind the Map" series — a challenge, not an algorithm: what we learned about database transactions before the first real customer arrived
Abstract
All 227 automated tests were green — and yet every review saved through what would later become the web dashboard vanished, without a trace, the next time the server restarted. This article isn't about an algorithm; it's about a challenge: how a bug can exist that a green test run simply cannot show, which three fixes we weighed against each other, and why the one we chose doesn't just patch the symptom but makes repeating the mistake structurally impossible.
1. The challenge
A database transaction is supposed to give a simple guarantee: either everything gets saved, or nothing does. Our first implementation honoured that guarantee in every single test — and still violated it in operation, for a reason no individual test could see.
The reason was a silent assumption about the state of a database connection. A transaction can only open cleanly if the connection isn't already in the middle of something. Our first architecture kept one single connection open for the entire lifetime of the process — for a command-line tool that does one job and exits, that's harmless. For a long-lived web server process that alternates between reads and writes, it isn't: a simple read (say, "show me this app's topics") left the connection in a state where the next write no longer opened a real transaction at all, only an inner checkpoint — one that got dissolved the moment the code block exited, without ever actually saving anything. The server reported success. The data had never arrived.
On the timeline, so the framing is right: this first architecture was built in May, during the product's pure command-line phase, where this exact bug was invisible. It only became reachable once a long-lived web server was added over the summer — and it was found before the public launch, during a rehearsal for a demo. It was fixed the same day. Regular operation with real payments only began two weeks later. No customer was ever affected — but the lesson from it still shapes how we build every new database operation today.
2. Approaches considered
Once the bug was understood, three paths were on the table:
| Approach | Rejected because |
|---|---|
| Keep the single connection, but replace the broken transaction-open with an explicit, direct "commit now" call | Fixes the one observed symptom in two lines of code — but leaves the actual cause standing: a single connection shared by every simultaneous request. Two requests at once could still have undone each other's work. A silent data-loss bug would have been traded for a silent data-corruption bug — no progress. |
| Check and reset the connection's state before every save, instead of changing it | Shifts the responsibility onto a rule that would have to be honoured at every current and every future call site in the code — with nothing enforcing it. The bug would return the first time someone later adds a new read. |
| One connection per unit of work, drawn from a shared connection pool | Chosen. There is then simply no long-lived, shared connection whose state could "leak" between two requests. The bug isn't fixed — it's made unreachable. |
The deciding question in evaluating these wasn't "which path fixes the observed symptom fastest," but "which path removes the class of bug, not just the one instance we happened to find." The first two options don't pass that test.
3. The conceptual solution
Instead of sharing a single connection across the server's entire lifetime, every individual unit of work — a save, a read — borrows its own connection from a shared pool for the duration of its execution and returns it afterward. Within one coherent operation (say: saving a review and recording the accompanying event for the rest of the system), the same connection is guaranteed throughout, so both steps genuinely succeed together or fail together. But between two completely independent requests, there is never a shared connection whose state could "leak" from one to the other.
4. The technical solution in detail
flowchart TD
subgraph Before["Before: one connection, whole process lifetime"]
V1["Request 1: read"] --> VC["ONE shared connection"]
V2["Request 2: write"] --> VC
VC --> VF["Transaction-open opens only an inner<br/>checkpoint instead of a real transaction —<br/>the result is discarded"]
end
subgraph After["After: one pool, one connection per unit of work"]
N1["Request 1: read"] --> P["Shared connection pool"]
N2["Request 2: write"] --> P
P -->|"lends connection A,<br/>returns it afterward"| N1
P -->|"lends connection B,<br/>returns it afterward"| N2
N2 --> NC["Real commit —<br/>connection B guaranteed to start IDLE"]
end
Two building blocks carry the fix:
- A connection pool instead of a single connection. At server startup, no single, permanent conversation with the database gets opened. Instead, a small stock of connections is kept, and every unit of work briefly borrows one and returns it afterward. Because a freshly-borrowed connection is guaranteed to start in the right state, the silent checkpoint-instead-of-transaction bug from section 1 can no longer occur at all — not because it "usually" doesn't happen, but because the precondition for it no longer exists.
- One unit of work binds exactly one connection for its full duration. If an operation saves a review and records an accompanying event, both steps run over the same borrowed connection — that's what turns two separate writes into an "both or neither" guarantee (more on this in part 8 of this series).
The second, equally important change: an earlier, parallel second implementation of the same transaction logic — untested, with no caller, but still present in the codebase — was removed outright. Two independent implementations of the same safety-critical rule are a risk of their own: fix one and forget the other, and the bug comes back somewhere else. Since then, the entire codebase uses exactly one shared implementation of the transaction boundary — every further part of the system that needs a database connection borrows the same solution instead of building it a second time.
5. The evidence
A green test run alone would never have surfaced this bug — every test took its own fresh connection, so no test could even express the sequence "read first, then write on the same connection." The necessary test had to force exactly that: read, then write, through the same connection setup the real server uses — and then check, from an independent second connection, whether the write is actually visible. Against the old code, this test fails. Against the new code, it passes. On top of that, the fix was verified live with the simplest possible proof: load twenty reviews, restart the server process, count them — all twenty were there.
6. Limits of the method
- One connection per unit of work isn't a free pass for unlimited database calls per request — every additional call to the pool costs a small amount of wait time. Operations that genuinely need several steps to happen together must deliberately bundle them into a single unit of work, or they'll run over multiple connections and lose exactly the "both or neither" guarantee. Left unchecked, one connection per unit of work is slow — the pool stays small on purpose.
- A connection pool prevents this one class of bug — it's not a substitute for a test that reproduces the actual order of real requests. Without that test, the new architecture could have been silently wrong too.
7. Conclusion
The real lesson here isn't "connection pools are good" — that's textbook knowledge. The lesson is: a green test run only proves that the tests that were written pass — not that the system is correct. Between those two sits the question of whether a test even exists that can reproduce the actual failure case. That was exactly what was missing at first — every test had its own isolated connection and simply couldn't express the sequence that triggered the bug. The fix addresses both: the architecture makes the bug unreachable, and the new test makes it visible if it ever came back anyway.
The next part stays with this exact "both or neither" guarantee — this time for two different writes that must never fall apart from each other: a review, and the message that carries it to the rest of the system. → Part 8: Two Writes That Must Never Come Apart (coming next)