Database Concurrency & Scaling Interview Questions

Summary

A lost update: both requests read stock = 1, then each wrote back a value computed in application code. Fix it with an atomic conditional update (UPDATE … SET stock = stock - 1 WHERE id = ? AND stock >= 1 and check affected rows), a row lock, or a version check.

Longer Answer

Read-modify-write in the application (product.stock -= 1; save()) splits the check and the write. Under load, two transactions read the same value before either writes. Neither Read Committed nor MySQL's Repeatable Read prevents this, because the plain SELECT takes no lock.

Options, in order of preference:

1. Atomic conditional update: the database checks and decrements in one statement; zero affected rows means sold out.
2. SELECT … FOR UPDATE when the decision needs several reads.
3. Optimistic version check (WHERE version = ?) when conflicts are rare.
4. Add CHECK (stock >= 0) as a last line of defence, and load-test with concurrent requests on the same item.

Key Points

• Read-modify-write loses updates
• Atomic conditional UPDATE + affected rows
• Locks or versions for multi-step logic
• CHECK constraint as a safety net

Deep dive: Lost Updates

Summary

Read the deadlock reports to find the two statements and tables involved, map them to the two code paths, and compare their lock order. Usually a new code path locks the same rows in a different order (or holds locks longer); fix the ordering and keep a retry for the remainder.

Longer Answer

1. Get the report: Postgres logs deadlock detected with Process X waits for ShareLock on transaction Y; blocked by process Z and each blocked query; MySQL shows LATEST DETECTED DEADLOCK in SHOW ENGINE INNODB STATUS (enable innodb_print_all_deadlocks).
2. Remember the queries shown are the ones that were blocked; the conflicting locks were taken earlier in the same transactions.
3. Correlate with the deploy: which new code touches those tables? A loop over cart lines in customer order, a new FK insert, a longer transaction with an HTTP call inside.
4. Fix: lock rows in a deterministic order (sort ids), shorten transactions, add missing indexes (fewer rows locked), move external calls out.
5. Keep a retry for deadlock errors (40P01 / 1213) — but only once the transaction body is side-effect free.

Key Points

• Read the database's deadlock report
• Find both code paths and compare lock order
• Deterministic lock order + short transactions
• Retry as a safety net, not the fix

Deep dive: Reading Deadlock Reports

Summary

Connections are being held while not doing database work — usually an HTTP call, slow rendering or open-session-in-view inside a transaction. Pool throughput is pool size ÷ hold time, so long holds drain the pool while the database idles.

Longer Answer

Check pg_stat_activity for many idle in transaction sessions and look at traces for external calls nested inside database spans. Fixes:

• Release connections before external calls; never call APIs inside a transaction.
• Disable open-session-in-view (spring.jpa.open-in-view=false).
• Fail fast with a short pool timeout and shed load instead of queueing for 30 s.
• Size pools from database capacity, not traffic, and use a pooler (PgBouncer) when instances multiply.

Raising the pool size usually just moves the bottleneck to max_connections.

Key Points

• Throughput = pool size ÷ hold time
• No external calls while holding a connection
• Short pool timeouts
• Don't fix it by raising pool sizes

Deep dive: Connection Pools and Transactions

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:

1. Reserve (conditional update + reserved row with expiry) and commit.
2. Call the provider with an idempotency key derived from the reservation.
3. 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

Summary

Read Committed gives each statement a fresh view of committed data; Repeatable Read gives the whole transaction one snapshot. Multi-statement reports can see inconsistent data under Read Committed.

Longer Answer

• Postgres defaults to Read Committed; its Repeatable Read is snapshot isolation and raises serialization errors when a concurrently updated row is written.
• MySQL InnoDB defaults to Repeatable Read; plain reads use a snapshot, but locking reads and UPDATEs see the latest data — so read-modify-write can still lose updates.
• Neither prevents write skew; that needs locks, constraints or SERIALIZABLE.

Use Repeatable Read (read-only) for consistent reports; set isolation explicitly when correctness depends on it.

Key Points

• RC: per-statement snapshot
• RR: per-transaction snapshot
• Different defaults in Postgres and MySQL
• Neither stops write skew

Deep dive: Isolation Levels in Postgres vs MySQL

Summary

Rank statements by total time (calls × mean) using pg_stat_statements or performance_schema digests, fix the top entries (indexes, N+1, pagination), and verify with EXPLAIN ANALYZE — usually far cheaper than scaling hardware.

Longer Answer

