📝 Understanding Transactions, Deadlocks & Log-Based Recovery in SQL 💾

👋 Introduction

Have you ever wondered what happens when two people try to withdraw money from the same bank account at the same time?
Databases handle such cases using transactions, locks, and recovery logs
to make sure data always stays consistent.

In this blog, we’ll explore:

• How transactions maintain atomicity
• What a deadlock is and how it’s detected
• How log-based recovery ensures data safety after a crash

CREATE TABLE Accounts (
    acc_no INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
);

INSERT INTO Accounts VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);

⚙️ 2️⃣ Transaction – Atomicity & Rollback

🧠 Concept:
A transaction ensures that all steps succeed or none at all.
If any step fails, the database will rollback to the previous stable state.

START TRANSACTION;
UPDATE Accounts
SET balance = balance - 500
WHERE name = 'Alice';
UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';
SELECT * FROM Accounts;
ROLLBACK;
SELECT * FROM Accounts;

✅ Result:
All updates are undone after rollback → no partial transaction is saved.
This proves Atomicity — either all or none of the operations take effect.

🔄 3️⃣ Deadlock Simulation

🧠 Concept:
A deadlock happens when two transactions wait forever because each holds a lock the other needs.
Let’s simulate it using two SQL sessions.
🪟 Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice'; 
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

🪟 Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 50 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 50 WHERE name = 'Alice';

📜 4️⃣ Log-Based Recovery

🧠 Concept:
Every database maintains logs (like MySQL Binary Logs or PostgreSQL WAL) that record each change.
If a crash happens, the DB can redo committed and undo uncommitted transactions.

SHOW VARIABLES LIKE 'log_bin';
START TRANSACTION;
UPDATE Accounts SET balance = 900 WHERE name = 'Alice';
ROLLBACK;

Then check the logs:

SHOW BINLOG EVENTS LIMIT 10;

You’ll see records for the update and rollback.

✅ Result:
The undo operation is recorded, ensuring the database can recover correctly after any failure.

🧠 Conclusion

Through this activity, we learned:

• Transactions ensure data integrity (Atomicity, Consistency)
• Deadlocks are automatically detected and handled by DBMS
• Logs help recover data during failures

💡 Key takeaway: Databases are smart enough to protect your data even when things go wrong!