💾 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.