Orders stall only during sales: work done while holding a SELECT FOR UPDATE lock

If you keep stock as one row per item and every order takes that row with SELECT ... FOR UPDATE before updating it, there is a ceiling on how many orders for the same item can complete per second. That is because the row lock is not released until COMMIT, and other orders that come for the same row wait on the lock in the meantime.¹ Call the time from when SELECT ... FOR UPDATE returns with the lock until COMMIT the hold time. The ceiling is then 1 ÷ hold time per second, which is 10 per second for a hold time of 0.1s. The hold time includes everything the application server does while holding the lock, such as calls to external APIs.

The excess orders pile up unfinished only during periods like a sale, when orders for the same item arrive faster than this ceiling, and response time for that item's orders keeps growing. An order waiting for the lock holds its connection while it waits, so other requests on the same connection pool also slow down while they wait for a free connection. Adding DB connections does not push completions per second past the ceiling. Waiting orders use no DB CPU, so this happens even while DB CPU usage stays low.

In traces collected with Datadog, OpenTelemetry, or similar tools (a trace lays out, for a single request, the time spent on each operation, such as each SQL statement sent to the DB; one operation is called a span), the span of the SELECT containing FOR UPDATE grows by the time spent waiting for the lock. The UPDATE and INSERT spans that follow are short. Once the connection pool is full, most of the response time is spent waiting for a connection.

I reproduced this on PostgreSQL 18.6 and checked the ceiling and the effect of moving the application server's work outside the lock.²

What is happening

The diagram below shows orders for the same item arriving every 0.05s (20 per second) with a hold time of 0.1s (a ceiling of 10 per second). It assumes there are enough connections and that orders take the lock in arrival order (the actual order is not necessarily the arrival order³). Each character is 0.01s, = is time holding the lock, . is time waiting for the lock, and t is seconds since the first order arrived.

t 0.0 0.1 0.2 0.3 0.4 A ========== B .....========== C ..........========== D ...............========== 

Completions stay at one per 0.1s, so the gap between that and the 20 per second arriving accumulates. As long as orders keep arriving, the wait grows with each one: 0.05s for B, 0.1s for C, 0.15s for D. If you add connections, the new connections fill up with orders for the same item. Orders that were waiting for a connection move to waiting for the lock, and completions stay at one per 0.1s.

Completions and response time at peak

I sent orders for the same item at an average of 20 per second, at random intervals, for 30 seconds. Each order sends the following SQL.

BEGIN; SELECT stock FROM items WHERE id = 1 FOR UPDATE; -- 0.1s of work on the application server UPDATE items SET stock = stock - 1 WHERE id = 1; INSERT INTO orders ...; COMMIT; 

Over the 30 seconds of arrivals, COMMITs per second averaged 9.8, leveling off at about the ceiling of 10 per second for a 0.1s hold time.

The p99 response time (the value at the 99% position when response times are sorted from fastest to slowest) was 30.5s, longer than the 30 seconds during which orders kept arriving.

Identifying it in your own environment

If, while response times are growing, orders for the item arrive at more than 1 ÷ hold time per second and completions flatten out at that level, this is what is happening. Count arrivals and completions by filtering order requests and successful responses on the item ID in the URL or access log.

To get the hold time, take a few order traces, measure from the end of the SELECT span containing FOR UPDATE to the end of the last SQL span (the COMMIT span, if there is one), and take the median. The spans and gaps in between are the work done while holding the lock.

Fixes

Move the work found in the previous section, between the SELECT containing FOR UPDATE and the last SQL statement, to before the transaction or after COMMIT. Either way, only SQL is left in the hold time. Only work that does not use values from the locked row (such as the stock count) can be moved out. For work that can fail, such as payment, what you have to undo when it fails depends on which side you move it to.

Before the transaction

-- 0.1s of work on the application server BEGIN; SELECT stock FROM items WHERE id = 1 FOR UPDATE; -- if stock is 0, ROLLBACK and reject the order UPDATE items SET stock = stock - 1 WHERE id = 1; INSERT INTO orders ...; COMMIT; 

