Lecture 5: Transactions, Security & Internals

Under the Hood: How Databases Stay Reliable & Secure

🎓 Concept Overview

Advanced SQL: Using Subqueries and Set Operations for complex logic.
ACID: The promise that your data is safe (Atomicity, Consistency, Isolation, Durability).
Security (DCL): Controlling who can see what (GRANT/REVOKE).
Internals: Understanding how MySQL actually executes your queries.

5.1 Advanced SQL Patterns

5.1.1 Subqueries (Nested Queries)

A query inside another query. The inner query runs first.

-- Find users who have placed an order (Using IN)
SELECT username FROM users 
WHERE id IN (SELECT user_id FROM orders);

-- Find users who have placed an order (Using EXISTS - Faster for large lists)
SELECT username FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

5.1.2 Set Operations (UNION)

Combines results from two separate queries into one list.

Command	Behavior
`UNION`	Combines results AND removes duplicates (Slower).
`UNION ALL`	Combines results, keeping duplicates (Faster).

5.1.3 The ANY, ALL Operators

Advanced comparison operators used with subqueries.

-- Find products cheaper than ANY product in the 'Budget' category
SELECT * FROM products 
WHERE price < ANY (SELECT price FROM products WHERE category = 'Budget');
-- Equivalent to: price < MAX(budget_prices)

-- Find products cheaper than ALL products in 'Premium'
WHERE price < ALL (SELECT price FROM ... 'Premium');
-- Equivalent to: price < MIN(premium_prices)

5.1.4 Aliases (AS)

Renaming tables or columns for readability.

SELECT p.product_name, c.category_name 
FROM products AS p
JOIN categories AS c ON p.cat_id = c.id;

5.2 phase 8: Transactions (TCL)

A transaction is a group of SQL commands that succeed or fail as a team.

🧠 Mental Model: The Magic Envelope

Imagine sending cash in a magic envelope.

START TRANSACTION: You put the cash in the envelope.
COMMIT: The envelope arrives and opens. The transfer is real.
ROLLBACK: The envelope vanishes mid-air and reappears in your pocket. It's as if nothing happened.

5.2.1 Banking Example

START TRANSACTION;

-- 1. Take $100 from Alice
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 2. Give $100 to Bob
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 3. Save Changes
COMMIT;

5.2.2 Savepoints

Checkpoints within a transaction. Allow partial rollbacks.

START TRANSACTION;
UPDATE ...;
SAVEPOINT checkpoint1;
UPDATE ...; 
-- Something went wrong?
ROLLBACK TO checkpoint1; -- Undo only the second update
COMMIT;

5.3 Isolation Levels

What happens if two people edit the same row at the same time? Isolation levels control the "Locking" strategy.

-- How to set it for the current session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
...

The "Dirty Read" Problem: Reading data that hasn't been committed yet.

Isolation Levels (Weakest to Strongest)

Level	Dirty Reads?	Phantom Reads?	Performance
READ UNCOMMITTED	✅ Yes (Danger!)	✅ Yes	Fastest
READ COMMITTED	❌ No	✅ Yes	Standard (Postgres default)
REPEATABLE READ	❌ No	❌ No	Standard (MySQL default)
SERIALIZABLE	❌ No	❌ No	Slowest (Full Locking)

5.4 Security (DCL)

Principle of Least Privilege: Give users only the keys they need. Never run your app as root.

5.4.1 Granting Access

-- Create a read-only user
CREATE USER 'intern'@'localhost' IDENTIFIED BY 'password123';

-- Give permission
GRANT SELECT ON library.* TO 'intern'@'localhost';

5.4.2 Revoking Access

-- Take permission away
REVOKE INSERT ON library.* FROM 'intern'@'localhost';

5.5 Database Internals: Execution

How does MySQL understand your query?

5.5.1 Error Types

Syntax Error: Using SELEKT instead of SELECT. (Caught immediately).
Runtime Error: Inserting a duplicate Primary Key. (Caught during execution).
Logic Error: Query runs, but returns wrong data (e.g., wrong JOIN). (The hardest to catch).
Deadlock (1213): Two transactions waiting for each other. MySQL kills one to save the other.
Connection Timeout (2006): "MySQL server has gone away". Usually packet size too large or timeout reached.

5.6 Teaching Notes

🎓 Instructor Guide

Key Analogies:

ACID = Ordering Pizza:
- Atomicity: You pay, you get pizza. If one fails, the other must cancel. (No free pizza, no lost money).
- Isolation: If your neighbor orders a pizza at the same time, your orders shouldn't get mixed up.
- Durability: Once the shop says "Order Confirmed", even if their power goes out, they still owe you a pizza.
SQL Injection: Imagine a robot that reads a form. If you write "Name: Bob; DROP TABLE users;", and the robot reads it blindly, it effectively executes the delete command. This is why we Sanitize inputs!

5.7 Debugging Workshop

1. The Phantom Money

I ran this code, but when I refreshed, the transfer was gone!

START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 500);
UPDATE inventory SET stock = stock - 1 WHERE id = 99;
-- Connection closed here...

COMMIT; -- Essential!

Explanation: Without COMMIT, the database assumes you disconnected mid-work and rolls back to safety.

2. The Undo Button

I wanted to save my work, but I typed the wrong command.

START TRANSACTION;
UPDATE users SET points = 100 WHERE id = 1;
ROLLBACK; -- Oops?

3. Access Denied

Error: UPDATE command denied to user 'guest'.

-- Login as 'guest'
UPDATE books SET price = 0;

5.8 Practice Tasks

Task 1: The Bank Heist

Scenario: You are testing a banking app. You transfer $1,000,000 from Bank A to Bank B. Halfway through (after taking money from A), the power goes out.

Activity: Write the command that the Database automatically runs when it restarts.

Show Answer

ROLLBACK;

Because the transaction never saw a COMMIT, the database reverts it during recovery to ensure consistency.

Task 2: The Hacker

Scenario: A user enters this into a login box: admin' --.

Question: What did they just do?