💾Ensuring Database Stability: Transactions, Deadlocks & Log-Based Recovery

🧱Modern databases are built to stay consistent, reliable, and resilient — even when systems crash or multiple users access data at once.
Three major mechanisms that make this possible are Transactions, Deadlocks, and Log-Based Recovery.

In this guide, we’ll explore how each of these works in practice using a simple DB_Assignment table as an example.

🧱 Step 1: Set Up the DB_Assignment Table

• We’ll start by creating a basic table that mimics a real banking environment where multiple accounts interact.
• Once inserted, verify your data using a simple SELECT * FROM DB_Assignment; query.

✅ Output:
This table will serve as the foundation for testing transactions and recovery concepts.

🔹 1️⃣Transaction – Atomicity & Rollback

• A transaction is a collection of SQL commands treated as a single logical operation.
• It upholds the ACID principles — Atomicity, Consistency, Isolation, and Durability.
• Here, we’ll highlight Atomicity, which guarantees that either all steps in a transaction are applied or none are — there’s no halfway point.
• 💸 Example: Money Transfer
• Suppose Alice transfers ₹500 to Bob.
• We’ll begin the transfer but then roll it back before committing to observe atomicity.

START TRANSACTION;
UPDATE DB_Assignment SET balance = balance - 500 WHERE acc_no = 1;
UPDATE DB_Assignment SET balance = balance + 500 WHERE acc_no = 2;
ROLLBACK;
SELECT * FROM DB_Assignment;

✅ Result:
After rollback, both account balances stay the same — confirming that no partial update occurred.

💡 Why It’s Important:
If a power outage or software failure happens midway, the rollback mechanism prevents incomplete changes from corrupting the database.

🔹 2️⃣Deadlock Simulation:

• A deadlock happens when two transactions each hold a lock that the other needs, causing both to wait forever.
• This issue often surfaces when multiple users access and modify shared data simultaneously.

💼 Real-World Example:
Think of two cashiers trying to update two linked bank accounts — one has locked Account A and the other Account B.
Each is waiting for the other to release their lock — that’s a deadlock.

-- Session 1
BEGIN;
UPDATE DB_Assignment SET balance = balance - 500 WHERE acc_no = 1; -- Locks Alice
UPDATE DB_Assignment SET balance = balance + 500 WHERE acc_no = 2; -- Waiting on Bob

-- Session 2
BEGIN;
UPDATE DB_Assignment SET balance = balance - 300 WHERE acc_no = 2; -- Locks Bob
UPDATE DB_Assignment SET balance = balance + 300 WHERE acc_no = 1; -- Waiting on Alice

⏳ Both transactions are now stuck waiting — forming a deadlock.

🧠 How Databases Handle It:

• Modern DBMS (like MySQL, PostgreSQL, Oracle) automatically detect deadlocks and abort one transaction, allowing the other to complete successfully.
• This maintains database consistency without manual intervention.

💡Tips to Prevent Deadlocks:

• Always access tables and rows in a consistent order.
• Keep transactions short and efficient.
• Avoid unnecessary or long-held locks.

🔹 3️⃣Log-Based Recovery:

• Even with transactions and deadlock control, unexpected failures can still occur.
• This is where log-based recovery ensures the Durability part of ACID — guaranteeing that committed data survives system crashes.

⚙️ What It Does:

• Databases maintain transaction logs — for instance, Binary Logs in MySQL or Write-Ahead Logs (WAL) in PostgreSQL — that track every change made to the database.
• If a crash happens, the DBMS replays the log to redo committed transactions and undo incomplete ones.

START TRANSACTION;
UPDATE DB_Assignment SET balance = balance + 100 WHERE acc_no = 3;
ROLLBACK;

✅ Result:
The rollback entry gets recorded in the log.
If the system crashes, the recovery process reads this log and restores the database to the last consistent state.

💡Why It Matters:
Without logging, recovering from crashes or incomplete updates would be nearly impossible.
That’s why mission-critical systems like banking and e-commerce rely heavily on log-based recovery.

💡 Final Thoughts

Database reliability isn’t just about storing data — it’s about maintaining integrity during every possible failure or concurrency issue.

Transactions ensure atomic and consistent operations.

Deadlocks teach us how to manage concurrent processes safely.

Log-Based Recovery guarantees data durability even after unexpected crashes.

By practicing these concepts hands-on, you’ll understand how professional-grade databases achieve high fault tolerance and data safety in real-world scenarios. ⚡