Chapter 9: Advanced Concepts - Learn SQL Online

💼 What Are Transactions?

A transaction is a sequence of one or more SQL operations that are treated as a single unit of work. Either ALL operations succeed together, or ALL operations fail together - there's no in-between!

🎯 Real-Life Analogy: Bank Transfer

Imagine transferring $100 from your savings account to your checking account:

Step 1: Subtract $100 from savings
Step 2: Add $100 to checking

What if the computer crashes after Step 1 but before Step 2?

❌ Without a transaction: Your $100 disappears! (Savings reduced, but checking not increased)
✅ With a transaction: If anything fails, BOTH steps are undone. Your money is safe!

Transactions ensure all-or-nothing execution!

Transaction Lifecycle:

BEGIN TRANSACTION;
    -- SQL operations here
    -- (INSERT, UPDATE, DELETE, etc.)
COMMIT;  -- Save all changes permanently

-- OR

BEGIN TRANSACTION;
    -- SQL operations here
ROLLBACK;  -- Undo all changes

Simple Transaction Example:

BEGIN TRANSACTION;

-- Deduct $100 from Alice's account
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';

-- Add $100 to Bob's account
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

COMMIT;  -- Both updates are saved together!

If anything goes wrong (power outage, error, etc.), the database automatically rolls back BOTH updates. No partial changes!

🔍 Key Transaction Concepts:

BEGIN TRANSACTION: Starts a transaction (also: BEGIN or START TRANSACTION)
COMMIT: Saves all changes permanently
ROLLBACK: Undoes all changes, restores to the state before BEGIN
Atomic: All-or-nothing - either everything happens or nothing happens

🤔 Why We Need Transactions

Transactions solve real-world problems that happen in multi-step operations!

1. Data Integrity

Prevent partial updates that leave data in an inconsistent state

Example: Money transfer - both debit and credit must happen

2. Error Recovery

If an error occurs, undo all changes automatically

Example: Database crash mid-operation

3. Concurrent Access

Handle multiple users updating the same data simultaneously

Example: Two people booking the last seat on a flight

4. Business Logic

Enforce complex business rules across multiple operations

Example: Order processing with inventory updates

🎯 Real-World Scenario: E-Commerce Order

When a customer places an order, multiple things must happen:

BEGIN TRANSACTION;

-- 1. Create the order record
INSERT INTO orders (customer_id, total) VALUES (123, 299.99);

-- 2. Add order items
INSERT INTO order_items (order_id, product_id, quantity) VALUES (5001, 42, 2);

-- 3. Reduce inventory
UPDATE products SET stock = stock - 2 WHERE product_id = 42;

-- 4. Charge the customer
UPDATE customer_balances SET balance = balance - 299.99 WHERE customer_id = 123;

COMMIT;  -- All steps complete successfully!

Without a transaction: What if the inventory update fails but the order was already created? Customer is charged but gets nothing!

With a transaction: If ANY step fails, EVERYTHING is rolled back. The customer isn't charged, inventory isn't changed, and the order doesn't exist. Database stays consistent!

Problems Transactions Solve:

❌ Lost Updates: Two users updating the same row at the same time
❌ Partial Failures: Some operations succeed, others fail
❌ Dirty Reads: Reading uncommitted data from another transaction
❌ Inconsistent State: Data violates business rules mid-operation

✅ COMMIT and ROLLBACK

Learn to control when changes are saved or discarded!

🎯 Real-Life Analogy: Editing a Document

BEGIN TRANSACTION: Open a Word document and start editing
Making changes: Type, delete, format - changes are in memory
COMMIT: Click "Save" - changes written to disk permanently
ROLLBACK: Click "Close without saving" - all edits discarded

COMMIT - Making Changes Permanent:

Try It: COMMIT Example

Output will appear here...

🔍 What Happened:

BEGIN TRANSACTION: Started tracking changes
UPDATEs: Made changes in a temporary state
COMMIT: Wrote all changes to disk permanently
Alice's balance: $1000 → $800
Bob's balance: $500 → $700
Total money in system: $1500 (unchanged, as it should be!)

ROLLBACK - Undoing Changes:

Try It: ROLLBACK Example

Output will appear here...

Note: In SQLite.js (browser environment), autocommit is on by default. In production databases, transactions give you more control!

🔍 When to Use ROLLBACK:

Error Detection: IF you detect a business rule violation, ROLLBACK
User Cancellation: User clicks "Cancel" mid-operation
Validation Failure: Data doesn't pass validation checks
Dry Run/Testing: Test queries without actually changing data

