On a database engine using strict two-phase locking, throughput on a contended workload rises as you add concurrent transactions and then collapses past a certain point. Explain the mechanism behind that collapse, and how you would design and operate the system to stay on the healthy side of the curve.

## The shape of the curve Plot throughput against the number of concurrently active transactions (the multiprogramming level, MPL) on a lock-based engine with a contended workload. Three regions appear: 1. **Rising** — resources are underused, conflicts are rare, each added transaction adds throughput. 2. **Flat knee** — conflicts become common; added concurrency mostly buys waiting. 3. **Collapse** — throughput falls, sometimes to near zero, while CPU and I/O sit idle. This is **lock thrashing**. The collapse is not gradual saturation; it is a feedback loop, which is why systems fall off it suddenly and why the usual reflex — add more workers — makes it worse. ## The mechanism Under strict 2PL a transaction holds every exclusive lock (and under the rigorous variant, every shared lock too) until it commits. Now note the crucial detail: **a blocked transaction does not release anything while it waits.** So each waiter is simultaneously a victim and an obstacle. As MPL rises: - the chance that a new transaction requests a lock someone already holds rises, - each new waiter extends the lock hold time of nothing — but it removes itself from the set of transactions making progress while keeping its own locks live, - which lengthens the *effective* hold time seen by everyone else, raising conflict probability again. Waits also **chain**: T3 waits on T2 which waits on T1, so T1's commit latency propagates to everyone behind it. Convoys form behind a single hot row, and one slow commit — a stalled fsync, a paused JVM, a slow replica ack — stalls the whole line. The classic analytical result (Gray and Reuter) is worth carrying: for small conflict probabilities, the expected number of conflicts grows roughly **linearly in MPL and quadratically in transaction length**, and deadlock rate grows far faster — roughly with the fourth power of transaction size. The practical reading is blunt: **transaction length is the most dangerous variable in the system**, and it appears squared. ## Design levers, in order of impact **1. Shorten the time locks are held.** This is the whole game. - Never make a network call, an external API request, a queue publish or a user interaction from inside an open transaction. A 200 ms remote call inside a transaction is 200 ms of held locks per execution. - Do reads, validation and computation *before* opening the write transaction where possible. - Because exclusive locks are held to commit anyway, **acquire the hottest row as late as possible** in the transaction. Reordering statements so the contended update is the last one before COMMIT cuts its hold time to nearly zero — a change that often buys more than any tuning knob. - Keep the commit path fast: group commit, sensible durability settings, and awareness that synchronous replication puts a network round trip *inside* the critical section. **2. Reduce the conflict itself.** - **Shard hot aggregates**: a single "total" row updated by every transaction serializes the whole workload. Split into N counter rows, update a random one, sum on read. This trades read cost for a factor-of-N reduction in conflict. - Prefer **inserting facts and aggregating** over updating a shared running total; inserts to distinct rows rarely conflict. - **Order access consistently** across code paths — it does not reduce blocking but sharply reduces deadlocks, which are the most expensive form of conflict since they waste all work done so far. - **Batch** many tiny transactions into fewer medium ones where the domain allows — but remember length is squared, so batching too aggressively pushes you back up the conflict curve. There is an optimum, and it is empirical. **3. Cap concurrency deliberately.** Past the knee, admission control *increases* throughput: bound the connection pool, and specifically bound the **writer** concurrency, letting queued work wait outside the lock manager rather than inside it. Waiting in a queue holds no locks; waiting on a lock does. This is the counter-intuitive move to be able to argue for, because the instinct under load is always to raise the pool size. **4. Take readers off the lock path.** If the engine offers snapshot reads, route reporting and dashboard queries there or to a replica so long analytical scans never hold shared locks against the OLTP writers. On a pure locking engine, a single long report can stall the write path entirely. **5. Choose granularity with eyes open.** Finer locks reduce false conflicts but cost lock-manager memory and CPU, and coarse escalation under a big statement can convert a manageable workload into a table-wide stall. The interaction to watch is a bulk update escalating and colliding with the OLTP path. **6. Transaction chopping, carefully.** Splitting one long transaction into a sequence of shorter ones is a large win for contention, and is only safe when the resulting pieces still preserve the invariants under interleaving — the formal condition involves checking that the chopping graph has no cycle mixing conflict and sibling edges. Doing this by intuition is how correctness bugs are introduced; treat it as a design exercise with explicit reasoning, and make each piece idempotent or independently retryable. ## Operating it Instrument the right things. A thrashing system shows **low CPU, low I/O, high lock-wait time, growing lock-wait queue depth, rising deadlock/rollback rate, and climbing p99 with a flat p50**. Utilization dashboards will report an idle, healthy machine. Alert on lock-wait time per transaction and on average lock hold time, not on CPU. Track the top contended objects continuously, since contention usually concentrates on a handful of rows. And have a load-shedding answer ready: when the system is past the knee, the correct emergency action is to *reduce* admitted concurrency, kill the longest-running transactions, and only then investigate. Scaling out application instances against a contended lock manager adds load to the exact resource that is failing. ## The framing that lands Say explicitly: under 2PL, a blocked transaction is still an obstacle, so contention is self-amplifying. The design goal is not "more concurrency" but **minimum lock hold time × minimum conflict probability**, with admitted concurrency capped just below the knee — and lock hold time is the term you can move by an order of magnitude with ordinary application changes.