Why long transactions hurt

Transactions, isolation and locking Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Pick the fix

A checkout endpoint opens a transaction, reserves stock, calls the payment provider (up to 30 s), then commits. Better design?

Options
Hint 1 of 1

What is the transaction waiting on most of the time?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A Reserve stock and commit, call the provider outside any transaction, then confirm or release in a second short transaction

Correct. Locks and snapshots are held for milliseconds, and the reservation state machine handles failures.

A transaction that waits on a slow network call holds its locks and snapshot the whole time. Commit the reservation first, call the provider with no transaction open, then confirm or release in a new one.

Why not the others?
  • B Raise statement_timeout to 60 s. Makes the long lock hold official.
  • C Use SERIALIZABLE. Still holds the transaction open during the call.
  • D Use READ UNCOMMITTED. PostgreSQL treats it as READ COMMITTED, and the problem isn't visibility.

30-second answer

It holds its locks for the whole time, so writers to those rows queue behind it, and DDL queues behind those writers. It also pins its snapshot: VACUUM can't remove row versions newer than the oldest open snapshot, so tables bloat across the whole database. The usual culprit is a session left 'idle in transaction' by code that began a transaction and then waited on something else.

Practise saying it · 30 s. No recording, just a timer

Why are long transactions harmful?

Say your answer out loud, then check which of these points you covered:

  • Locks held, queues form
  • Snapshot pinned, VACUUM blocked
  • Idle in transaction
  • Timeouts and short transactions

Deep dive

Find them: SELECT pid, state, xact_start FROM pg_stat_activity WHERE state LIKE 'idle in transaction%'.

idle_in_transaction_session_timeout kills sessions that sit idle inside a transaction (off by default).

statement_timeout and lock_timeout bound how long one statement or lock wait may run.

An ALTER TABLE waiting for a lock blocks every later query on that table; set a short lock_timeout for migrations.

Guard rails for a session Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
SET idle_in_transaction_session_timeout = '30s';
SET lock_timeout = '3s';
SET statement_timeout = '15s';
SHOW lock_timeout;

Common mistakes

  • Opening a transaction, then calling an external HTTP API inside it.
  • Leaving autocommit off in an interactive console.
  • Running migrations without lock_timeout.

Senior take & follow-ups

Name both costs (locks and VACUUM) and the guard rails: timeouts, monitoring, no network calls in transactions.

Follow-up questions

Think of your answer first, then open the hint.

  1. What's a safe lock_timeout for a migration?

    Show hint

    A few seconds, with a retry, so it gives up instead of stalling traffic.

  2. Do read-only transactions hurt?

    Show hint

    Yes, they still pin a snapshot and block cleanup.

Related questions

Suggest a fix if something here is wrong or out of date.