openskills.info
Course catalog
Course Preview

Deadlocks and Lock Contention Troubleshooting

Database lock contention occurs when concurrent transactions need incompatible access to the same resource. A deadlock is a cycle in those waits; troubleshooting identifies the waiting chain, the lock holder, and the transaction design that created it.

itDatabases and data storage

Don't Panic — Deadlocks and Lock Contention Troubleshooting

Lock contention is what happens when two transactions need access that cannot be granted at the same time. The database is not being difficult for sport. A lock protects a resource while a transaction reads or changes it, so concurrent work does not turn into a collection of accidental interpretations of the same data.

The first useful distinction is wonderfully unsentimental. Blocking means one transaction waits for another, but the holder can still commit or roll back and release the lock. A deadlock is worse in a very specific way: the wait comes back around in a cycle. Transaction A holds what B needs; B holds what A needs. The database ends the stalemate by aborting a victim. Progress resumes, though one request receives the bill.

The map for this small administrative drama is a wait-for graph. Each transaction or session is a node. An arrow points from a waiter to the holder. Follow arrows to a head blocker and you have a chain. Follow them until they return to an earlier node and you have a deadlock. The slow request that prompted the investigation is often merely the messenger, which is an unfair job but a common one.

The surprising part is that current SQL can be poor evidence. A transaction may acquire a lock, run another statement, and then sit idle while still holding it. That is why transaction start time, earlier statements, session state, application identity, and the resource itself belong in the incident record. Live lock views are snapshots. Logs, event capture, sampling, and application telemetry remember the bit that disappeared while someone found the dashboard.

Durable fixes live in the shape of the work. Acquire shared resources in a consistent order. Keep transactions no longer than the business invariant needs. Move network calls and unrelated computation outside them. Use selective access paths so a statement does not touch more rows than intended. Retries matter too, but only for the entire failed transaction, only after cleanup, and only when repeating external effects is safe. A retry loop is a recovery mechanism, not a forgiving substitute for a design.

For the fuller map, use the Intro for the concurrency path and engine-specific interpretation. Open Slides for the graph shapes and investigation order. Keep the Cheatsheet nearby during an incident for the evidence bundle and PostgreSQL, MySQL InnoDB, and SQL Server observation surfaces. The Quiz is where the terminology stops pretending it can hide behind a diagram.

Where this skill leads

Relevant careers

See how this topic contributes to broader role-level skill maps.

Sources