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

#computerscience #database #sql

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