Lock timeouts and monitoring: finding the blocking session and capping wait time Intermediate

The problem

An ordering API decrements stock with UPDATE products SET stock = stock - 1 WHERE id = 101. The API’s pool has 10 connections. An admin script runs an UPDATE on the same product 101 inside a transaction, then stops at a prompt without COMMIT.

Every request for product 101 waits for the row-level lock, each holding a connection. Ten requests are enough to drain the pool; requests for other products also wait for the pool, so the whole API’s p99 spikes while database CPU is nearly idle.

This article answers two questions: which timeout cuts this wait chain, and where to set it, and how to find the root session doing the blocking.

What to choose (summary first)

Situation	Choose	Condition
Requests must not wait long for locks	lock_timeout per app role	Smaller than statement_timeout and the request deadline
Slow query, not waiting on locks	statement_timeout	Slowest legitimate query, plus margin
Code forgets COMMIT/release()	idle_in_transaction_session_timeout	The pool tolerates closed connections
Stop long transactions whatever the reason	transaction_timeout (17+)	Not applied to legitimate batch jobs
Mid-incident, need to find the blocker	pg_stat_activity + pg_blocking_pids()	Walk the tree up to the root before killing
Need a trace of long waits	log_lock_waits = on	The threshold is deadlock_timeout

Core concepts

The four timeouts in PostgreSQL 17

All four default to 0 (off); values without a unit are in milliseconds.

Parameter	Measures	SQLSTATE	Cancels
lock_timeout	Each wait to acquire one lock	55P03 lock_not_available	The statement
statement_timeout	From when the command reaches the server until it finishes, including lock waits	57014 query_canceled	The statement
idle_in_transaction_session_timeout	Idle time inside an open transaction	25P03	The session
transaction_timeout (new in 17)	Total transaction time, including the implicit transaction of a single statement	25P04	The session

Consequences:

• Canceling a statement inside an explicit transaction aborts the transaction; the app must ROLLBACK before reusing the connection.
• Canceling the session is FATAL: the connection is closed, and the pool must discard it and create a new one.
• If statement_timeout is non-zero and ≤ lock_timeout, lock_timeout never fires.
• If transaction_timeout ≤ either the idle or statement timeout, the longer timeout is ignored. Prepared transactions are not subject to transaction_timeout.

deadlock_timeout and log_lock_waits

deadlock_timeout (default 1s) is how long to wait before the server checks for a deadlock. It does not cancel ordinary waits.

log_lock_waits logs when a wait exceeds deadlock_timeout. It defaults to off in PostgreSQL 17 and 18. Only superusers or roles with the corresponding SET privilege can change these two parameters.

How it works

The wait chain in the scenario above, with lock_timeout = 2s for the API role; the script role’s idle timeout is shortened to 5 seconds to keep the diagram compact:

An idle-in-transaction blocker drains the pool; lock_timeout returns connections, the idle timeout releases the lock

◀ Prev▶ PlayNext ▶↺ RestartStep 1/7

admin-script UPDATEs product 101 and leaves the transaction open: idle in transaction, the row lock still held.

Step	Lock holder	Waiting	Pool
0	admin-script, idle in transaction	Nobody	Normal
1–3	admin-script	order-api-1 (transactionid), order-api-2 (tuple)	Drained once there are 10 waiters
4	admin-script	Waiters get 55P03 after 2 seconds	Connections come back
5–6	Nobody: the admin session is closed, rolled back	New requests get the lock immediately	Recovers

lock_timeout protects the pool but does not remove the blocker; idle_in_transaction_session_timeout removes the blocker but has to wait out its threshold. You need both.

Operational details

Where to set timeouts

The docs advise against setting the three statement/transaction-canceling timeouts in postgresql.conf because they affect every session, including migrations and maintenance. Precedence from lowest to highest:

1. ALTER DATABASE ... SET.
2. ALTER ROLE ... SET, then ALTER ROLE ... IN DATABASE ... SET. Only applies to new sessions.
3. options in the connection string. Tested: role set to 3s, options='-c lock_timeout=1s' gives 1s.
4. SET in the session; SET LOCAL only until the end of the transaction.

