Deadlocks Kill Performance: Fix Them Fast
Understand the causes, see them in action, and stop stalled transactions in your database.
Picture this: you’re in a narrow alley, face to face with someone coming from the opposite side. You both pause. You wait for them to move, they wait for you. Nobody budges. Congrats. You’ve just invented the world’s most awkward standoff.
That’s exactly what a deadlock looks like in a database. Two (or more) transactions are stubbornly waiting on each other, and since neither gives in, everything just…freezes. Think of it as rush-hour traffic with no cop, no signals, and every driver convinced they have the right of way. Lots of honking, zero movement.
Frustrating? Absolutely. In database terms, it means queries hang, users see errors, and performance flatlines. Let’s peel back the curtain and see how this mess actually happens.
What Exactly Is a Deadlock?
In simple terms:
A deadlock happens when two or more transactions are waiting on each other to release resources, and because of that, none of them can ever finish.
Think of resources as “reserved tables” in a restaurant. Transaction A has reserved the “Employees” table, and Transaction B has reserved the “Salaries” table. Now A wants the Salaries table, but it’s already taken. Meanwhile, B wants the Employees table, but A won’t let go.
Further Expanding this:
Step 1: The Tables
We have two tables:
Step 2: Two Transactions Start at the Same Time
Imagine Transaction A is run by one user and Transaction B by another.
Transaction A (User 1)
BEGIN TRANSACTION;
-- Step 1: Lock Employees row for EmpID=1
UPDATE Employees
SET Dept = 'Finance'
WHERE EmpID = 1;
-- Step 2: Then try to lock Salaries row for EmpID=1
UPDATE Salaries
SET Salary = 52000
WHERE EmpID = 1;
At this point, A is holding a lock on Employees.EmpID=1.
Transaction B (User 2)
BEGIN TRANSACTION;
-- Step 1: Lock Salaries row for EmpID=1
UPDATE Salaries
SET Salary = 65000
WHERE EmpID = 1;
-- Step 2: Then try to lock Employees row for EmpID=1
UPDATE Employees
SET Dept = 'Operations'
WHERE EmpID = 1;
At this point, B is holding a lock on Salaries.EmpID=2.
Step 3: The Conflict
Transaction A locks Employees (EmpID=1).
Transaction B locks Salaries (EmpID=1).
A tries to update Salaries (EmpID=1) → but B is holding it → A waits.
B tries to update Employees (EmpID=1) → but A is holding it → B waits.
Now we have the cycle:
A waits for B.
B waits for A.
That’s the deadlock
The Four Conditions of Deadlock
Deadlocks don’t just happen randomly; they need the right ingredients, like a recipe for chaos. These four conditions, known as Coffman’s conditions, must all exist at once for a deadlock to occur:
Mutual Exclusion
Only one transaction can use a resource at a time.
Example: Only one person can stand on a stair step at once. No sharing.
Hold and Wait
A transaction holds on to one resource while waiting for another.
Example: You’re holding your spot on the step, but you won’t climb further until the person ahead moves.
No Preemption
Locks can’t be forcibly taken away. The transaction must finish (commit/rollback) to release them.
You can’t just shove someone off their step. (Well, NO!)
Circular Wait
Transactions are waiting in a loop. A waits for B, B waits for C, and C… waits for A.
It’s like three Spidermen pointing at each other, each saying, “You move first.”
When all four show up together, congratulations you’ve got yourself a deadlock.
How Databases Handle Deadlocks
The good news is, databases aren’t clueless. They’ve got strategies for dealing with deadlocks. Let’s look at the three main ones.
1. Deadlock Prevention
This strategy tries to stop deadlocks before they even happen.
How? Break at least one of the four conditions.
Example: force transactions to request all the resources they’ll need upfront. If they can’t get them, they wait without holding anything.
Or, enforce a strict order always lock the Employees table before Salaries.
Pros: Deadlocks can’t occur.
Cons: Feels like over-planning. It limits flexibility and may slow down performance.
Think of it like banning narrow lanes altogether, no chance of people getting stuck. But also… less freedom to roam.
2. Deadlock Avoidance
Here, the database plays chess. It thinks ahead before granting a resource.
How? It runs algorithms (like the Banker’s Algorithm) to check if granting a request might cause a future deadlock. If yes, the request waits.
Pros: Smarter than prevention, allows more concurrency.
Cons: Needs prior knowledge of how many resources each transaction might want, which isn’t always possible.
It’s like having a bouncer who scans the crowd at a club and says, “Hmm, if I let you in now, there might be a fight later. You wait.”
3. Deadlock Detection & Recovery (Most Common)
This is the real-world favorite. Instead of avoiding deadlocks, the system assumes they will happen and then fixes them.
How?
Detection: The DBMS builds a “Wait-For Graph.” Each arrow means “this transaction is waiting on that one.” If the graph has a cycle → deadlock spotted!
Recovery: The system picks a “victim” transaction to kill (rollback). That transaction releases its resources, breaking the cycle, and everyone else can move on.
Pros: Practical, balances performance and correctness.
Cons: Someone has to be the victim (a transaction loses progress).
It’s like the traffic police showing up, towing one car away, and letting the rest move forward.
Wrapping It Up
Deadlocks happen when four conditions come together: mutual exclusion, hold and wait, no preemption, and circular wait.
There are three main ways to address them:
Prevention – effective, but usually too restrictive for real systems.
Avoidance – smart in principle, but not scalable in practice.
Detection & Recovery – the practical strategy adopted by most modern DBMS like SQL Server, Oracle, and PostgreSQL.
At the end of the day, deadlocks aren’t mysterious, they’re just the result of how transactions compete for resources. The good news is that a few simple habits, like always accessing tables in a consistent order, can drastically reduce the chances of running into them. Combine that with the built-in safeguards of your database, and you’ll find deadlocks are less of a nightmare and more of a manageable hiccup in the life of your application.
Resources
Introduction to SQL: Coursera