Real-World Example: ROLLBACK on Error

BEGIN TRANSACTION;

-- Try to withdraw money
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Check if balance went negative (business rule violation!)
-- In real code, you'd check this programmatically

-- If balance < 0:
ROLLBACK;  -- Undo the withdrawal!

-- If balance >= 0:
COMMIT;  -- Allow the withdrawal

🧪 Introduction to ACID

ACID is a set of four properties that guarantee reliable database transactions!

🎯 What is ACID?

ACID is an acronym for four critical properties that every reliable database transaction must have:

A - Atomicity
C - Consistency
I - Isolation
D - Durability

These properties work together to ensure your data stays safe, accurate, and reliable - even in the face of errors, crashes, or concurrent users!

Why ACID Matters:

Imagine a database for a hospital, bank, or airline reservation system. A single error could have serious consequences:

💊 Hospital: Wrong medication dosage recorded
💰 Bank: Money disappearing from accounts
✈️ Airline: Double-booking the same seat

ACID properties prevent these disasters!

A - Atomicity

All or nothing

Either the entire transaction succeeds, or none of it does

C - Consistency

Rules are never broken

Database always moves from one valid state to another

I - Isolation

Transactions don't interfere

Concurrent transactions happen as if they ran one-at-a-time

D - Durability

Changes are permanent

Once committed, data survives crashes and power failures

Let's explore each property in detail!

⚛️ A - Atomicity

Atomicity means transactions are "all-or-nothing" - they either complete fully or have no effect at all.

🎯 Real-Life Analogy: Buying a Coffee

When you buy coffee:

You pay $5
You receive the coffee

What if the power goes out after you pay but before you get your coffee?

❌ Non-atomic: You lose $5 and get nothing
✅ Atomic: The entire transaction is canceled - you keep your $5, store keeps the coffee

Atomicity ensures you can't have half a transaction!

Example: Atomicity in Action

BEGIN TRANSACTION;

-- Step 1: Deduct from inventory
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;

-- Step 2: Create order
INSERT INTO orders (customer_id, product_id) VALUES (101, 42);

-- Step 3: Charge customer
UPDATE customers SET balance = balance - 99.99 WHERE customer_id = 101;

COMMIT;

With Atomicity:

✅ All 3 steps succeed: COMMIT saves everything
✅ Any step fails: ROLLBACK undoes everything automatically
✅ Database crashes: On restart, transaction is completely undone

Without Atomicity:

❌ Inventory reduced but order not created
❌ Customer charged but no order record
❌ Inconsistent, broken database state

Key Points About Atomicity:

Think of each transaction as a single, indivisible unit
The word "atomic" comes from Greek atomos = "uncuttable"
Partial updates are never visible to other users or after crashes
Database automatically handles ROLLBACK if anything goes wrong

✔️ C - Consistency

Consistency means the database always moves from one valid state to another valid state, never violating any rules or constraints.

🎯 Real-Life Analogy: Chess Game Rules

In chess, certain rules must always be followed:

Each player has exactly one king
Pawns can't move backward
You can't move into check

Consistency means the database enforces rules like constraints, triggers, and business logic - just like a chess app won't let you make illegal moves!

Example: Consistency with Constraints

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    balance REAL CHECK(balance >= 0)  -- Constraint: balance can't be negative
);

BEGIN TRANSACTION;

-- Valid transaction (respects constraint)
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
-- If balance was $100, it's now $50 ✅ Still valid (>= 0)

COMMIT;  -- Success!

BEGIN TRANSACTION;

-- Invalid transaction (violates constraint)
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
-- If balance was $50, this would make it -$150 ❌ Violates constraint!

-- Database automatically ROLLBACK this transaction!
-- Consistency preserved!

What Consistency Enforces:

Constraints: CHECK, NOT NULL, UNIQUE, FOREIGN KEY
Data types: Can't insert text into an INTEGER column
Triggers: Automatic actions that enforce business rules
Referential integrity: Foreign keys must reference existing rows

🔍 Real-World Example: Money Transfer

Business Rule: Total money in the system must stay constant

Before transaction:

Alice: $1000
Bob: $500
Total: $1500

After transaction:

Alice: $800 (sent $200)
Bob: $700 (received $200)
Total: $1500 ✅ (same as before - consistent!)

If only one UPDATE happened, the total would be $1300 or $1700 - violating consistency!

Valid State

Before Transaction

All constraints satisfied ✅

⬇️ Transaction Executes

May temporarily violate rules internally

