🧾 Transactions, Deadlocks & Log-Based Recovery in DBMS – A Practical Guide

🧩 Schema Setup

We’ll use a single table called Accounts.

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

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

Check your table:
SELECT * FROM Student_Fees;

⚙️ 1️⃣ Transaction – Atomicity & Rollback

Concept:
Atomicity ensures that a transaction is all or nothing.
If any part fails or if we manually rollback, all changes are undone.

Steps:

SAVEPOINT start_point;
UPDATE Student_Fees SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';
ROLLBACK TO start_point;
SELECT * FROM Student_Fees;

✅ Result:
Balances remain unchanged → proves Atomicity works!

🔁 2️⃣ Deadlock Simulation

Concept:
A deadlock happens when two transactions hold locks that each other needs.

Even though Oracle LiveSQL doesn’t support two sessions, we can understand the concept using example code:

💻 Session 1:
UPDATE Student_Fees SET fees_balance = fees_balance - 500 WHERE stud_name = 'Deepa';
UPDATE Student_Fees SET fees_balance = fees_balance + 500 WHERE stud_name = 'Meena';

💻 Session 2:
UPDATE Student_Fees SET fees_balance = fees_balance - 700 WHERE stud_name = 'Meena';
UPDATE Student_Fees SET fees_balance = fees_balance + 700 WHERE stud_name = 'Deepa';

Result: Both transactions wait on each other → deadlock detected → DBMS automatically rolls back one transaction.

Note: In LiveSQL, updates are sequential, so no deadlock error appears.
This section is for conceptual understanding.

🧠 3️⃣ Log-Based Recovery (Undo Demonstration)

Oracle automatically maintains undo/redo logs. Rollback uses undo logs to restore old data.

SAVEPOINT log_demo;
UPDATE Student_Fees
SET fees_balance = fees_balance + 1000
WHERE stud_name = 'Meena';
ROLLBACK TO log_demo;
SELECT * FROM Student_Fees;

Explanation: Undo logs restored the previous state → log-based recovery works.