💾 Transactions, Deadlocks & Log-Based Recovery (Hands-on SQL Guide)

Databases guarantee data consistency using ACID properties.
In this blog, we’ll explore Transactions, Deadlocks, and Log-Based Recovery with simple examples.

We’ll use the following schema:

CREATE TABLE Accounts (
    acc_no INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
);

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

1️⃣ Transaction – Atomicity & Rollback

Atomicity ensures that a transaction is all or nothing – either all operations succeed or none are applied.
👉 Example: Transfer 500 from Alice to Bob, but rollback before commit.

-- Start Transaction
BEGIN;

-- Deduct 500 from Alice
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';

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

-- Check balances (before rollback)
SELECT * FROM Accounts;

-- Rollback the transaction
ROLLBACK;

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

✅ After rollback → Balances remain the same as original.

2️⃣ Deadlock Simulation

A deadlock happens when two transactions hold locks that the other one needs, and neither can proceed.
👉 Steps to simulate:

Session 1

BEGIN;
-- Lock Alice's account
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';

-- Try updating Bob (will wait because Session 2 locks it)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

Session 2

BEGIN;
-- Lock Bob's account
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Bob';

-- Try updating Alice (will wait, creating deadlock)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

⚠️ At this point, the database detects a deadlock and aborts one transaction automatically.

ORA-00060: deadlock detected while waiting for resource

3️⃣ Log-Based Recovery

Databases maintain transaction logs (Redo & Undo logs) to ensure durability and recovery.
👉 Let’s test with a transaction and rollback.

-- Start transaction
BEGIN;

-- Try updating Charlie's account
UPDATE Accounts SET balance = balance + 300 WHERE name = 'Charlie';

-- Rollback
ROLLBACK;

✅ You’ll find entries for the UNDO operation confirming rollback was recorded in the log.

🎯 Conclusion

1.Transactions ensure data integrity using atomicity & rollback.
2.Deadlocks can occur when two sessions wait for each other’s locks – DBMS resolves them automatically.
3.Log-based recovery makes sure that even after crashes or rollbacks, the database can restore consistency.