We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
When two or more transactions are each waiting for the other to release a lock, creating a cycle where none can proceed. Databases detect and break deadlocks by aborting one.
In short
A deadlock is a situation where two or more transactions or threads are each holding a lock the other needs and waiting for the other to release it, forming a cycle in which none can ever proceed. Databases break the deadlock by detecting the cycle and aborting one transaction, which then has to retry.
In short
A deadlock is a situation where two or more transactions or threads are each holding a lock the other needs and waiting for the other to release it, forming a cycle in which none can ever proceed. Databases break the deadlock by detecting the cycle and aborting one transaction, which then has to retry.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Deadlock across a run of lessons, not one page. These 4 alone are about 128 minutes of step-by-step reading, 2 of them with code you run in the browser, all with a quiz.
Renaming one category rewrote 200,000 rows in a flat table and 1 in a normalised one. A partial update left the flat table holding two answers to one question, and the join it costs measured 1.34 times.
Reuse threads instead of creating them, the pattern that makes web servers handle thousands of concurrent connections.
Coordinating atomic operations across multiple nodes, the hardest problem in databases.
Mutual exclusion across network boundaries. Redlock, ZooKeeper locks, and why they're harder than you think.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Database Normalizationpay once,
yours for life
A lock is a way to claim exclusive access to a row, table, or other resource so two transactions do not corrupt each other's work. A deadlock happens when transactions grab those locks in a different order and then wait on each other in a loop.
The classic example uses two rows, A and B. Transaction 1 locks row A and then asks for row B. At the same time, Transaction 2 locks row B and then asks for row A. Transaction 1 cannot get B because Transaction 2 holds it, and Transaction 2 cannot get A because Transaction 1 holds it. Neither will give up its lock until it finishes, and neither can finish. That is a deadlock.
A deadlock is not the same as slowness or a long wait. In a normal wait, one transaction will eventually get the lock once the holder commits. In a deadlock, the wait is circular, so it would last forever if nothing intervened. There are four conditions that must all hold for a deadlock: mutual exclusion, hold and wait, no preemption, and a circular wait. Remove any one of them and the deadlock cannot form.
Most databases keep a wait-for graph, a small in-memory structure where each node is a transaction and each edge points from a transaction that is waiting to the transaction that holds the lock it wants. When that graph contains a cycle, a deadlock exists.
A background process scans this graph on an interval. In PostgreSQL the check runs after a transaction has been blocked for deadlock_timeout, which defaults to one second. MySQL with InnoDB keeps the wait-for graph updated continuously and detects cycles almost immediately. When a cycle is found, the engine picks a victim, usually the transaction that has done the least work or holds the fewest locks, and aborts it. The victim gets an error such as PostgreSQL SQLSTATE 40P01 or MySQL error 1213, and its locks are released so the survivors can move on.
Some systems use a timeout instead of cycle detection. They do not build a graph at all. They simply give up if any single lock wait exceeds a limit, on the assumption that a very long wait probably means a deadlock. This is cheaper but can falsely abort transactions that were just slow, and it can let a real deadlock sit until the timeout fires.
The most effective fix is consistent lock ordering. If every transaction always locks rows in the same order, say by primary key ascending, a circular wait cannot form, so a deadlock is impossible. The two transactions in the earlier example would both try to lock A before B, and one would simply wait normally for the other to finish.
Other practical defenses are keeping transactions short so locks are held for less time, touching fewer rows per transaction, and lowering the isolation level when serializable guarantees are not needed since higher levels take more and broader locks. Some teams use SELECT ... FOR UPDATE to grab all the rows they will need up front, in a known order, before doing any work.
The trade-off is that you can never fully design deadlocks away in a busy system, so production code must treat them as a normal, retryable error. The standard pattern is to catch the deadlock error code and retry the whole transaction a few times with a short randomized backoff. Aborting one victim is cheap and correct, while trying to prevent every possible deadlock would require so much locking that throughput would collapse.
Picture a banking app transferring money between two accounts. Transfer X moves money from account 100 to account 200. Transfer Y moves money from account 200 to account 100. Both run at the same instant.
Transfer X locks account 100 to debit it, then reaches for account 200 to credit it. Transfer Y has already locked account 200 to debit it and now reaches for account 100. Each holds what the other needs. The database detects the cycle within about a second, aborts one transfer with a deadlock error, and releases its locks. The surviving transfer completes, and the application retries the aborted one, which now succeeds.
The clean fix here is to always lock the lower account number first. Both transfers would then start by locking account 100, and one would wait politely for the other instead of deadlocking. This is exactly why payment and inventory systems sort the resources they touch before locking them.
PostgreSQL
Runs deadlock detection after a lock wait exceeds deadlock_timeout (default 1s), aborts a victim with SQLSTATE 40P01.
MySQL InnoDB
Maintains a wait-for graph continuously and rolls back the cheapest transaction with error 1213 the moment a cycle appears.
Oracle Database
Detects deadlocks automatically and raises ORA-00060, rolling back one statement to break the cycle.
SQL Server
A background deadlock monitor scans for cycles and kills a victim with error 1205, choosing the lowest-cost transaction.
A normal lock wait ends as soon as the holder commits or rolls back, so it always resolves on its own. A deadlock is a circular wait where each transaction is blocked by the other, so it would never resolve without the database aborting one of them.
Catch the deadlock error code (40P01 in PostgreSQL, 1213 in MySQL) and retry the entire transaction, ideally with a short randomized backoff. To reduce how often they happen, lock rows in a consistent order, keep transactions short, and touch fewer rows.
In theory yes, by always acquiring locks in the same global order so a circular wait cannot form. In practice, busy systems with complex access patterns cannot guarantee this everywhere, so the realistic strategy is to make deadlocks rare and handle the leftovers with retries.
Because waiting would last forever. The cycle means no transaction can release its locks until it finishes, and none can finish. Aborting one victim releases its locks and lets the others complete, which is the cheapest way to break the cycle.
Yes. Any system with multiple locks can deadlock, including multithreaded application code using mutexes and distributed systems holding locks across services. The same four conditions and the same lock-ordering fix apply everywhere.
Transaction
A sequence of database operations treated as a single atomic unit. Either all operations succeed (commit) or none of them do (rollback).
Isolation Level
Controls how much one transaction can see changes made by other concurrent transactions. Ranges from Read Uncommitted (fastest, least safe) to Serializable (slowest, safest).
Distributed Lock
A lock that coordinates access to a shared resource across multiple machines. Implemented via Redis (Redlock), ZooKeeper, or etcd. Much harder than local locks.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
This page explains the idea. The course covers it step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
See the System Design Masterclass coursecourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Deadlock in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
