The flakiest test in your suite is fighting over a database row

The 3pm failure nobody can reproduce

Here's a story you've lived through. A pull request goes red on CI. The failing test is test_user_can_update_profile. The engineer reads it, shrugs, and hits rerun. Green. Merged. Nobody writes anything down.

Three days later the same test fails on a different branch, in a different PR, and the on-call engineer spends forty minutes convinced they broke profile updates. They didn't. The test broke itself — or more precisely, some other test broke it by leaving a row behind in a table they both touch.

This is shared database state flakiness, and it's the most under-diagnosed root cause class I run into. It doesn't show up as a network timeout or a sleep() race. It shows up as a test that passes alone, passes in isolation, passes on your laptop, and fails roughly one run in twenty on CI for reasons that look like black magic. It isn't magic. It's a leaked transaction, a truncated-but-not-really table, or two tests racing for the same primary key.

Why shared state is different from other flakiness

Most flaky-test advice targets timing: add a proper wait, mock the clock, stub the network. Useful, but it misses this class entirely. Shared database state has three properties that make it uniquely nasty.

It's order-dependent. The test only fails when Test B runs after Test A and Test A left something behind. Change your test ordering — parallel sharding, a new file, a randomized seed — and the failure appears or vanishes. That's why it correlates with "we added tests" or "we turned on parallelism," not with any change to the failing test itself.

It's environment-sensitive. Locally you run one test file at a time against a fresh Docker Postgres. On CI you run four shards against one shared instance. The bug was always there; CI just gave it the conditions to fire.

It's invisible in the stack trace. The exception points at the victim test, never the culprit. UniqueViolation: duplicate key value violates unique constraint "users_email_key" tells you which row collided. It does not tell you which other test inserted alice@example.com and forgot to clean up.

That last property is what makes this so expensive to debug by hand. You're not looking at the failing test. You're looking for a test you haven't identified yet, somewhere in a suite of three thousand.

The four ways it usually happens

In rough order of how often I see them:

1. Leaked transactions. A test opens a transaction, asserts, and the rollback either never fires or fires on a different connection than the one that did the writes. Common with connection pools where the test framework and the app grab different connections.
2. Truncate/cleanup that misses tables. Someone wrote a cleanDatabase() helper listing tables to truncate. A migration added a new table. Nobody updated the helper. That table now accumulates rows across tests.
3. Hardcoded fixtures with unique constraints. Two tests both insert a user with email test@test.com. Run them in the same transaction scope or the same un-cleaned DB and the second one explodes.
4. Sequence and ID assumptions. A test asserts expect(user.id).toBe(1). Fine in isolation. On a shared DB where another test already inserted three users, id is 4, and the assertion fails for reasons that have nothing to do with the code under test.

None of these are exotic. They're the natural entropy of a test suite that grew faster than its isolation strategy.

Reproduce it before you fix it

The single most useful move: stop trusting that a test passes in isolation. Run it in a randomized order and see what breaks. Most frameworks support this.

Pytest with pytest-randomly:

# Run with a fixed seed so a failure is reproducible
pytest --randomly-seed=12345

# When it fails, that seed is your reproduction. Bisect from there.
pytest --randomly-seed=12345 tests/test_billing.py tests/test_users.py

RSpec ships this in the box:

# Ruby prints the seed on every run — capture it
rspec --seed 44921

# Reproduce the exact order
rspec --seed 44921 spec/models/invoice_spec.rb spec/models/user_spec.rb

Jest, if you shard, wants a deterministic worker layout:

// jest.config.js
module.exports = {
  // Make ordering deterministic so a flake is reproducible
  testSequencer: '<rootDir>/test/sequencer.js',
  maxWorkers: 4,
};

The goal is a command you can paste into a ticket that fails every single time. Once you have that, the fix is almost boring. The hunt was the hard part.

Fix it at the isolation layer, not the test

Resist the urge to patch the victim test. If you add a DELETE FROM users at the top of test_user_can_update_profile, you've treated the symptom and left the culprit free to poison the next victim. Fix isolation for the whole suite instead.

