The Lock You Did Not Know You Were Taking

GET /invoices/{id} went from 180ms p99 to 2.4s over a Tuesday afternoon, and no deploy had touched it. Nothing about the endpoint writes: it loads an invoice, checks a feature flag, and returns JSON. It was taking row locks anyway.

A read that writes

The handler had grown a helper during a previous incident:

def load_invoice_for_display(conn, invoice_id):
    row = conn.execute(
        "SELECT * FROM invoices WHERE id = %s FOR UPDATE",
        (invoice_id,),
    ).fetchone()
    if row is None:
        raise NotFound()
    # ... feature flag lookup, an outbound call to the tax service ...
    return row

Someone added FOR UPDATE because a different caller wanted a read-modify-write and this helper looked like the place to put it. The read-only caller inherited the lock and never knew.

FOR UPDATE takes a row-level exclusive lock. It blocks other FOR UPDATE, UPDATE, and DELETE on those rows, and it blocks them for the rest of the transaction. So the question that decides the damage is not "how long is this query" but "how long is this transaction".

In this case the transaction also called the tax service over HTTP, with a 2-second timeout. Every request held two row locks while waiting on a remote dependency. Under load, requests queued behind each other on the same invoice ids, which is why p99 collapsed while average latency barely moved.

Finding it

Two queries answer almost every version of this question. First, who is waiting:

SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by,
       now() - query_start AS waiting
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY waiting DESC;

Second, what is holding the lock and for how long:

SELECT l.pid, l.locktype, l.mode, l.granted,
       c.relname, a.state, now() - a.xact_start AS tx_age,
       left(a.query, 80) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.mode IN ('ExclusiveLock', 'RowExclusiveLock')
ORDER BY tx_age DESC;

xact_start is the column that matters. A transaction that has been open for 4 seconds is the problem, not the statement it is running right now.

What we changed

Three things, in order of impact.

The read stopped taking a lock. It did not need one. FOR UPDATE is a write lock borrowed by a read path, and the path that needed it got its own function.

The transaction stopped containing a network call. The tax service call moved outside the transaction boundary: read, commit, call, then a second short transaction if the result needed writing. A transaction should describe one unit of database work, not one unit of work in general.

The remaining read-modify-write used the weakest lock that works. For a row you are going to update but whose key you are not changing, FOR NO KEY UPDATE is enough, and it does not block foreign-key inserts that reference the row. FOR SHARE if you only need the row not to change underneath you. Take the strongest lock only when you actually need it.

The pattern to grep for

Any FOR UPDATE inside a function called from a read path. Then check the transaction boundary around it: tracing helpers, log shipping that opens a transaction, and retry decorators that wrap a whole function are the usual way a lock ends up spanning a remote call.

Two rules survived this:

• A lock lives as long as the transaction, not as long as the statement. Optimising the query that takes it is treating the symptom.
• If a function is called from a read path, it must not be able to write. The way to enforce that is not a comment. It is a separate connection with a read-only role for anything that claims to be a read.

The endpoint was never slow. It was waiting in line behind itself.

---

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky