Transactions, Deadlocks & Log Based Recovery

💾 Understanding Transactions, Deadlocks & Log-Based Recovery in DBMS

Databases are the backbone of modern applications — from banking and e-commerce to social networks. For a system to be reliable, it must handle multiple users, ensure data integrity, and recover gracefully from failures.

In this post, we’ll explore three key concepts that make this possible: Transactions, Deadlocks, and Log-Based Recovery.

🔄 What Is a Transaction?

A transaction in a Database Management System (DBMS) is a sequence of operations performed as a single logical unit of work.
The transaction must follow the ACID properties — Atomicity, Consistency, Isolation, and Durability.

In simple terms:

Either all operations of a transaction are successfully executed, or none of them take effect.

✳️ Example

Let’s take a simple bank transfer:

Debit ₹1000 from Account A

Credit ₹1000 to Account B

If either step fails, the transaction should rollback to its previous state — ensuring data accuracy.

⚙️ Concurrency & Deadlocks

In multi-user environments, multiple transactions may access the same data simultaneously. To maintain correctness, databases use locks to control access.

But sometimes, this leads to a problem called a Deadlock.

🧩 What Is a Deadlock?

A deadlock occurs when two or more transactions are waiting indefinitely for each other to release locks.

Example Scenario

Transaction T1 locks Row A and waits for Row B

Transaction T2 locks Row B and waits for Row A

Both are waiting forever — creating a deadlock!

💡 Deadlock Handling Strategies

Databases use different approaches to detect and resolve deadlocks:

Timeouts: Abort a transaction if it waits too long.

Wait-For Graphs: Detect cycles and terminate one transaction.

Consistent Resource Ordering: Access resources in a predefined order to prevent cycles.

A typical MySQL error might look like this:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

🧾 Log-Based Recovery

What happens if a system crashes in the middle of a transaction?

This is where log-based recovery comes in.

📘 What Is a Log?

A log file records all modifications made by transactions.
Before any change is applied to the database, the action is written to the log — a principle called Write-Ahead Logging (WAL).

🔁 Recovery Using Logs

After a crash:

Undo (Rollback): Revert the effects of incomplete transactions.

Redo: Reapply the effects of committed transactions not yet saved to disk.

This ensures data durability and atomicity — even after system failures.

✅ Summary
Concept Description
Transaction A logical unit of work that must be fully completed or fully undone.
Deadlock A situation where transactions wait indefinitely for each other’s resources.
Log-Based Recovery A mechanism to recover the database after a failure using transaction logs.
🚀 Final Thoughts

Understanding how transactions, deadlocks, and recovery mechanisms work is crucial for database professionals and developers alike.
These core principles ensure that our systems remain reliable, consistent, and fault-tolerant — even under heavy load or unexpected crashes.