Database Concurrency & Scaling interview question · Most asked
Why shouldn't you call a payment API inside a database transaction?
Summary
The transaction holds row locks and a pooled connection for the whole call. A slow provider then blocks every other request needing those rows and exhausts the pool; and a rollback can't undo the charge.
Longer Answer
Restructure into short transactions around the database work:
- Reserve (conditional update +
reservedrow with expiry) and commit. - Call the provider with an idempotency key derived from the reservation.
- Confirm in a second short transaction (
WHERE status = 'reserved').
Add idle_in_transaction_session_timeout and lock_timeout as guard rails, and a sweeper for expired reservations.
Key Points
- Locks and connections held until commit
- External effects can't be rolled back
- Reserve → call → confirm
- Idempotency key on the external call
Deep dive: Long Transactions
More most-asked Database Concurrency & Scaling questions
- How do you prevent overselling in a flash sale with 50,000 buyers in the first minute?
- After a deploy, logs show hundreds of 'deadlock detected' errors per hour on checkout. Walk through how you'd investigate.
- Two customers buy the last item at the same moment and both orders are confirmed. What happened, and how do you prevent it?
- Your service throws 'connection pool exhausted' errors but database CPU is only 20%. What's going on?
- What's the difference between Read Committed and Repeatable Read in practice?
- Database CPU is at 85%. How do you decide what to optimise before buying a bigger server?
- A user saves their profile and the next page shows the old data. Reads go to replicas. How do you fix it?
- How do you choose a shard key for a multi-tenant SaaS?