DBMS - Transactions, Deadlocks & Log-Based Recovery

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