Why does a slow payment API make other checkouts fail, even though the database CPU is idle?

The checkout transaction holds a row lock (and a pooled connection) while waiting for the API. Other checkouts queue on that lock and keep their own connections busy, so latency and pool usage grow without any database work being done.

How do you restructure a checkout so the payment call happens outside the transaction without overselling?

Reserve stock in a short transaction (conditional decrement + a 'reserved' order with an expiry), call the provider with an idempotency key outside any transaction, then confirm in a second short transaction. A sweeper releases expired reservations.

What does idle_in_transaction_session_timeout protect against?

Sessions that opened a transaction and then stopped sending queries (an app bug, a slow external call, a crashed client). Postgres terminates them after the timeout, releasing their locks and letting VACUUM clean up old row versions.

Long Transactions and Calling APIs Inside Them — Transactions, Locking & Concurrency in Code

Scenario

Example app: a ticket shop. Checkout locks the event's inventory row, calls the payment provider, then decrements stock and commits. The provider usually answers in 300 ms — but during an evening on-sale it takes 8–20 seconds.

What you see in production

During the on-sale, checkout p99 jumps from 0.4 s to 30 s, then requests fail with connection pool timeout errors. The database CPU is idle. pg_stat_activity shows dozens of sessions waiting for a lock and a few idle in transaction.

The naive code

with Session(engine) as session, session.begin():
    event = session.execute(
        select(Event).where(Event.id == event_id).with_for_update()   # row lock taken here…
    ).scalar_one()
    charge = payments.charge(card, amount)                            # …held during a slow HTTP call
    event.tickets_left -= qty
    session.add(Order(event_id=event_id, charge_id=charge.id, qty=qty))

@Transactional
public Order checkout(long eventId, int qty, Card card) {
    Event event = events.findByIdForUpdate(eventId);   // @Lock(PESSIMISTIC_WRITE)
    Charge charge = paymentClient.charge(card, price(event, qty));   // 8–20 s during incidents
    event.reserve(qty);
    return orders.save(new Order(event, qty, charge.id()));
}

DB::transaction(function () use ($eventId, $qty, $card) {
    $event = Event::whereKey($eventId)->lockForUpdate()->firstOrFail();
    $charge = $this->payments->charge($card, $event->price * $qty);   // HTTP call inside the lock
    $event->decrement('tickets_left', $qty);
    Order::create(['event_id' => $eventId, 'qty' => $qty, 'charge_id' => $charge->id]);
});

await prisma.$transaction(async (tx) => {
  await tx.$queryRaw`SELECT id FROM "Event" WHERE id = ${eventId} FOR UPDATE`;
  const charge = await payments.charge(card, amount);       // slow call while holding the lock
  await tx.event.update({ where: { id: eventId }, data: { ticketsLeft: { decrement: qty } } });
  await tx.order.create({ data: { eventId, qty, chargeId: charge.id } });
}, { timeout: 30_000 });

Reproduce it

Twenty buyers arrive 100 ms apart. Each holds the event row lock for the duration of the payment call. Compare a 300 ms provider with a slow 8 s one:

Lock held during a slow API call: waits pile upTry it yourself

def simulate(payment_seconds, buyers=20, gap=0.1, db_work=0.005):
    lock_free_at = 0.0
    waits = []
    for i in range(buyers):
        arrive = i * gap
        start = max(arrive, lock_free_at)            # wait for the row lock
        waits.append(start - arrive)
        lock_free_at = start + db_work + payment_seconds + db_work
    return waits

for label, seconds in [("healthy provider (0.3 s)", 0.3), ("slow provider (8 s)", 8.0)]:
    waits = simulate(seconds)
    print(f"{label:24} last buyer waits {waits[-1]:6.1f} s, "
          f"buyers waiting > 30 s: {sum(w > 30 for w in waits)}")

Output

healthy provider (0.3 s) last buyer waits    4.0 s, buyers waiting > 30 s: 0
slow provider (8 s)      last buyer waits  150.3 s, buyers waiting > 30 s: 16

Root cause

A transaction holds its row locks until commit. Anything slow inside the transaction — HTTP calls, file uploads, sleeping retries, waiting for a queue — extends the lock for everyone queued behind it, and also holds a pooled connection the whole time. Long transactions also hurt Postgres MVCC: old row versions can't be vacuumed while an old snapshot is open, so tables and indexes bloat.

The fix

Split the work into short transactions around the database work, and do the slow call in between, made safe with a reservation and an idempotency key:

# 1) short transaction: reserve
with Session(engine) as s, s.begin():
    updated = s.execute(
        update(Event).where(Event.id == event_id, Event.tickets_left >= qty)
        .values(tickets_left=Event.tickets_left - qty)
    ).rowcount
    if not updated:
        raise SoldOut()
    order = Order(event_id=event_id, qty=qty, status="reserved", expires_at=now() + timedelta(minutes=10))
    s.add(order)
# 2) no transaction, no locks: talk to the provider
charge = payments.charge(card, amount, idempotency_key=f"order-{order.id}")
# 3) short transaction: confirm
with Session(engine) as s, s.begin():
    s.execute(update(Order).where(Order.id == order.id).values(status="paid", charge_id=charge.id))

public Order checkout(long eventId, int qty, Card card) {
    Order order = reservations.reserve(eventId, qty);          // @Transactional, milliseconds
    Charge charge = paymentClient.charge(card, order.total(), "order-" + order.getId());
    return reservations.confirm(order.getId(), charge.id());   // @Transactional, milliseconds
}

$order = DB::transaction(fn () => $this->reservations->reserve($eventId, $qty));  // short
$charge = $this->payments->charge($card, $order->total, "order-{$order->id}");    // outside
DB::transaction(fn () => $order->markPaid($charge->id));                         // short

const order = await reserve(eventId, qty);                         // short $transaction
const charge = await payments.charge(card, order.total, `order-${order.id}`);
await prisma.order.update({ where: { id: order.id }, data: { status: "PAID", chargeId: charge.id } });

A background job releases reservations that expire unpaid. Add guard rails in the database too:

-- Postgres: kill sessions that sit idle inside a transaction, and cap lock waits
ALTER ROLE app SET idle_in_transaction_session_timeout = '15s';
ALTER ROLE app SET lock_timeout = '3s';

Trade-offs

Splitting the transaction means handling intermediate states (reserved, paid, expired) and a cleanup job — more code, but every state is explicit and recoverable.
The external call needs an idempotency key so a retry after a timeout doesn't double-charge (see Idempotency & Retry-Safe Systems).
Short lock_timeout values turn queues into fast errors; pair them with a friendly "busy, try again" response.

Detect it in production

-- Oldest open transactions and what they are doing (Postgres)
SELECT pid, now() - xact_start AS xact_age, state, wait_event_type, left(query, 80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Alert on transaction age (e.g. > 5 s) and on idle in transaction sessions.
Trace spans: an HTTP client span nested inside a DB transaction span is a red flag.
MySQL: information_schema.innodb_trx shows trx_started for each open transaction.

Key points

Locks and connections are held until commit — keep transactions to milliseconds.
Never call external services inside a transaction; reserve, call, confirm.
Use explicit intermediate states plus cleanup for expired reservations.
Set idle_in_transaction_session_timeout and lock_timeout as guard rails.