Database System Internals — Transactions, Deadlocks, Recovery & Indexing Explained with SQL Examples

Database System Internals — Transactions, Deadlocks, Recovery & Indexing Explained with SQL Examples

Step 1: Create Table and Insert Sample Data

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);

📸 Screenshot: Accounts table with 3 rows.

✅ Current State:

acc_no name balance

1 Alice 1000

2 Bob 1500

3 Charlie 2000

⚡ Task 1️⃣: Transaction – Atomicity & Rollback

Goal: Transfer ₹500 from Alice → Bob, then rollback before committing.

🧩 Step 1: Start Transaction

BEGIN TRANSACTION;

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

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

📸 Screenshot: Transaction started and updates pending.

🧩 Step 2: Rollback

ROLLBACK;

Now verify balances:

SELECT * FROM Accounts;

✅ Expected Output (No Changes):

acc_no name balance

1 Alice 1000

2 Bob 1500

3 Charlie 2000

📘 Explanation:

Atomicity ensures either all operations happen or none do.

Rollback prevented a partial transfer.

📸 Screenshot: Balances unchanged after rollback.

⚔️ Task 2️⃣: Deadlock Simulation

Goal: Create a deadlock scenario using two sessions.

🪄 Session 1:

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';

-- Lock held on Alice

UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob'; -- Will wait

🪄 Session 2:

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 200 WHERE name = 'Bob';

-- Lock held on Bob

UPDATE Accounts SET balance = balance + 200 WHERE name = 'Alice'; -- Causes Deadlock

✅ Observation:

Both sessions hold different locks and wait for each other.

Database detects deadlock → rolls back one transaction.

📸 Screenshot: SQL deadlock message (one transaction aborted).

📘 Explanation:

Deadlocks happen when two transactions hold resources the other needs.

The DBMS automatically detects and resolves it by aborting one.

🪵 Task 3️⃣: Log-Based Recovery

Most modern databases (MySQL, PostgreSQL, SQL Server) maintain transaction logs:

Binary Log / WAL (Write-Ahead Log) records every change before committing.

🧩 Step 1: Start and Rollback a Transaction

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance + 300 WHERE name = 'Charlie';

ROLLBACK;

🧩 Step 2: Check Logs

If using MySQL:

SHOW BINARY LOGS;

SHOW BINLOG EVENTS;

If using PostgreSQL:

SELECT * FROM pg_stat_activity;

✅ You’ll see entries for the UPDATE followed by a ROLLBACK (UNDO) event.

📸 Screenshot: Log entries showing rollback.

📘 Explanation:

The transaction log helps recover or rollback uncommitted operations after crashes — ensuring data consistency.

📚 Part 2 — Indexing, Hashing & Query Optimization

Now let’s explore how indexes make your queries lightning fast ⚡

🧱 Step 1: Create Table and Insert Data

CREATE TABLE Students (

roll_no INT PRIMARY KEY,

name VARCHAR(50),

dept VARCHAR(10),

cgpa DECIMAL(3,2)

);

🧩 Step 2: Insert 20 Sample Records

INSERT INTO Students VALUES

(101, 'Arun', 'CSE', 8.7),

(102, 'Meena', 'ECE', 7.9),

(103, 'Vishal', 'CSBS', 8.3),

(104, 'Priya', 'IT', 9.0),

(105, 'Ravi', 'CSBS', 8.5),

(106, 'Swetha', 'MECH', 7.8),

(107, 'Deepak', 'EEE', 6.9),

(108, 'Ananya', 'CSE', 9.1),

(109, 'Balaji', 'IT', 8.4),

(110, 'Hari', 'CSBS', 8.9),

(111, 'Nisha', 'ECE', 7.4),

(112, 'Sanjay', 'MECH', 8.0),

(113, 'Keerthi', 'CSE', 9.3),

(114, 'Suresh', 'CSBS', 8.0),

(115, 'Gayathri', 'EEE', 8.4),

(116, 'Kavin', 'IT', 7.6),

(117, 'Lavanya', 'CSE', 8.8),

(118, 'Karthik', 'CSBS', 9.2),

(119, 'Rohit', 'MECH', 8.1),

(120, 'Preethi', 'ECE', 8.0);

📸 Screenshot: 20 records inserted.

🌲 Step 3: Create a B-Tree Index on roll_no

CREATE INDEX idx_rollno_btree ON Students (roll_no);

Now query:

SELECT * FROM Students WHERE roll_no = 110;

📘 Explanation:

B-Tree index improves performance of equality and range lookups on numeric fields.

📸 Screenshot: Query output for roll_no = 110.

🌳 Step 4: Create a B+ Tree Index on cgpa

(In most SQL databases, normal indexes are internally implemented as B+ Trees)

CREATE INDEX idx_cgpa_bplustree ON Students (cgpa);

Query:

SELECT * FROM Students WHERE cgpa > 8.0;

✅ Retrieves high-scoring students efficiently.

📸 Screenshot: Output for cgpa > 8.0.

⚡ Step 5: Create a Hash Index on dept

CREATE INDEX idx_dept_hash ON Students USING HASH (dept);

Now run:

SELECT * FROM Students WHERE dept = 'CSBS';

✅ Fast equality search on department names.