(but never visible externally!)

Valid State

After Transaction

All constraints satisfied ✅

🔒 I - Isolation

Isolation means concurrent transactions don't interfere with each other - they execute as if they're the only transaction running.

🎯 Real-Life Analogy: ATM Withdrawals

Imagine you and your spouse both try to withdraw money from the same account at the same time:

Account balance: $500
You withdraw: $400 at ATM #1
Spouse withdraws: $300 at ATM #2 (at the same moment!)

Without isolation:

Both ATMs read balance: $500
ATM #1 deducts $400, writes $100
ATM #2 deducts $300, writes $200
Final balance: $200 (but you withdrew $700 total - $200 disappeared!)

With isolation:

ATM #1 locks the account, reads $500
ATM #1 deducts $400, writes $100, unlocks
ATM #2 locks the account, reads $100
ATM #2 rejects withdrawal (insufficient funds)
Final balance: $100 ✅ Correct!

Isolation Prevents:

Dirty Reads: Reading uncommitted changes from another transaction
Non-Repeatable Reads: Reading the same data twice in a transaction but getting different values
Phantom Reads: New rows appearing between two reads in the same transaction
Lost Updates: Two transactions updating the same row, one overwriting the other

Example: Isolation in Action

-- Transaction 1 (running concurrently)
BEGIN TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 42;
-- Transaction is active but NOT yet committed...

-- Transaction 2 (running at the same time)
BEGIN TRANSACTION;
SELECT stock FROM products WHERE product_id = 42;
-- What value does it see?

