🧩 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.

📘 What You’ll Learn

• ✅ How atomicity prevents partial updates


• ⚡ How deadlocks can occur in multi-session environments


• 🔄 How log-based recovery restores data safely

🙏 Special Thanks

A heartfelt thank you to Santhosh NC Sir for his guidance and continuous support throughout this DBMS assignment.

🏷️ Tags

dbms #oracle #sql #transactions #database #students #learning