This content originally appeared on DEV Community and was authored by Deepana
🧩 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
This content originally appeared on DEV Community and was authored by Deepana
Deepana | Sciencx (2025-10-04T16:47:58+00:00) 🧾 Transactions, Deadlocks & Log-Based Recovery in DBMS – A Practical Guide. Retrieved from https://www.scien.cx/2025/10/04/%f0%9f%a7%be-transactions-deadlocks-log-based-recovery-in-dbms-a-practical-guide/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.