-- With ISOLATION: Sees the OLD value (before Transaction 1's update)
-- Transaction 2 doesn't see uncommitted changes!

-- Transaction 1 completes
COMMIT;  -- Now the change is visible to others

-- Transaction 2 continues
SELECT stock FROM products WHERE product_id = 42;
-- Still sees the OLD value (reads are consistent within a transaction)

COMMIT;

🔍 How Isolation Works (Simplified):

Databases use locks or versioning to provide isolation:

1. Locking:

When you read/write a row, database puts a "lock" on it
Other transactions must wait for the lock to be released
Prevents two transactions from modifying the same data simultaneously

2. Multi-Version Concurrency Control (MVCC):

Each transaction sees a "snapshot" of the database from when it started
Reads don't block writes, writes don't block reads
More efficient but more complex to implement

Isolation Levels (Advanced):

Different levels of isolation offer different trade-offs between safety and performance:

READ UNCOMMITTED: Weakest, fastest (allows dirty reads)
READ COMMITTED: Most common default
REPEATABLE READ: Stronger isolation
SERIALIZABLE: Strongest, slowest (transactions run as if sequential)

SQLite uses SERIALIZABLE by default!

💾 D - Durability

Durability means once a transaction is committed, the changes are permanent - even if the system crashes immediately after!

🎯 Real-Life Analogy: Saving a Document

When you click "Save" in a word processor:

Before save: Your work is in RAM (volatile memory)
After save: Your work is on the hard drive (permanent storage)
Power outage 1 second after save: Your work is still safe! ✅

Durability means COMMIT works like that "Save" button - once it's done, your data is safe from crashes!

Example: Durability Guarantees

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance + 1000 WHERE account_id = 123;

COMMIT;  -- ✅ Data is written to disk!

-- Even if these disasters happen 1 millisecond after COMMIT:
-- ❌ Power failure
-- ❌ System crash
-- ❌ Database process killed

-- When the database restarts:
-- ✅ The $1000 credit is still there!
-- ✅ Transaction is NOT lost!

🔍 How Durability Works:

Databases use several techniques to ensure durability:

1. Write-Ahead Logging (WAL):

Before making changes, database writes them to a log file on disk
Log is written sequentially (fast!)
If crash happens, database replays the log on restart
Used by SQLite, PostgreSQL, MySQL

2. Force Writes to Disk:

COMMIT doesn't return until data is physically written to disk
Uses fsync() system call to force OS to flush buffers
Ensures data survives power failures

3. Redundancy:

Write to multiple disks (RAID)
Replicate to backup servers
Even if one disk fails, data is safe

What Durability Protects Against:

💥 Power failures
💥 System crashes
💥 Hardware failures
💥 Operating system crashes
💥 Database process crashes

Once COMMIT returns, your data is SAFE!

Before COMMIT

Changes in memory

❌ Not durable (lost on crash)

During COMMIT

Writing to disk

⏳ Becoming durable...

After COMMIT

Safely on disk

✅ Durable (survives crashes!)

Important Note:

Durability is why databases are sometimes slower than just storing data in memory. The trade-off is worth it - you get guaranteed safety even in disasters!

💼 Practical Use Cases

Let's see how transactions are used in real-world applications!

1. Banking

Money transfers, deposits, withdrawals

Critical: Can't lose money!

2. E-Commerce

Order processing, inventory updates

Critical: Don't charge without shipping!

3. Social Media

Post creation, like counts, follower updates

Important: Keep counts accurate

4. Booking Systems

Hotel, flight, concert ticket reservations

Critical: No double-booking!

5. Healthcare

Patient records, medication logs

Critical: Accuracy is life-or-death

6. Gaming

Inventory, points, achievements

Important: Fair gameplay

Real-World Example: Hotel Booking

BEGIN TRANSACTION;

-- Check if room is available
SELECT available FROM rooms WHERE room_id = 101;
-- Returns: available = 1 (YES)

-- Reserve the room
UPDATE rooms SET available = 0 WHERE room_id = 101;

-- Create booking record
INSERT INTO bookings (customer_id, room_id, check_in, check_out)
VALUES (456, 101, '2024-03-15', '2024-03-20');

-- Charge the customer
UPDATE customers SET balance = balance - 500 WHERE customer_id = 456;

COMMIT;  -- All or nothing!

Why transaction is essential:

✅ If credit card declines, room stays available (ROLLBACK)
✅ If system crashes, no partial booking (ATOMICITY)
✅ Two people can't book the same room (ISOLATION)
✅ Once booked, reservation survives crashes (DURABILITY)

🔍 Transaction Best Practices:

Keep transactions short: Long transactions lock resources
Use transactions for multi-step operations: Anything that must happen together
Don't mix user input with transactions: Don't wait for user during a transaction
Handle errors: Always have error handling that calls ROLLBACK
Test failure scenarios: Make sure ROLLBACK works correctly

⚠️ Common Transaction Mistakes

Learn from these common pitfalls that can cause data corruption and performance problems!

🎯 Real-Life Analogy: Common Mistakes are Like Traffic Accidents

Just like driving mistakes cause accidents, transaction mistakes cause data problems:

Forgetting to commit: Like parking but leaving the engine running
Long transactions: Like blocking traffic at a green light
Not handling errors: Like driving without insurance

Mistake #1: Forgetting to COMMIT or ROLLBACK

❌ Bad Practice:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- ❌ Forgot to COMMIT or ROLLBACK!
-- Transaction stays open forever, locking the rows!

✅ Good Practice:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- ✅ Always end your transaction!

Why this is bad: An uncommitted transaction locks rows and prevents other users from accessing them. In production databases, this can cause major performance problems!

Mistake #2: Not Handling Transaction Errors

❌ Bad Practice (Pseudo-code):

BEGIN TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE id = 42;
INSERT INTO orders (product_id, quantity) VALUES (42, 1);

COMMIT;

-- ❌ What if the INSERT fails?
-- The stock is reduced but there's no order!

✅ Good Practice (Pseudo-code):

BEGIN TRANSACTION;

try:
    UPDATE products SET stock = stock - 1 WHERE id = 42;
    INSERT INTO orders (product_id, quantity) VALUES (42, 1);
    COMMIT;  -- Only if everything succeeds
except:
    ROLLBACK;  -- ✅ Undo everything on error!
    raise error;

Why this is important: Always wrap transactions in error handling. If anything fails, ROLLBACK ensures the database stays consistent!

Mistake #3: Long-Running Transactions

❌ Bad Practice:

BEGIN TRANSACTION;

-- Process 10,000 orders one by one
FOR each order in orders:
    UPDATE inventory SET stock = stock - order.quantity;
    INSERT INTO shipments VALUES (...);
    -- This takes 30 minutes!

COMMIT;  -- ❌ Locks held for 30 minutes!

✅ Good Practice:

-- Process in smaller batches
FOR each batch of 100 orders:
    BEGIN TRANSACTION;

    -- Process 100 orders
    FOR each order in batch:
        UPDATE inventory SET stock = stock - order.quantity;
        INSERT INTO shipments VALUES (...);

    COMMIT;  -- ✅ Release locks every 100 orders!

Why this is bad: Long transactions:

Lock resources for extended periods
Block other users from accessing data
Increase the chance of deadlocks
Use more memory and log space

Best Practice: Keep transactions as short as possible!

Mistake #4: Mixing User Input with Transactions

❌ Bad Practice (Pseudo-code):

BEGIN TRANSACTION;

UPDATE seats SET reserved = 1 WHERE seat_id = 42;

-- ❌ Wait for user to confirm booking
user_input = wait_for_user_confirmation();  // Could take minutes!

if user_input == "confirm":
    COMMIT;
else:
    ROLLBACK;

✅ Good Practice:

-- First, get user confirmation
user_input = get_user_confirmation();

-- Then, do the transaction quickly
if user_input == "confirm":
    BEGIN TRANSACTION;
    UPDATE seats SET reserved = 1 WHERE seat_id = 42;
    COMMIT;  -- ✅ Transaction completes in milliseconds!

Why this is bad: Never wait for user input during a transaction! Users might:

Go to lunch (locking rows for hours!)
Close their browser
Get distracted

Best Practice: Gather all needed information BEFORE starting the transaction, then execute it quickly!

Mistake #5: Not Understanding Isolation Levels

❌ Bad Practice:

-- Using READ UNCOMMITTED isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRANSACTION;

-- Reading data from another uncommitted transaction
SELECT balance FROM accounts WHERE id = 1;
-- ❌ Might read "dirty" data that gets rolled back!

COMMIT;

✅ Good Practice:

-- Use appropriate isolation level (default is usually READ COMMITTED)
-- SQLite uses SERIALIZABLE by default (safest)

BEGIN TRANSACTION;

-- Only reads committed data
SELECT balance FROM accounts WHERE id = 1;
-- ✅ Guaranteed to see only committed data!

COMMIT;

Why this matters:

READ UNCOMMITTED: Fastest but can read data that gets rolled back!
READ COMMITTED: Good balance for most applications
SERIALIZABLE: Safest but slowest

Best Practice: Use the strongest isolation level you can afford performance-wise. When in doubt, stick with the default!

🔧 Transaction Troubleshooting Checklist:

✅ Always COMMIT or ROLLBACK: Never leave transactions hanging
✅ Wrap in error handling: Always have a ROLLBACK plan
✅ Keep transactions short: Measure in milliseconds, not seconds
✅ No user interaction: Get input before BEGIN TRANSACTION
✅ Use appropriate isolation: Understand the trade-offs
✅ Test failure scenarios: Make sure ROLLBACK works
✅ Monitor locks: Watch for blocking and deadlocks

🔒 Isolation Levels Explained

Isolation levels control how transactions interact with each other. Different levels provide different trade-offs between data consistency and performance!

🎯 Real-Life Analogy: Privacy Levels in a Library

Think of isolation levels like different study room options:

READ UNCOMMITTED: Open area - you see everyone's messy drafts
READ COMMITTED: Semi-private room - you only see finished work
REPEATABLE READ: Private room with locked door
SERIALIZABLE: Soundproof vault - complete isolation

Level 1: READ UNCOMMITTED (Weakest)

What it allows: Read data that hasn't been committed yet (dirty reads)

Example of the Problem:

-- Transaction 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = 1000000 WHERE id = 1;
-- Not yet committed!

-- Transaction 2 (with READ UNCOMMITTED)
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- Returns: 1000000 ❌ But this might get rolled back!

-- Transaction 1
ROLLBACK;  -- Oops! Transaction 2 read fake data!

Use Case: Read-only reports where absolute accuracy isn't critical

Risk: Can read data that never actually existed!

Level 2: READ COMMITTED (Most Common Default)

What it guarantees: You only see committed data

What it allows: Data can change between reads in the same transaction (non-repeatable reads)

Example:

-- Transaction 1
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- Returns: 500

-- Transaction 2 commits a change
BEGIN TRANSACTION;
UPDATE accounts SET balance = 1000 WHERE id = 1;
COMMIT;

-- Transaction 1 reads again
SELECT balance FROM accounts WHERE id = 1;
-- Returns: 1000 ⚠️ Different value! (non-repeatable read)

Use Case: Most web applications, general OLTP systems

Good for: High concurrency with acceptable consistency

Level 3: REPEATABLE READ

What it guarantees: If you read a row twice in a transaction, you get the same value

What it allows: New rows can appear (phantom reads)

Example:

-- Transaction 1
BEGIN TRANSACTION;
SELECT COUNT(*) FROM orders WHERE customer_id = 123;
-- Returns: 5 orders

-- Transaction 2 adds a new order
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (123, 99.99);
COMMIT;

-- Transaction 1 counts again
SELECT COUNT(*) FROM orders WHERE customer_id = 123;
-- Returns: 6 ⚠️ A "phantom" row appeared!

Use Case: Financial calculations, reports needing consistent snapshots

Good for: Operations that need stable reads

Level 4: SERIALIZABLE (Strongest)

What it guarantees: Transactions execute as if they ran one-at-a-time (serially)

What it prevents: Dirty reads, non-repeatable reads, phantom reads - EVERYTHING!

Example:

-- Transaction 1 (SERIALIZABLE)
BEGIN TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000;
-- Locks these rows and prevents new rows from appearing

-- Transaction 2 tries to insert
BEGIN TRANSACTION;
INSERT INTO accounts VALUES (999, 'New', 5000);
-- ⏸️ BLOCKED! Must wait for Transaction 1 to finish

-- Transaction 1
COMMIT;  -- Now Transaction 2 can proceed

-- Transaction 2
COMMIT;

Use Case: Critical financial transactions, inventory systems

Trade-off: Slowest, most locking, but safest!

Note: SQLite uses SERIALIZABLE by default!

READ UNCOMMITTED

Speed: ⚡⚡⚡ Fastest

Safety: ⚠️ Lowest

Allows: Dirty reads

READ COMMITTED

Speed: ⚡⚡ Fast

Safety: ⚠️⚠️ Medium

Allows: Non-repeatable reads

REPEATABLE READ

Speed: ⚡ Slower

Safety: ⚠️⚠️⚠️ High

Allows: Phantom reads

SERIALIZABLE

Speed: 🐌 Slowest

Safety: ⚠️⚠️⚠️⚠️ Highest

Prevents: Everything!

Choosing the Right Isolation Level:

Scenario	Recommended Level
Money transfers	SERIALIZABLE
E-commerce checkout	REPEATABLE READ or SERIALIZABLE
Social media feed	READ COMMITTED
Analytics dashboard	READ UNCOMMITTED (if approximate is OK)

💀 Understanding Deadlocks

A deadlock occurs when two or more transactions are waiting for each other to release locks, causing all of them to be stuck forever!

🎯 Real-Life Analogy: Four-Way Stop Gridlock

Imagine a four-way intersection where:

Car A is waiting for Car B to move
Car B is waiting for Car C to move
Car C is waiting for Car D to move
Car D is waiting for Car A to move

Result: Nobody can move! This is a deadlock. Someone has to back up (rollback) to break the cycle.

How Deadlocks Happen:

Classic Deadlock Example:

-- Transaction 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks row 1
-- (waiting a moment...)

-- Transaction 2 (running at the same time)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- Locks row 2
-- (waiting a moment...)

-- Transaction 1 continues
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- ⏸️ BLOCKED! Waiting for row 2

-- Transaction 2 continues
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- ⏸️ BLOCKED! Waiting for row 1

-- 💀 DEADLOCK! Both transactions are waiting for each other!

What happens:

Transaction 1 locks row 1
Transaction 2 locks row 2
Transaction 1 tries to lock row 2 (blocked by Transaction 2)
Transaction 2 tries to lock row 1 (blocked by Transaction 1)
DEADLOCK! Neither can proceed

How Databases Detect and Resolve Deadlocks:

Detection: Database periodically checks for circular waits
Victim Selection: Database picks one transaction to be the "victim"
Rollback: The victim transaction is automatically rolled back
Error: Application receives a deadlock error
Retry: Application should retry the failed transaction

Preventing Deadlocks:

1. Lock Order

Always acquire locks in the same order

Example: Always lock accounts in ascending ID order

2. Keep Transactions Short

Less time holding locks = less chance of deadlock

Example: Commit frequently

3. Lock All at Once

Acquire all needed locks at the start

Example: SELECT FOR UPDATE at beginning

4. Use Lower Isolation

Lower isolation = fewer locks

Trade-off: Less consistency

✅ Good Practice: Consistent Lock Ordering

-- Always lock accounts in ascending ID order
-- This prevents deadlocks!

-- Transaction 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Lock smaller ID first
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Then larger ID
COMMIT;

-- Transaction 2 (running concurrently)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;   -- Lock smaller ID first
UPDATE accounts SET balance = balance + 50 WHERE id = 2;   -- Then larger ID
COMMIT;

-- ✅ No deadlock! Both transactions lock in the same order
-- One waits for the other to complete

🔍 Handling Deadlocks in Application Code:

When a deadlock occurs, your application should retry the transaction!

Retry Pattern (Pseudo-code):

max_retries = 3
for attempt in range(max_retries):
    try:
        BEGIN TRANSACTION;
        -- Your SQL operations here
        COMMIT;
        break;  // Success!
    except DeadlockError:
        ROLLBACK;
        if attempt < max_retries - 1:
            sleep(random(100ms, 500ms));  // Wait a bit
            retry;  // Try again
        else:
            raise;  // Give up after 3 attempts

Why this works: After one transaction is rolled back, the other can complete. Then the rolled-back transaction can retry and succeed!

🔧 Deadlock Troubleshooting Tips:

✅ Log deadlock errors: Track when and where they occur
✅ Identify the pattern: Which tables/rows are involved?
✅ Fix the lock order: Make all transactions lock in the same sequence
✅ Implement retry logic: Always retry deadlocked transactions
✅ Monitor frequency: Frequent deadlocks indicate design problems
✅ Reduce transaction time: Faster transactions = fewer deadlocks

Practice Exercises

Part 1: Guided Exercises

Guided Exercise 1: Safe Money Transfer

Task: Create a transaction that safely transfers $50 from Account 1 to Account 2. Both updates must succeed or both must fail.

Output will appear here...

Guided Exercise 2: ROLLBACK on Insufficient Funds

Task: Try to transfer $300 from Alice's account (which only has $200). The transaction should detect the error and ROLLBACK.

Output will appear here...

Guided Exercise 3: Multi-Table Transaction

Task: Process a customer order that involves three steps: reduce inventory, create an order record, and update customer balance. All must succeed together!

-- Setup tables
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT,
    stock INTEGER,
    price REAL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    quantity INTEGER
);

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

-- Insert test data
INSERT INTO products VALUES (1, 'Laptop', 10, 999.99);
INSERT INTO customers VALUES (101, 'Alice', 5000.00);

-- Show before
SELECT 'Before Transaction' as status;
SELECT * FROM products;
SELECT * FROM customers;

-- Process order with transaction
BEGIN TRANSACTION;

-- Step 1: Reduce inventory
UPDATE products SET stock = stock - 1 WHERE product_id = 1;

-- Step 2: Create order
INSERT INTO orders (customer_id, product_id, quantity) VALUES (101, 1, 1);

-- Step 3: Charge customer
UPDATE customers SET balance = balance - 999.99 WHERE customer_id = 101;

COMMIT;

-- Show after
SELECT 'After Transaction' as status;
SELECT * FROM products;
SELECT * FROM orders;
SELECT * FROM customers;

Output will appear here...

Guided Exercise 4: Understanding ROLLBACK

Task: Make changes within a transaction, then intentionally ROLLBACK to undo everything.

Output will appear here...

Part 2: Independent Practice

Challenge 1: E-Commerce Order Processing

Difficulty: Medium

Task: Create a transaction that processes an order - reduce inventory by 2 units for product_id 1, and create an order record. Use BEGIN TRANSACTION and COMMIT.

