A write-heavy OLTP service reports a rising number of database deadlock errors under load, and simply retrying them is no longer enough. What design and access-pattern changes reduce how often deadlocks occur in the first place?

## Start from the definition A deadlock requires a **cycle** of waits: T1 holds A and wants B while T2 holds B and wants A. Every avoidance technique attacks one of the three ingredients — the ordering that lets a cycle form, the *duration* over which locks overlap, or the *number* of rows locked. Retry handles the residue; design removes the cause. ## 1. Consistent lock ordering — the single biggest win If every transaction in the system acquires contended resources in the same global order, a cycle is impossible: a transaction can only ever wait on one that is further along the same order, so the wait-for graph is acyclic by construction. This is the database analogue of ordered mutex acquisition. In practice that means: - **Sort keys before multi-row writes.** A batch UPDATE that walks a list in arrival order will interleave with another batch's list. Sorting both by primary key before writing makes them queue behind each other instead of crossing. - **Fix the order in transfer-style operations.** A funds transfer that locks the source then the destination deadlocks with the reverse transfer. Lock the lower account id first, always, regardless of direction. - **Order across tables too**, not just rows. If one path writes `order` then `inventory` and another writes `inventory` then `order`, the same cycle appears at table-row level. Agree a canonical sequence and encode it in shared code rather than in each caller. - **Beware implicit order.** A single multi-row statement acquires locks in whatever order the access path produces — index order, table order, or a parallel plan's order — so two statements with different plans can still cross. Where it matters, drive updates from an explicitly ordered set of keys. ## 2. Short transactions Two transactions can only deadlock if their lock lifetimes overlap. Reducing hold time shrinks the window quadratically in effect, and it is often easier than reordering. - Do reads that do not need to be in the transaction *before* opening it. - Never hold a transaction across anything you do not control: a user's confirmation, an HTTP call to a payment provider, a message-broker read, a file upload. This turns a millisecond lock into a multi-second one and is a top cause of both deadlocks and lock-wait timeouts. - Split long batches into chunks that commit periodically, when the business semantics allow it. A batch that updates a million rows in one transaction holds a million locks for its whole runtime and will collide with everything. - Move expensive computation outside the transaction; compute first, then open, write, and commit. ## 3. Avoid lock upgrades A transaction that reads a row in shared mode and later updates it must upgrade to exclusive. If two transactions do that on the same row simultaneously, each holds a shared lock the other's upgrade must wait for — an instant deadlock, and one that scales badly because it happens on *reads* of popular rows. The remedies: take the exclusive lock on first access with a locking read; or, better, express the change as a single UPDATE statement that reads and writes atomically, so no upgrade path exists. The read-modify-write shape (`SELECT` the value, compute in application code, `UPDATE` it back) is the pattern to look for. ## 4. Lock fewer rows, and the right ones An UPDATE whose predicate cannot use a selective index forces the engine to examine — and, depending on the engine and isolation level, lock — far more rows than it changes. Two such statements collide even when their target rows are disjoint. Indexing the predicate columns narrows the locked set to something close to the affected rows and removes collisions that were never semantically necessary. Similarly, prefer statements that touch only the rows they need, rather than broad "update everything in this partition" sweeps that overlap with every other writer. ## 5. Reduce hot-row contention structurally Some deadlock storms are really contention storms around one row: a global counter, an aggregate total, a status row for a popular entity. Ordering does not help when everyone wants the *same* row. The fixes are structural — shard the counter into N rows and sum on read, maintain the aggregate asynchronously from an event stream, or move the update out of the request path entirely into a batch that runs single-threaded. Single-writer designs (one worker owns a key range) eliminate the contention rather than managing it, at the cost of throughput ceilings per key. ## 6. Use the evidence All of this should be driven by the engine's deadlock reports, which name the transactions in the cycle, the locks held and requested, and the statements involved. Two patterns show up immediately: the same pair of statements recurring means an ordering bug; the same statement always losing means starvation, usually a small transaction repeatedly colliding with a batch. Guessing at ordering fixes without reading the report usually rearranges code that was never in a cycle. ## What does not work Lowering the isolation level is not a deadlock fix — it changes which locks are taken but not the ordering, and it trades correctness for a symptom. Coarsening locks to table level removes deadlocks by removing concurrency, which is rarely the trade you want. Raising the lock-wait timeout does nothing for a real cycle, since the detector resolves it long before the timeout. And increasing the retry budget masks a rising rate rather than fixing it.