Transactions, Deadlocks & Log Based Recovery

Transactions, Deadlocks & Log-Based Recovery – Hands-on SQL Demo

**Databases are powerful because they ensure data integrity even when multiple users access the system at the same time. This is managed through transactions, deadlock detection, and log-based recovery.

In this blog, I demonstrate these concepts using a single Accounts table in Oracle Live SQL.**

📌 Schema Setup

We first create a table to simulate account balances:
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);

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

COMMIT;

1️⃣ Transaction – Atomicity & Rollback

Goal: Transfer ₹500 from Alice to Bob. Rollback before commit to ensure no partial update.
-- Start transaction
UPDATE Accounts
SET balance = balance - 500
WHERE name = 'Alice';

UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';

-- Rollback the transaction
ROLLBACK;

-- Check balances (should be unchanged)
SELECT * FROM Accounts;

✅ Balances remain:

Alice → 1000
Bob → 1500

Lesson: Atomicity ensures that either all operations in a transaction succeed, or none do.

2️⃣ Deadlock Simulation

Goal: Simulate a deadlock between two sessions.
Session 1: Lock Alice’s account and try to update Bob’s.
Session 2: Lock Bob’s account and try to update Alice’s.

🔥 Oracle detects circular wait → deadlock → one session is rolled back automatically.
Lesson: Deadlocks can occur when two transactions wait on each other’s locks. DBMS automatically resolves them.

3️⃣ Log-Based Recovery

Goal: Show that updates are tracked for recovery using logs.
-- Start transaction
UPDATE Accounts SET balance = balance + 1000 WHERE name = 'Alice';

-- Rollback
ROLLBACK;

-- Verify
SELECT * FROM Accounts;

✅ Even after rollback, balances are restored, demonstrating undo capability via internal logs.
Note: In Oracle Live SQL, redo/undo logs are internal. The rollback proves that log-based recovery works.

🎯 Summary
Through these demos, I explored:
✅ Atomicity & Rollback – all-or-nothing transactions
✅ Deadlocks – isolation and conflict resolution
✅ Log-Based Recovery – durability and undo operations

Special thanks to @santhoshnc Sir for guiding us to try these experiments hands-on in Oracle Live SQL.