Output will appear here...

BEGIN TRANSACTION;

UPDATE products SET stock = stock - 2 WHERE product_id = 1;
INSERT INTO orders (product_id, quantity) VALUES (1, 2);

COMMIT;

Why this is atomic: If the INSERT fails, the stock update is automatically rolled back!

Challenge 2: Hotel Room Booking System

Difficulty: Medium-Hard

Task: Create a transaction that books a hotel room. The transaction should: (1) Check if room 101 is available, (2) Mark it as unavailable, (3) Create a booking record for customer 5. All steps must succeed or the room stays available!

Output will appear here...

BEGIN TRANSACTION;

-- Step 1: Mark room as unavailable
UPDATE rooms SET available = 0 WHERE room_id = 101;

-- Step 2: Create booking record
INSERT INTO bookings (customer_id, room_id, check_in, check_out)
VALUES (5, 101, '2024-03-15', '2024-03-20');

COMMIT;

Explanation:

If INSERT fails, UPDATE is rolled back - room stays available
ATOMICITY ensures no partial bookings
Room 101 is now marked as unavailable (0)
Booking record created for customer 5

Challenge 3: Banking Withdrawal with Validation

Difficulty: Hard

Task: Create a transaction that withdraws $500 from account_id 1, BUT only if the balance is sufficient (>= $500). Use a CHECK constraint to enforce this rule. Test with initial balance of $600.

