A service opens a database transaction, calls an external payment API inside it, and commits only after that API responds. What costs does keeping the transaction open across the remote call impose on the database, and what would you do instead?
answer
- duration × contention, not work done
- locks released at commit, not last statement
- oldest open txn = cleanup horizon
- never hold a txn across network I/O
- PENDING row + idempotency key + reconcile
basics
~20 sFor the whole API call the transaction keeps its locks, keeps its read snapshot, and holds a pooled connection. Other writers queue behind those locks and old row versions cannot be cleaned up. Move the remote call outside the transaction.
solid answer
~50 sA transaction's cost is driven by its **duration**, not by how much work it does. While it is open the engine must keep every row and index lock it has taken, keep the read snapshot it was given, keep its undo/rollback data reachable, and keep a connection occupied. Wrapping a network call in that window lets an unrelated third party decide how long your locks are held. A slow provider turns a 5 ms write into a 30-second lock hold: sessions touching the same rows block, the connection pool drains, and the service stalls on one dependency. The fix is to shrink the transaction to local work: validate and read, commit, make the remote call, then open a second short transaction to record the outcome. The atomicity you gave up is recovered by design — an idempotency key, an outbox row, or a status state machine (PENDING then CONFIRMED) — never by holding the transaction open longer.
code
text · 12 linesBAD
BEGIN
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
<-- HTTP POST to payment provider (up to 30s) -->
INSERT INTO payments(...);
COMMIT -- row 42 locked for the whole HTTP call
GOOD
BEGIN; INSERT INTO payments(id, status, idem_key) VALUES (...,'PENDING',...); COMMIT;
<-- HTTP POST to payment provider, with timeout -->
BEGIN; UPDATE payments SET status='CONFIRMED' WHERE idem_key = ...;
UPDATE accounts SET balance = balance - 100 WHERE id = 42; COMMIT;go deeper
Say clearly that locks and the connection are held until commit, so a slow API call means a long lock hold, and that the call belongs outside the transaction.
Add the MVCC snapshot and undo retention, name the pool-exhaustion cascade, and sketch the PENDING-then-confirm split.
Talk about the failure cascade under provider slowdown, idempotency keys and the outbox pattern, and the timeouts (statement, lock wait, idle-in-transaction) that bound the blast radius.
Frame it as a coupling problem: transaction scope must not span trust or availability boundaries, and discuss reconciliation, framework defaults that wrap HTTP clients in transactions, and how you enforce this across many services.
## What an open transaction is holding A transaction is more than markers around some statements. From its first lock or first snapshot until it ends, the engine must keep several resources alive. **Locks.** Every row, page or index entry the transaction has written stays locked until commit or rollback. Two-phase locking means write locks are never released early — that is what makes the transaction atomic. Anyone who wants to write the same row waits. **A read snapshot.** In a multi-version (MVCC) engine, each transaction reads a consistent point-in-time view. The engine may not discard any row version that this snapshot could still need, so the *oldest open transaction* sets the cleanup horizon for the whole database. **Undo / rollback data.** The before-images needed to undo the transaction (and, in many engines, to reconstruct old versions for other readers) must remain available until it finishes. **A session and a connection.** Transactions are bound to a session; a pooled connection cannot be handed to anyone else while a transaction is in flight. None of these are released "when the last statement finishes". They are released **at commit or rollback**. So the resource bill is duration × contention, and a transaction that spends 30 seconds waiting on someone else's HTTP endpoint pays exactly as much as one doing 30 seconds of real work. ## Why a remote call inside a transaction is the classic mistake When you write `begin → UPDATE account → call payment provider → INSERT payment_record → commit`, you have handed control of your lock duration to a system you do not run. Its p99 latency, its retries, its timeouts and its outages become your lock-hold time. The failure mode is not gradual: when the provider slows from 50 ms to 5 s, lock waits pile up, every worker thread ends up blocked on the same rows, the pool is exhausted, and requests that never touch payments start failing too. A single slow dependency has become a database-wide incident. The same shape appears with a transaction held open across **user think-time** (open a transaction when the user opens an edit form, commit when they press Save), across a large in-application computation, or across a message-broker publish. ## The restructuring Split the unit of work at the network boundary: 1. Short transaction 1: read state, validate, write an intent row (`status = 'PENDING'`) with a unique idempotency key. Commit. 2. Outside any transaction: call the provider, with its own timeout. 3. Short transaction 2: record the outcome (`status = 'CONFIRMED'` or `'FAILED'`). Commit. You lost one-shot atomicity across the two systems — but you never really had it, because a remote side effect cannot be rolled back by a database `ROLLBACK` anyway. If the process dies between steps 2 and 3, a reconciliation job finds rows stuck in `PENDING` and asks the provider what happened; the idempotency key stops step 2 from being executed twice. When the second system is also a service you own, the **transactional outbox** pattern does the same thing: commit the business row and an outbox row atomically, then a relay publishes the message after commit. ## Practical rules - Keep transactions to local database work; do I/O to other systems outside them. - Acquire whatever you will contend on **as late as possible** so the hot row is locked for the shortest window. - Begin the transaction after the connection is checked out and cheap preparation is done; do not begin on request entry. - Set a bound so a mistake cannot become an outage: a statement timeout, a lock-wait timeout, and an idle-in-transaction timeout that kills sessions sitting inside an open transaction doing nothing. - Beware framework defaults: a service-layer transactional method that also calls an HTTP client reproduces this bug invisibly, and so does an ORM session opened for the whole web request. The general principle an interviewer wants to hear: **a transaction is a lock-and-snapshot lease; lease it for the shortest time you can, and never let a third party decide when you give it back.**
- If you commit before the external call, how do you avoid charging a customer twice when the process crashes and retries?Generate an idempotency key when you write the PENDING row and send that same key to the provider on every attempt, so the provider collapses duplicates into one charge. On retry you first re-read the local row: if it is already CONFIRMED you stop. A reconciliation job sweeps rows left PENDING past a threshold and queries the provider for the authoritative outcome.
- A colleague says the answer is just to raise the lock-wait timeout. Why is that wrong?A longer timeout does not shorten the lock hold; it only makes the victims wait longer before failing, so queues and pool usage grow instead of shrinking. Timeouts are a blast-radius control, not a fix. The real fix is reducing how long the transaction holds the lock, which means taking the network call out of the transaction.
Holding a transaction across an API call is like taking the only library copy of a book to the counter, then standing there on the phone with your travel agent. You are not reading it, but nobody else can have it until you hang up.
saying these in an interview costs you the question
- Thinking locks are released when the statement finishes rather than at commit or rollback
- Believing an idle transaction is harmless because it is doing no work
- Proposing a bigger connection pool or a longer lock timeout as the fix
- Assuming database ROLLBACK can undo an external side effect such as a charge
- Opening the transaction at the start of the web request and committing at the end by default