ALTER ROLE order_api SET lock_timeout = '2s';
ALTER ROLE order_api SET statement_timeout = '5s';
ALTER ROLE order_api SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE admin_ops SET idle_in_transaction_session_timeout = '5min';

BEGIN;                              -- migration
SET LOCAL lock_timeout = '500ms';   -- only within this transaction
-- ALTER TABLE ...
COMMIT;

Through PgBouncer in transaction mode, a session-level SET can leak to another client; use ALTER ROLE or SET LOCAL. Migrations use a short lock_timeout with retry because a waiting DDL blocks every query behind it (see lock queue).

Relationship with app and driver timeouts

Per the timeout budget, timeouts shrink as you go inward: request deadline > statement_timeout > lock_timeout. Example with an 8-second deadline: statement_timeout = 5s, lock_timeout = 2s.

If the app-side timeout fires first, the app stops waiting, but the server may still be waiting for the lock if the driver does not send a cancel. Only a server-side timeout guarantees the wait really stops, with a SQLSTATE the app can map to 503 or a retry.

Real-world example

Flash sale: 10 connections, one script forgets to COMMIT

The order-api pool has 10 connections, product 101 receives 200 requests/second. Without lock_timeout, in under a second the whole pool is waiting behind the admin script. With lock_timeout = 2s, requests for product 101 fail fast and other endpoints still get connections. The numbers are an illustrative scenario.

Demo: the blocker → blocked tree

Run on PostgreSQL 17.11 (examples/docker-compose.yml), schema demo_lock_timeouts. Three psql sessions set application_name:

1. admin-script: BEGIN; UPDATE ... WHERE id = 101; then sends nothing more.
2. order-api-1, then order-api-2: UPDATE ... SET stock = stock - 1 WHERE id = 101;.

A fourth session prints the wait tree, root first. The query calls pg_blocking_pids() exactly once per waiter because the function has a cost:

WITH RECURSIVE waiting AS (
  SELECT pid, pg_blocking_pids(pid) AS blockers
  FROM pg_stat_activity
  WHERE wait_event_type = 'Lock'
), tree AS (
  SELECT r.pid, 0 AS depth, ARRAY[r.pid] AS path
  FROM (SELECT DISTINCT unnest(blockers) AS pid FROM waiting) r
  WHERE r.pid NOT IN (SELECT pid FROM waiting)
  UNION ALL
  SELECT w.pid, t.depth + 1, t.path || w.pid
  FROM tree t
  JOIN waiting w ON t.pid = ANY (w.blockers)
  WHERE w.pid <> ALL (t.path)
)
SELECT repeat('  ', t.depth) || t.pid AS pid_tree,
       a.application_name AS app, a.state, a.wait_event,
       round(extract(epoch FROM now() - a.xact_start)) AS xact_s,
       left(a.query, 45) AS query
FROM tree t
JOIN pg_stat_activity a USING (pid)
ORDER BY t.path;

 pid_tree  |     app      |        state        |  wait_event   | xact_s |                     query
-----------+--------------+---------------------+---------------+--------+-----------------------------------------------
 17864     | admin-script | idle in transaction | ClientRead    |      4 | UPDATE demo_lock_timeouts.products SET stock
   17871   | order-api-1  | active              | transactionid |      3 | UPDATE demo_lock_timeouts.products SET stock
     17884 | order-api-2  | active              | tuple         |      2 | UPDATE demo_lock_timeouts.products SET stock

How to read it:

• The root waits for nothing (ClientRead), has state = idle in transaction, and the oldest xact_start.
• The query of an idle session is the last statement run, not one currently running.
• A root with PID 0 is a prepared transaction. A waiter with several blockers appears several times.

Real output of the timeouts

A third waiter with SET lock_timeout = '2s' and \set VERBOSITY verbose:

ERROR:  55P03: canceling statement due to lock timeout
LOCATION:  ProcessInterrupts, postgres.c:3399
Time: 2001.100 ms (00:02.001)

The blocker sets idle_in_transaction_session_timeout = '3s'; the waiter arrives about 1 second later:

-- blocker
FATAL:  25P03: terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
-- waiter
UPDATE 1
Time: 1999.035 ms (00:01.999)