Output will appear here...

BEGIN TRANSACTION;

UPDATE bank_accounts SET balance = balance - 500 WHERE account_id = 1;

COMMIT;

Explanation:

Account 1 starts with $600, ends with $100 ✅
CHECK constraint ensures balance never goes negative
If you tried to withdraw $700, transaction would fail automatically
CONSISTENCY: Database rules are always enforced!

Try This: Change withdrawal to $700 and watch it fail!

Challenge 4: Social Media Post with Like Count

Difficulty: Medium

Task: Create a transaction that simulates user 3 liking post 1. The transaction should: (1) Insert a like record, (2) Increment the likes_count on the post. Both must happen atomically!

Output will appear here...

BEGIN TRANSACTION;

-- Step 1: Insert like record
INSERT INTO post_likes (post_id, user_id) VALUES (1, 3);

-- Step 2: Increment likes count
UPDATE posts SET likes_count = likes_count + 1 WHERE post_id = 1;

COMMIT;

Explanation:

Like record created in post_likes table
Post 1's likes_count incremented from 5 to 6
ATOMICITY: Both operations succeed together or fail together
Prevents scenario where like is recorded but count not updated (or vice versa)

Challenge 5: Multi-Currency Exchange Transaction

Difficulty: Hard

Task: Create a transaction that exchanges $100 USD to EUR at a rate of 0.85. The transaction should: (1) Deduct 100 from usd_balance for user 1, (2) Add 85 to eur_balance for user 1. Total value must be preserved!

