Modern databases are designed to be reliable, consistent, and fault-tolerant — even during crashes or concurrent access.
Three critical concepts that make this possible are:
Transactions, Deadlocks, and Log-Based Recovery.
This blog demonstrates how they work using a simple Accounts table example in SQL.
🧱 Step 1: Create the Accounts Table
We’ll begin by creating a basic table to simulate real-world banking operations.
To verify the data:
✅ Output:
This table will be used to understand how transactions and recovery work internally.
🔹 1️⃣ Transaction – Atomicity & Rollback
A transaction is a sequence of SQL operations executed as a single logical unit.
It follows the ACID properties — Atomicity, Consistency, Isolation, and Durability.
Let’s focus on Atomicity, which ensures that either all operations in a transaction succeed or none do.
Example: Money Transfer
Suppose Alice sends ₹500 to Bob.
We’ll perform the transfer and then roll it back before committing.
✅ Result:
Balances remain unchanged after rollback.
This confirms no partial update took place — a real demonstration of atomicity.
💡 Why It Matters:
If a power failure or crash occurs mid-transfer, rollback ensures that incomplete operations are undone automatically.
🔹 2️⃣ Deadlock Simulation
A deadlock occurs when two transactions are waiting for each other’s locked resources.
This often happens in multi-user environments where concurrent access is common.
Real-World Analogy:
Imagine two people trying to withdraw money from two linked accounts at the same time — each holding a lock on one account and waiting for the other.
Session 1
Session 2
⏳ Both sessions wait indefinitely — creating a deadlock.
How Databases Handle It:
Most DBMSs (like MySQL, Oracle, and PostgreSQL) use deadlock detection algorithms to automatically abort one of the conflicting transactions.
This allows the other transaction to continue and keeps the database consistent.
💡 Tip to Avoid Deadlocks:
- Access tables in a consistent order across transactions.
- Keep transactions short and simple.
- Avoid unnecessary locks.
🔹 3️⃣ Log-Based Recovery
Even with transactions and deadlock control, system failures can still occur.
That’s where log-based recovery ensures Durability — the “D” in ACID.
What It Means:
Databases maintain transaction logs (Binary Logs in MySQL, WAL in PostgreSQL) that record every change.
If a crash occurs, the DBMS uses these logs to redo committed transactions and undo uncommitted ones.
✅ Result:
The rollback operation is written to the log.
If a failure occurred, the system would automatically restore the database to the last consistent state.
💡 Insight:
Without logging, recovering from crashes or incomplete updates would be nearly impossible.
That’s why log-based recovery is essential for mission-critical systems like banking or e-commerce.
💡 Final Thoughts
Database reliability depends on how well it handles failures, concurrency, and recovery.
- Transactions guarantee atomic and consistent operations.
- Deadlocks teach us to manage concurrency wisely.
- Log-Based Recovery ensures no data is lost, even after system crashes.
By experimenting with these SQL commands, you gain a real understanding of how databases ensure data integrity and fault tolerance in the real world. ⚡
Top comments (0)