The work finishes before stock is checked, so even an order that is rejected because stock ran out has already done the work. When rejecting, undo the result of that work. For payment, release the authorization (the hold placed on the card's credit limit).

After COMMIT

BEGIN; SELECT stock FROM items WHERE id = 1 FOR UPDATE; UPDATE items SET stock = stock - 1 WHERE id = 1; INSERT INTO orders ...; COMMIT; -- 0.1s of work on the application server 

The order and the stock decrement are committed before the work runs, so when the work fails (for payment, when the payment does not go through), cancel the order and restore the stock in a separate transaction.

Completions and response time with the work moved out

As in the peak run, I sent orders for the same item at 20 per second for 30 seconds.

In both cases, completions per second catch up with the 20 per second arriving, and p99 drops below 0.2s.

Appendix: getting a span per SQL statement with OpenTelemetry

If your order traces do not show a span for each SQL statement, add OpenTelemetry instrumentation (a library that records operations such as SQL statements as spans) to the application server. The application server in the test is written in Python, sends SQL to the DB with psycopg 3, and uses psycopg_pool for the connection pool. These are the packages and versions installed.

opentelemetry-api==1.45.1 opentelemetry-sdk==1.45.1 opentelemetry-instrumentation==0.66b1 opentelemetry-instrumentation-dbapi==0.66b1 opentelemetry-instrumentation-psycopg==0.66b1 

Enable the instrumentation once at startup, before creating the connection pool, with the following code.

from opentelemetry import trace from opentelemetry.instrumentation.psycopg import PsycopgInstrumentor from opentelemetry.sdk.trace import TracerProvider from opentelemetry.sdk.trace.export import BatchSpanProcessor provider = TracerProvider() provider.add_span_processor(BatchSpanProcessor(exporter)) trace.set_tracer_provider(provider) tracer = trace.get_tracer("server") PsycopgInstrumentor().instrument(tracer_provider=provider) 

For exporter, use an exporter that sends spans to your APM (a service for viewing traces, such as Datadog) or to a collector.⁴

Once PsycopgInstrumentor().instrument(...) has been called, each SQL statement sent through a cursor's execute becomes one span. The span name is the first word of the statement, so statements with and without FOR UPDATE are both named SELECT. To tell which span contains FOR UPDATE, look at the db.statement attribute, which holds the full SQL text. conn.commit() does not produce a span, so Identifying it in your own environment measures the hold time up to the end of the last SQL span.

Open a parent span for each order, and take a connection and send the SQL inside it.

with tracer.start_as_current_span("POST /orders"): with pool.connection() as conn: with conn.cursor() as cur: cur.execute("SELECT stock FROM items WHERE id = 1 FOR UPDATE") # work done while holding the lock  cur.execute("UPDATE items SET stock = stock - 1 WHERE id = 1") cur.execute("INSERT INTO orders (item_id) VALUES (1)") conn.commit() 

The SQL spans are recorded as children of this parent span, so all the SQL for one order lines up in one trace. The parent span is opened before the connection is taken, so time spent waiting for a connection does not become a span. It shows up as the gap between the start of the parent span and the first SQL span.

For one order from the peak run, the exporter wrote these four spans (attributes other than db.statement are omitted).

{"trace_id": "5a660400fdbe203fae688234501042ab", "span_id": "8e6cf824a2827572", "parent_id": "ee5e5356e7faea3e", "name": "SELECT", "start_ns": 1792974910167581150, "end_ns": 1792974912090909676, "attributes": {"db.statement": "SELECT stock FROM items WHERE id = 1 FOR UPDATE"}, "status": "UNSET"} {"trace_id": "5a660400fdbe203fae688234501042ab", "span_id": "a70b9b5a83f4056a", "parent_id": "ee5e5356e7faea3e", "name": "UPDATE", "start_ns": 1792974912191022767, "end_ns": 1792974912191166374, "attributes": {"db.statement": "UPDATE items SET stock = stock - 1 WHERE id = 1"}, "status": "UNSET"} {"trace_id": "5a660400fdbe203fae688234501042ab", "span_id": "79f979eafb07d7ff", "parent_id": "ee5e5356e7faea3e", "name": "INSERT", "start_ns": 1792974912191198963, "end_ns": 1792974912191307658, "attributes": {"db.statement": "INSERT INTO orders (item_id) VALUES (1)"}, "status": "UNSET"} {"trace_id": "5a660400fdbe203fae688234501042ab", "span_id": "ee5e5356e7faea3e", "parent_id": null, "name": "POST /orders", "start_ns": 1792974892585093046, "end_ns": 1792974912191552889, "attributes": {}, "status": "UNSET"} 

Spans from the same order share a trace_id, so group them by it. Among them, find the SELECT span whose db.statement contains FOR UPDATE, and measure from its end_ns to the end_ns of the last SQL span (here, INSERT). For this order that is 1792974912191307658 − 1792974912090909676 = 100,397,982 ns, about 0.10s. This is the hold time measured in Identifying it in your own environment.

1. The Row-Level Locks section of the PostgreSQL 18 documentation. A row lock taken with SELECT ... FOR UPDATE is not released until the transaction ends, and a transaction that comes to run SELECT ... FOR UPDATE or UPDATE on the same row waits until then. ↩

2. Run on a GitHub Actions ubuntu-24.04 runner with PostgreSQL on its default settings. The results are in the CI run, and the code is in show-your-work. ↩

3. ExecLockRows() and heapam_tuple_lock() in the PostgreSQL 18.6 source. When the preceding transaction updates the row, a waiting transaction moves to the new row version the update created and tries to take the lock again, competing with transactions that arrived later. ↩

4. The test used a custom exporter that writes spans to a file as JSON, one span per line. ↩