Never Hold a Write Lock Across a Network Call

A Loop That Looked Efficient

The enrichment passes did the sensible thing. For each candidate row: write what we know, make the network call, write the result. Commit once at the end, because committing per row is more fsyncs and everyone knows batching is faster.

In a single-writer database, an uncommitted write holds the write lock. So the first update in that loop took the lock, and the very next statement was a network call. The lock was then held across every remaining fetch in the round — measured at roughly nine seconds for one pass, well past the configured busy timeout.

The Symptom Was in an Unrelated Feature

Nothing in the enrichment pass reported a problem. The failure surfaced somewhere else entirely: during a round, a reader pressing like or save got a locked-database error. And because the client's event handler caught errors broadly and just reloaded the shelf, the tap simply vanished — no message, no retry, the button just did not appear to work.

Three properties made this hard to find. It is intermittent, occurring only during rounds. It appears in a feature that has nothing to do with the code responsible. And the error was swallowed by a catch-all one layer away from where anyone would look.

Commit Per Row

The fix is one line moved inside the loop. Each row's update commits before its network call, so the lock is held for microseconds rather than seconds. The batching intuition was simply wrong for this shape of work: the cost being optimized was a handful of fsyncs, and the cost being paid was every concurrent writer in the process.

The general rule is worth stating without the database specifics: the duration of a lock should be bounded by computation you control, never by a remote system's response time. Any await, any request, any subprocess between acquiring and releasing turns your latency budget over to somebody else's server.

Test the Property, Not the Pass

The pass had tests and they passed, because they ran alone. The test that catches this asserts a property about concurrency: while a round is running, an ordinary write still succeeds. That is a different kind of test — it needs the pass and a writer in flight at the same time — and it is the only kind that could have found this.

A lock held across I/O is not a lock, it is an outage with a schedule. Its duration is set by a remote host, so your write availability is now a property of somebody else's uptime — and the failure will appear in whichever unrelated feature happens to write during the window.

Code

The held lock, the per-row commit, and the concurrency property test·python

# BEFORE: batched commit. The first UPDATE takes the write lock and the
# next statement is a network call, so the lock is held across the
# WHOLE round. Measured ~1.12s per resolution x 8 per round = ~9s of
# held lock, past the busy timeout.
def run_shell_pass(con, candidates):
    for row in candidates:
        con.execute("UPDATE articles SET shell_checked_at=? WHERE id=?",
                    (utcnow(), row["id"]))       # <- takes the lock
        resolved = _unwrap(row["url"])           # <- ...across THIS
        if resolved:
            con.execute("UPDATE articles SET url=? WHERE id=?",
                        (resolved, row["id"]))
    con.commit()                                 # <- released here


# AFTER: commit per row. The lock is held for microseconds. The
# batching instinct was optimizing a handful of fsyncs and paying with
# every concurrent writer in the process.
def run_shell_pass(con, candidates):
    for row in candidates:
        con.execute("UPDATE articles SET shell_checked_at=? WHERE id=?",
                    (utcnow(), row["id"]))
        con.commit()                             # <- before the network
        resolved = _unwrap(row["url"])
        if resolved:
            con.execute("UPDATE articles SET url=? WHERE id=?",
                        (resolved, row["id"]))
            con.commit()


# The test that finds it. Not 'does the pass work' -- it did. This
# asserts a PROPERTY: a writer landing mid-pass still succeeds.
def test_a_writer_survives_a_round(con, slow_network):
    with running_in_background(run_shell_pass, con, candidates):
        time.sleep(0.5)                          # land inside the round
        record_event(con, "like", article_id=1)  # must not raise
    assert store.article_state(con, 1)["liked_at"]

Exercise

Search your codebase for transactions or locks, and for each one list every call made between acquisition and release. Flag any that perform I/O — a request, a queue publish, a subprocess, a file read on a network mount. For the worst one, estimate the held duration and compare it to your database's busy timeout.

Hint

The pattern hides in loops that update a row, call out, and update again — it reads as a natural pipeline and the transaction boundary is invisible because nobody wrote BEGIN. Autocommit being off, or an ORM session staying open, is often the reason the transaction exists at all.

A Loop That Looked Efficient

The Symptom Was in an Unrelated Feature

Commit Per Row

Test the Property, Not the Pass

Code

External links

Exercise

Progress

Comments 0