Output will appear here...

BEGIN TRANSACTION;

-- Step 1: Deduct USD
UPDATE currency_accounts SET usd_balance = usd_balance - 100 WHERE user_id = 1;

-- Step 2: Add EUR (100 * 0.85 = 85)
UPDATE currency_accounts SET eur_balance = eur_balance + 85 WHERE user_id = 1;

COMMIT;

Explanation:

USD balance: $500 → $400
EUR balance: €200 → €285
CONSISTENCY: Value is preserved (100 USD = 85 EUR at 0.85 rate)
ATOMICITY: Both currency updates happen together
If either UPDATE fails, both are rolled back - no money lost!

Transactions & ACID

💼 What Are Transactions?

🎯 Real-Life Analogy: Bank Transfer

Transaction Lifecycle:

Simple Transaction Example:

🔍 Key Transaction Concepts:

🤔 Why We Need Transactions

1. Data Integrity

2. Error Recovery

3. Concurrent Access

4. Business Logic

🎯 Real-World Scenario: E-Commerce Order

Problems Transactions Solve:

✅ COMMIT and ROLLBACK

🎯 Real-Life Analogy: Editing a Document

COMMIT - Making Changes Permanent:

Try It: COMMIT Example

🔍 What Happened:

ROLLBACK - Undoing Changes:

Try It: ROLLBACK Example

🔍 When to Use ROLLBACK:

Real-World Example: ROLLBACK on Error

🧪 Introduction to ACID

🎯 What is ACID?

Why ACID Matters:

A - Atomicity

C - Consistency

I - Isolation

D - Durability

⚛️ A - Atomicity

🎯 Real-Life Analogy: Buying a Coffee

Example: Atomicity in Action

Key Points About Atomicity:

✔️ C - Consistency

🎯 Real-Life Analogy: Chess Game Rules

Example: Consistency with Constraints

What Consistency Enforces:

🔍 Real-World Example: Money Transfer

Valid State

⬇️ Transaction Executes

Valid State

🔒 I - Isolation

🎯 Real-Life Analogy: ATM Withdrawals

Isolation Prevents:

Example: Isolation in Action

🔍 How Isolation Works (Simplified):

Isolation Levels (Advanced):

💾 D - Durability

🎯 Real-Life Analogy: Saving a Document

Example: Durability Guarantees

🔍 How Durability Works:

What Durability Protects Against:

Before COMMIT

During COMMIT

After COMMIT

Important Note:

💼 Practical Use Cases

1. Banking

2. E-Commerce

3. Social Media

4. Booking Systems

5. Healthcare

6. Gaming

Real-World Example: Hotel Booking

🔍 Transaction Best Practices:

⚠️ Common Transaction Mistakes

🎯 Real-Life Analogy: Common Mistakes are Like Traffic Accidents

Mistake #1: Forgetting to COMMIT or ROLLBACK

❌ Bad Practice:

✅ Good Practice:

Mistake #2: Not Handling Transaction Errors

❌ Bad Practice (Pseudo-code):

✅ Good Practice (Pseudo-code):

Mistake #3: Long-Running Transactions

❌ Bad Practice:

✅ Good Practice:

Mistake #4: Mixing User Input with Transactions

❌ Bad Practice (Pseudo-code):

✅ Good Practice:

Mistake #5: Not Understanding Isolation Levels