The cleanest pattern for most SQL databases is transaction-per-test with a rollback. Every test runs inside a transaction that never commits.

# conftest.py — pytest + SQLAlchemy
import pytest

@pytest.fixture
def db_session(engine):
    connection = engine.connect()
    transaction = connection.begin()
    session = Session(bind=connection)

    yield session

    # Roll back everything the test did, no matter what
    session.close()
    transaction.rollback()
    connection.close()

The catch: your application code has to use the same connection as the fixture, or its writes commit outside the rolled-back transaction and you're back where you started. If your app opens its own pool, transaction rollback silently does nothing and you'll swear the fixture is broken. It isn't — it's just not seeing the app's writes.

When transaction rollback isn't feasible (multiple connections, tests that need to test commit behavior, or async workers), fall back to truncating every table between tests — and generate the table list from the schema so a new migration can't quietly leave a table uncleaned:

-- Derive the list; don't maintain it by hand
SELECT tablename FROM pg_tables
WHERE schemaname = 'public'
  AND tablename NOT IN ('schema_migrations');

And stop hardcoding fixture values that unique constraints care about. A factory with a counter or a UUID costs nothing and removes an entire failure mode:

def make_user(**overrides):
    n = next(_counter)
    return User(email=f"user-{n}@example.test", **overrides)

The detection problem is a fleet problem

Here's where individual heroics stop scaling. You can reproduce and fix one shared-state flake. But the reason it went unnoticed for weeks is that no single engineer sees the pattern. Each one just saw a red run, hit rerun, got green, and moved on. The signal — "this test fails ~5% of the time, always after the billing suite" — lives across hundreds of runs and dozens of people. No human is holding that history in their head.

That's the actual job: correlating failures across runs to separate genuine regressions from order-dependent noise. You want a system that flags test_user_can_update_profile as flaky based on its pass/fail pattern across the whole fleet, tells you it correlates with a specific shard ordering, and gives you the seed to reproduce it — instead of relying on whoever's on-call to notice. That's the flaky-test detection BuildPulse does off your existing test output, and it's how a shared-state flake stops being a monthly mystery and becomes a ticket with a repro attached. If you're wiring up the ingestion side, the JUnit XML reporting most frameworks already emit is enough to start.

The compliance angle: "just rerun it" is an audit finding waiting to happen

If you're in a SOC2 or ISO 27001 shop, your passing CI check is a change-management control. It's evidence that a change was tested before it shipped. When an engineer hits rerun on a red build and merges the green one, they've overridden a control — and if the failure was a real regression masked by shared-state noise, you shipped a defect through a control that was supposed to catch it.

Auditors don't love "the test is flaky, we rerun it." They ask the reasonable follow-up: how do you distinguish a flaky failure from a real one, and where's the record? "Vibes and the on-call engineer's judgment" is not a satisfying answer.

A disciplined approach turns this from a liability into a clean story:

• Quarantine known flakes explicitly rather than reflexively rerunning everything. A quarantined test is documented, tracked, and doesn't block merges — but the record shows which tests were quarantined and why.
• Keep the failure history so "this was a known flaky test, here's its 60-day failure pattern" is a queryable fact, not a Slack memory.
• Never let a rerun silently pass a genuine regression. The distinction between "failed because of DB state leakage" and "failed because the code is broken" has to be made deliberately, with evidence.

The difference between a mature and an immature CI process, from an auditor's chair, is whether flakiness is managed or worked around. Managed means you can produce the evidence. Worked around means someone hit rerun and hoped.

Where to start Monday

Turn on randomized test ordering in one suite. Capture the seed. When something goes red that was green yesterday with no code change, you've found a shared-state flake — and now you can reproduce it. Fix isolation at the fixture layer, not the victim test. And build the detection so the next one shows up as a ticket instead of a 3pm mystery.

Your CI signal is only worth trusting if a green check means the code works — not that someone got a favorable roll of the dice on test ordering.