Database.Middle.What is a deadlock in a database?

A deadlock in a database occurs when two or more transactions block each other, each waiting for a resource (e.g., a row or table) that the other transaction holds. As a result, none of the transactions can proceed—they’re stuck forever unless the system intervenes.

---

🔄 Simple Example of a Deadlock

Imagine two transactions:

🧵 Transaction A

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- waits for Transaction B to release row id=2

🧵 Transaction B

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- waits for Transaction A to release row id=1

Both are waiting for each other to release a lock, creating a cycle → 💥 deadlock.

🔒 How Deadlocks Happen

Transaction A	Transaction B
Locks row 1
	Locks row 2
Tries to lock 2	Tries to lock 1
🚫 Waits	🚫 Waits

Neither can proceed. Database must choose one to rollback to break the cycle.

---

🛡️ How Databases Handle It

Most databases (PostgreSQL, MySQL InnoDB, SQL Server) automatically detect deadlocks and:

• Abort one transaction
• Roll back changes
• Return an error (e.g., Deadlock found; try restarting transaction)

🧠 How to Prevent Deadlocks

✅ 1. Access resources in consistent order

-- Always lock row 1, then row 2 (not the reverse)

✅ 2. Keep transactions short

• Don’t hold locks for longer than needed.

✅ 3. Use appropriate isolation levels

• Lower levels (like READ COMMITTED) reduce locking.
• SELECT ... FOR UPDATE should be used carefully.

✅ 4. Retry on deadlock

• Implement retry logic for deadlock errors in your app.

🧪 Detecting Deadlocks

• PostgreSQL: pg_stat_activity, logs with deadlock detected
• MySQL: SHOW ENGINE INNODB STATUS
• SQL Server: Deadlock Graph in Management Studio

---

⚠️ Summary

Term	Meaning
Deadlock	Two transactions waiting on each other’s locks indefinitely
Result	DB engine kills one to free up resources
Solution	Use consistent locking order, short transactions, and retries