Writes against one table have started timing out, and your monitoring shows several database sessions sitting idle inside an open transaction. How would you confirm that long-running transactions are the cause, and what controls would you put in place so it cannot recur?
answer
- classify: active-slow vs idle-in-transaction vs idle
- blocking chain: victim to holder, age, state
- capture evidence, then kill the root
- idle-in-transaction + statement + lock-wait timeouts
- alert on oldest txn age, not connection count
basics
~20 sFind the blocking chain: which session holds the lock the timed-out writers wait for, how old its transaction is, and what it is doing. Then bound it: statement timeout, lock-wait timeout, idle-in-transaction timeout, alerts on oldest-transaction age, and no network I/O inside transactions.
solid answer
~60 s**Confirm first.** Every engine exposes current sessions with their state, transaction start time, and what they wait on. I look for (1) the oldest transaction age in the instance, (2) the blocking chain — victim waits on a lock held by session X — and (3) session X's state. `idle in transaction` for minutes means the application opened a transaction and went off doing something else; `active` on a slow statement is a different fix. **Then act.** Terminate or roll back the offending session to restore service, but capture what it was running first, and trace it to the code path — usually a transaction opened around an HTTP call, a per-request ORM session, a batch job, or an interactive session with autocommit off. **Then prevent.** Server- or role-level statement timeout, a lock-wait timeout so victims fail fast rather than queueing, an idle-in-transaction timeout that kills offenders automatically, alerts on oldest-transaction age (not connection count), pool settings that never hand out a session with an open transaction, and batch jobs that commit in chunks. Also check for orphaned prepared two-phase transactions, which pin things forever and survive restarts.
code
text · 7 linessession 811 state=active waiting: row lock on orders(id=42) wait 38s
session 774 state=active waiting: row lock on orders(id=42) wait 51s
session 512 state=idle in transaction xact_age=00:14:22 holds: row lock orders(id=42)
last_stmt: UPDATE orders SET status='X' WHERE id=42
app=checkout-svc
root = 512 (idle in transaction 14 min) -> terminate, then bound with timeoutsgo deeper
Recognize that a session sitting inside an open transaction still holds its locks, and know that the first step is finding who blocks whom.
Walk the blocking chain explicitly, distinguish idle-in-transaction from a slow active statement, and name statement and idle-in-transaction timeouts as guards.
Show the full loop: evidence capture, terminate the root, trace to the code path, then per-role timeouts, age-based alerting, pool hygiene and chunked batch jobs.
Treat it as availability policy: duration limits and fail-fast lock timeouts are platform defaults, contention risk is designed out of the code, and long analytical work is isolated away from the OLTP primary.
## Step 1: separate the three states When writes time out, the first job is to classify the sessions you can see: - **Active and slow** — a statement is genuinely executing for a long time. Fix is query/plan work or moving it off the primary. - **Idle in transaction** — the session issued BEGIN and possibly some statements, then stopped sending anything and never committed. The database is waiting on the *application*. This is the classic long-transaction pathology: it holds locks and pins the snapshot while doing zero work. - **Idle** — no transaction open. Harmless from a lock and snapshot point of view, though it still consumes a connection slot. The distinction matters because the remedies are completely different, and candidates who lump them together usually propose the wrong fix. ## Step 2: prove causation with the blocking chain Do not stop at "there are long transactions". Establish the chain: the timing-out writer waits on a lock; that lock is held by session X; session X has transaction age T and state S. If X is idle in transaction with an age of minutes, causation is established. Follow the chain transitively — one long transaction at the root frequently has a fan-out of dozens of blocked sessions behind it, and the visible symptom (pool exhaustion, request timeouts across unrelated endpoints) is usually the fan-out, not the root. Evidence to capture before you kill anything: the session's last statement, its application name and client address, its transaction start time, and the lock it holds. Without that you will fix the symptom and see it again tomorrow. Also check two silent holders that never show up as busy sessions: - **Prepared / in-doubt two-phase transactions** left behind by a crashed coordinator. They hold locks and pin the cleanup horizon indefinitely and survive restarts. - **Replication slots or standby feedback** that hold the cleanup horizon back even with no long local transaction. Same bloat symptoms, different root cause. ## Step 3: restore service Cancel the statement or terminate the session at the root of the chain. Terminating rolls its transaction back, which releases locks and the snapshot; the queue behind it drains. Rollback cost is proportional to the work it did, so a mostly-idle transaction rolls back instantly, while a transaction that wrote tens of millions of rows can take a long time to undo — worth knowing before you press the button, because a half-finished giant DELETE can spend longer rolling back than it spent running. ## Step 4: bound it so it cannot recur Controls, roughly in order of value: 1. **Idle-in-transaction timeout.** Terminates sessions holding an open transaction while sending nothing. This is the single most effective guard because it targets the pathology directly. Set it in seconds, not minutes, for OLTP roles. 2. **Statement timeout.** Caps any single statement. Set per role: tight for the web application, looser for reporting or migration roles. 3. **Lock-wait timeout.** Makes victims fail fast with a clear error instead of piling up. This converts a total stall into a bounded error rate, which is far easier to diagnose and to shed load from. 4. **Alerting on oldest-transaction age.** Graph the age in seconds of the oldest open transaction and the oldest prepared transaction. Alert on age, because connection counts and CPU look fine while this is going wrong. 5. **Application-side rules.** No network calls inside transactions; do not open a transaction on connection checkout or at the start of a web request; keep read-only work out of explicit transactions; make batch jobs commit in chunks. 6. **Pool hygiene.** A pool that returns a connection with an open transaction poisons the next borrower; ensure rollback on return, and set a connection max-lifetime so leaked state does not live forever. ## Step 5: make the boundary visible The recurring organisational fix is making transaction scope explicit in code review: wrap transactional regions narrowly, forbid the pattern where a transactional service method also calls a client library, and add a test or lint rule where possible. Timeouts stop an incident; the code rule stops the class of bug. A good answer ends with the operational framing: **long transactions are a latency amplifier — one session's duration becomes everyone's queue — so you bound duration at the server, alert on age, and remove the code paths that create them.**
- How do you choose an idle-in-transaction timeout value without breaking legitimate work?Set it per role rather than globally. The OLTP application role gets a few seconds, because a correct request-scoped transaction never idles that long; migration, ETL and admin roles get a looser value or an explicit exemption. Measure the current distribution of transaction durations first so you place the cut above real traffic and below the pathological tail, and roll it out by alerting at the threshold before enforcing it.
- You kill the long transaction and locks are released, but the table is still bloated and scans are still slow. Why, and what next?Terminating releases locks immediately and unpins the cleanup horizon, but the dead versions accumulated during those hours are still physically present. Cleanup must now run and reclaim them, which frees space for reuse but usually does not shrink the file. If the bloat ratio is severe you follow with an online rebuild or repack of the table and its indexes during a low-traffic window.
saying these in an interview costs you the question
- Killing sessions without recording what they were running, so the root cause repeats
- Treating idle-in-transaction the same as idle, or the same as a slow active query
- Raising the lock-wait timeout so victims wait longer, and calling it a fix
- Alerting on connection count or CPU instead of oldest-transaction age
- Forgetting that rolling back a huge write-heavy transaction can itself take a long time
- Overlooking orphaned prepared two-phase transactions and replication slots as horizon holders