Slow API: Waiting for a Database Lock

This is Part 4 of Slow API. In Part 3, requests waited for a database connection. Here, they already have one.

Two customers try to buy the last item. The first purchase gets through its SQL. The second appears to hang.

The query is small. It finds one product by its primary key and decreases the stock. There is no large result to sort, no deep pagination, no extra query for every order.

We could start looking for a better index. But first, we need to find out what the database is doing while the request waits.

One item, two purchases

At the center of the purchase is this stock update:

UPDATE lab_lock_products
SET stock = stock - 1
WHERE id = 42 AND stock > 0
RETURNING id;

If it returns a product ID, checkout creates an order in the same transaction. If it returns nothing, this attempt has not reserved an item, so checkout must not create the order.

Customer A reaches the update first. Stock changes from one to zero, and the order is created. But the transaction stays open.

Customer B now tries to buy the same product. PostgreSQL cannot yet know whether A's change will become permanent. A could commit the purchase, or something could fail and cause it to roll back.

So B waits.

That waiting protects the last item from being sold twice. The question is why A takes so long to finish.

The blocker has already finished its SQL

In our PostgreSQL 17.10 experiment, we left A's transaction open after creating the order, then started B's purchase. From a third connection, we inspected the sessions:

SELECT
    pid,
    state,
    wait_event_type,
    pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE datname = current_database()
  AND xact_start IS NOT NULL
  AND pid <> pg_backend_pid();

The two buyer sessions looked like this:

B is waiting for A. The surprising part is A's state: idle in transaction.

A is not running an expensive query. Its SQL has finished, and PostgreSQL is waiting for the application to send the next command. But A has not committed or rolled back, so it still holds the lock B needs.

This is why a list of currently running slow queries can miss the cause. The slow statement belongs to B; the transaction keeping it slow belongs to A. The activity view and blocking PID function connect those two sessions.

To inspect A, use the blocker PID from your own result:

SELECT
    pid,
    clock_timestamp() - xact_start AS transaction_age,
    query
FROM pg_stat_activity
WHERE pid = 87;

For an idle session, query is its last statement. It tells us where the database work stopped. Application logs or a request trace can then show what happened before the eventual commit.

The missing time is between the statements

In the experiment, we created that gap by leaving A at its SQL prompt. In an application, the gap can be less obvious.

Imagine checkout reserves the item and creates the order, then calls a delivery service to add an estimated arrival date to the response. Only after that call returns does it commit.

Each operation seems reasonable on its own. Together, they make the next buyer wait for a delivery estimate that has nothing to do with stock.

The UPDATE may take very little time. The lock remains for the rest of the transaction.

An index cannot shorten that delivery call. A larger connection pool gives more requests somewhere to wait, but does not release A's lock.

Finish the purchase before the optional work

If the delivery estimate is only extra information for the response, the purchase can commit before fetching it:

The stock change and order creation still belong to one transaction. We move the optional work, keeping the purchase atomic.

There is a product decision here too: if the estimate fails after commit, the purchase still exists. The response must reflect that. If delivery information determines whether the purchase is allowed, obtain it before taking the stock lock and preserve any required consistency checks.

Back in our SQL experiment, sending COMMIT in A was enough to release B. Under Read Committed isolation, B rechecked stock > 0, found that the last unit was gone, and created no order. When we repeated the experiment with A rolling back instead, B bought the restored unit. PostgreSQL describes this behavior in its Read Committed documentation.

Both outcomes are correct. What we want to remove is the unnecessary time before B can learn which outcome applies.

Mistakes that keep other requests waiting

These are easy ways to turn a brief conflict into a queue:

• Doing unrelated work while holding locks. Calling another API, running expensive calculations, or formatting a response can prolong every request behind you. Keep the work that must be atomic inside the transaction; move independent work outside it.
• Catching an application exception and returning without a rollback. An exception in your code does not automatically tell PostgreSQL to end the transaction. If the connection stays open and nothing rolls it back, its locks can remain. Use transaction handling that cleans up on every exit path, including early returns, before releasing the connection to the pool.
• Assuming an HTTP timeout cleaned everything up. The caller can stop waiting while the database transaction remains open. Make sure cancellation reaches the database operation and the transaction is finished afterward.
• Putting an entire batch in one long transaction. Updating thousands of products before committing can leave the first products locked while the rest are processed. Use smaller transactions when the job permits partial commits.
• Retrying before cleaning up the failed attempt. A new attempt can end up waiting behind your own unfinished transaction. End the previous attempt first, and only retry eligible failures with a bounded delay and retry budget.

A successful path ends with a commit; an abandoned purchase needs a rollback. Make both paths explicit, either directly or through a transaction wrapper. PostgreSQL's transaction guide explains those boundaries.

What to look for in your own API

When a small query sometimes takes seconds, inspect it while it is waiting. If it is blocked, follow the blocking PID and look at the lifetime of that transaction.

The useful question is: what happens after this transaction takes the lock and before it releases it?

That interval may contain another query, a network call, or simply an application path that forgot to finish the transaction. A lock timeout can bound the wait, but it gives the caller an error; it does not make the purchase succeed sooner.

After changing the boundary, check both latency and the result. Two buyers must still produce at most one order for the last unit. Brief contention may remain when purchases overlap, but unrelated work should no longer prolong it.

If enough transactions wait this way, they can occupy the application's connection pool too. New requests then start waiting before their SQL even reaches PostgreSQL—the symptom from Part 3. The transaction at the front of the queue can explain both delays.

The Slow API series

1. Investigating a Slow Query — Use EXPLAIN ANALYZE, improve an index, and replace deep OFFSET pagination.
2. Fast Queries, Slow Endpoint — Find N+1 queries and compare SQLAlchemy relationship-loading strategies.
3. Waiting for a Database Connection — Measure Go connection pool waits, shorten transactions, and understand where PgBouncer helps.
4. Waiting for a Database Lock (this article) — Find the blocking transaction and shorten lock waits without breaking purchase correctness.