The slowest single query is rarely the biggest load; a 30 ms query called millions of times usually is. Look at calls per request too (N+1), and compare estimated vs actual rows in plans for statistics problems. Then consider caching and read replicas for what remains.

Key Points

• Optimise by total time
• pg_stat_statements / performance_schema
• Verify with EXPLAIN ANALYZE
• Hardware last

Deep dive: Finding the Queries That Matter

Summary

Provide read-your-writes: route that user's reads to the primary for a window after a write, or record the primary's LSN/GTID after the write and only read from replicas that have replayed past it.

Longer Answer

Asynchronous replicas are always slightly behind. Options:

• Sticky window: store wrote_at in the session; for N seconds read from the primary.
• Position tracking: pg_current_wal_insert_lsn() after the write; compare with pg_last_wal_replay_lsn() on replicas (MySQL: WAIT_FOR_EXECUTED_GTID_SET).
• Monitor lag and stop using replicas beyond your staleness budget.

Key Points

• Async replicas lag
• Sticky-to-primary after writes
• LSN/GTID position checks
• Lag-aware routing

Deep dive: Read-Your-Writes

Summary

Pick the entity almost every query and transaction is scoped to — usually the tenant/workspace — and co-locate related tables by it. Use many logical shards on fewer physical databases, and directory overrides for whale tenants.

Longer Answer

A key misaligned with access patterns forces fan-out queries and cross-shard transactions. Notion sharded by workspace ID into 480 logical shards over 32 physical databases; Figma separated logical from physical sharding with a routing proxy. Keep global, cross-tenant tables (users, billing) unsharded or in a separate store.

Key Points

• Shard by the scope of queries/transactions
• Co-locate related tables
• Logical vs physical shards
• Directory overrides for whales

Deep dive: Sharding Strategies

Summary

Expand/contract: add the new column, deploy code that writes both and backfill in batches, switch reads to the new column, and drop the old one only after no running code uses it.

Longer Answer

During rolling deploys old and new code run against the same schema, so every step must work with both. Use online DDL (CREATE INDEX CONCURRENTLY, NOT VALID constraints then VALIDATE, ALGORITHM=INSTANT, gh-ost for large MySQL tables) and set lock timeouts on migrations.

Key Points

• Old and new code coexist
• Expand → migrate → contract
• Batched backfill
• Online DDL + lock timeouts

Deep dive: Zero-Downtime Schema Changes

Summary

Use the transactional outbox: insert the event into an outbox table in the same transaction as the order, and publish it via a relay or CDC (Debezium). Consumers dedupe because delivery is at least once.

Longer Answer

Writing to the database and broker separately can't be atomic: commit-then-publish loses events on crashes; publish-then-commit emits events for rolled-back orders. The outbox makes the event part of the database transaction. Monitor the outbox backlog and, for CDC, replication slot lag.

Key Points

• Dual writes are not atomic
• Outbox in the same transaction
• Relay or CDC publishes
• Idempotent consumers

Deep dive: Outbox and CDC

Summary

One short transaction per attempt with an atomic conditional decrement and an idempotency key; payment outside the transaction; expiring holds returned by a sweeper; spread or meter the hot inventory rows (buckets or an admission queue); and isolate the endpoint's pool.

Longer Answer

Never hold locks during payment calls. Use a constraint (stock >= 0) as the final guard, load-test with concurrent requests on the same SKUs, and monitor invariants (held + paid ≤ initial stock) live.

Key Points

• Atomic decrement + idempotency key
• Reserve → pay → confirm
• Expiring holds
• Spread/meter hot rows

Deep dive: Capstone: Never Oversell

Summary

Either the method wasn't actually transactional (self-invocation bypasses the proxy, or the method isn't public on a Spring bean) or the exception was a checked exception, which commits by default. Also check for swallowed exceptions and REQUIRES_NEW calls.

Longer Answer

• Self-invocation: this.placeOrder() from the same class doesn't go through the proxy — no transaction at all.
• Checked exceptions: Spring rolls back only on RuntimeException/Error unless rollbackFor is set.
• Swallowed exceptions: catching the exception inside the transactional method means the interceptor never sees it.
• Propagation.REQUIRES_NEW inner calls commit independently of the outer transaction.
• Different transaction managers / datasources: the write went through a connection not bound to the transaction.

Fix by moving the method to another bean (or calling through the proxy), using rollbackFor = Exception.class where appropriate, rethrowing, and testing the failure path.

Key Points

• Proxy-based: self-calls skip it
• Checked exceptions commit by default
• Don't swallow exceptions
• Beware REQUIRES_NEW

Deep dive: Framework Transaction Pitfalls