Working with databases is not just about storing data — it’s about ensuring reliability, atomicity, and consistency, especially when multiple users or processes are involved. In this post, we’ll explore three important concepts using a simple Accounts table:
✅ Transactions & Rollback (Atomicity)
🔒 Deadlock Simulation
📝 Log-Based Recovery
Setup: The Accounts Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);
INSERT INTO CustomerAccounts VALUES (1, ‘Emily’, 1000);
INSERT INTO CustomerAccounts VALUES (2, ‘Bobby’, 1500);
INSERT INTO CustomerAccounts VALUES (3, ‘Caleb’, 2000);
SELECT * FROM CustomerAccounts;
Transaction – Atomicity & Rollback
Transactions ensure all-or-nothing execution. Let’s try transferring 500 from Emily to Bobby, but roll it back midway.
Deduct 500 from Emily
UPDATE CustomerAccounts
SET balance = balance – 500
WHERE name=”Emily”;
Add 500 to Bobby
UPDATE Accounts
SET balance = balance + 500
WHERE name=”Bob”;
Rollback transaction
ROLLBACK;
** Check balances**
SELECT * FROM Accounts;
Deadlock Simulation
Deadlocks occur when two transactions wait on each other’s locks. Let’s simulate with two sessions:
Session 1:
— Lock Emily
UPDATE Accounts SET balance = balance – 100 WHERE name=”Emily”;
— Do NOT commit
Session 2:
— Lock Bobby
UPDATE CustomerAccounts SET balance = balance – 200 WHERE name=”Bobby”;
— Do NOT commit
Continuing Session 1
— Try updating Bobby (held by Session2)
UPDATE CustomerAccounts SET balance = balance + 100 WHERE name=”Bobby”;
Continuing Session 2
— Try updating Emily (held by Session 1)
UPDATE CustomerAccounts SET balance = balance + 200 WHERE name=”Emily”;
Log-Based Recovery
Modern DBMSs use logs (MySQL → Binary Log, PostgreSQL → WAL) to ensure durability and rollback safety.
— Update Caleb
UPDATE CustomerAccounts SET balance = balance + 300 WHERE name=”Caleb”;
— Rollback
ROLLBACK;
— Verify balances
SELECT * FROM CustomerAccounts;
Wrap Up
In this tutorial, we covered:
Transactions & Rollback → Ensures atomicity
Deadlock Simulation → Shows concurrency pitfalls
Log-Based Recovery → Demonstrates how databases ensure durability
These concepts form the backbone of ACID properties in relational databases.
Special thanks to @santhoshnc Sir for guidance throughout this assignment.
dbms #oracle #sql #transactions #deadlock #recovery #assignment