• SET statement_timeout = '1s' while waiting for a lock: ERROR: canceling statement due to statement timeout (57014).
• SET transaction_timeout = '2s'; BEGIN; SELECT pg_sleep(3);: FATAL: 25P04: terminating connection due to transaction timeout, the session is closed while running.

Cancel or terminate

Loading diagram…

• pg_cancel_backend(pid): cancels the running statement; a canceled waiter gets 57014.
• pg_terminate_backend(pid, timeout): closes the session, rolls back, releases every lock; timeout (ms) makes the function wait until the process actually exits.
• Requires superuser, membership in the role owning the session, or pg_signal_backend.

Demo: after pg_terminate_backend(pid, 5000) on the blocker, the next waiter gets UPDATE 1 immediately. PIDs and seconds vary each run.

Trade-offs

Pros

• lock_timeout turns infinite waits into fast errors with their own SQLSTATE, keeping the pool for other endpoints.

• Session-level timeouts remove blockers automatically, with nobody on call needed.

< slot="cons">

• Low thresholds cause false errors under high load; the app needs bounded, idempotent retries.

• Session timeouts close connections: the pool and driver must tolerate FATAL.

• Timeouts only cut the symptom; long transactions are still a design bug.

Cons

🎮 Try it

• A migration locks the whole table: find the blocking session with pg_blocking_pids and cancel the right one.

Production checklist

1. Set lock_timeout, statement_timeout and idle_in_transaction_session_timeout with ALTER ROLE for each app role; verify with SHOW from a new connection.
2. Keep request deadline > statement_timeout > lock_timeout.
3. Map 55P03 and 57014 to bounded-retry errors; treat 25P03 and 25P04 as dead connections.
4. Migrations use a short SET LOCAL lock_timeout with retry.
5. Enable log_lock_waits on PostgreSQL 17/18.
6. Every connection sets application_name per service.
7. Alert on the number of idle in transaction sessions above a threshold and the number of sessions waiting on Lock.
8. Runbook: run the tree query, save the root’s query and xact_start, then terminate.

Common mistakes

The most common mistake during an incident is acting on the wrong session:

Canceling a blocker that is idle in transaction

pg_cancel_backend only cancels the running statement. An idle in transaction session runs nothing, so the function returns t and the lock is still held. Use pg_terminate_backend, after saving query and application_name to track down the code that forgot to COMMIT.

Misconception	Consequence	What to do
deadlock_timeout caps the wait time	Waiters still wait forever	Use lock_timeout
Kill the first waiter you see	The next waiter moves to the front	Walk up to the root
An idle session’s query is the running statement	Blaming the wrong statement	It is the last one; check state, xact_start
lock_timeout equal to statement_timeout	The app only sees 57014, not knowing it was a lock wait	Set lock_timeout clearly smaller

Self-check

1. Why does lock_timeout save the pool but not remove the blocker on product 101?
2. pg_blocking_pids() for order-api-2 returns order-api-1. What happens if you kill order-api-1?
3. transaction_timeout = 30s, statement_timeout = 60s, a statement running 45 seconds: which error, and what gets canceled?
4. Why does an expired client-side timeout not prove that the lock wait on the server has stopped?

Summary

Key takeaways

• lock_timeout (55P03) and statement_timeout (57014) cancel the statement; idle_in_transaction_session_timeout (25P03) and transaction_timeout (25P04) close the session.
• Set them per role or with SET LOCAL; request deadline > statement_timeout > lock_timeout.
• The root of a wait tree is usually idle in transaction, waiting on nothing, with the oldest xact_start.
• An idle blocker must be terminated; cancel only stops a running statement.
• log_lock_waits defaults to off in 17 and 18.

References

• Client Connection Defaults: defaults and interactions of the four timeouts.
• Error Codes: 55P03, 57014, 25P03, 25P04.
• Lock Management and Logging: deadlock_timeout, log_lock_waits.
• pg_stat_activity: state, query, xact_start, wait_event_type.
• Session Information Functions: pg_blocking_pids(), hard/soft blocks, cost.
• Server Signaling Functions: cancel, terminate, privileges.