DBMS Interview Questions — Lost Update Race Conditions

-- Scenario: Bank transfer between two accounts
-- This demonstrates Atomicity — both updates happen or neither does

-- Setup: create accounts table with a balance constraint
CREATE TABLE bank_accounts (
    account_id   INT PRIMARY KEY,
    holder_name  VARCHAR(100) NOT NULL,
    balance      DECIMAL(10, 2) NOT NULL CHECK (balance >= 0)  -- Consistency constraint
);

INSERT INTO bank_accounts VALUES (1, 'Alice Johnson', 1000.00);
INSERT INTO bank_accounts VALUES (2, 'Bob Martinez',  500.00);

-- Begin a transaction (Atomicity wrapper)
BEGIN TRANSACTION;

    -- Step 1: Deduct from Alice
    UPDATE bank_accounts
    SET    balance = balance - 500.00
    WHERE  account_id = 1;

    -- Step 2: Credit Bob
    UPDATE bank_accounts
    SET    balance = balance + 500.00
    WHERE  account_id = 2;

    -- If EITHER update fails (e.g. Alice's balance would go negative,
    -- violating CHECK constraint), the whole block rolls back.
    -- Neither account changes. That's Atomicity + Consistency working together.

COMMIT;  -- Durability: this is now written to the WAL and survives a crash

-- Verify the final state
SELECT account_id, holder_name, balance FROM bank_accounts;

-- Now demonstrate a ROLLBACK scenario
BEGIN TRANSACTION;

    UPDATE bank_accounts
    SET    balance = balance - 2000.00  -- Alice only has 500 now; CHECK will fail
    WHERE  account_id = 1;

ROLLBACK;  -- We manually roll back, or the DB engine does it on constraint violation

-- Alice's balance is unchanged — Atomicity protected us
SELECT account_id, holder_name, balance FROM bank_accounts;

-- =============================================
-- BAD: Unnormalized orders table (violates 3NF)
-- =============================================
-- Problem: customer_city depends on customer_id, NOT on order_id.
-- If a customer moves, you update 100 rows instead of 1.
CREATE TABLE orders_unnormalized (
    order_id       INT PRIMARY KEY,
    customer_id    INT,
    customer_name  VARCHAR(100),   -- redundant — should live in customers table
    customer_city  VARCHAR(100),   -- transitive dependency: city -> customer, not order
    product_name   VARCHAR(100),
    order_total    DECIMAL(10, 2)
);

-- =============================================
-- GOOD: Normalized to 3NF — two separate tables
-- =============================================
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_city VARCHAR(100) NOT NULL
    -- city lives here — update once, reflects everywhere
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL REFERENCES customers(customer_id),  -- FK enforces integrity
    product_name  VARCHAR(100) NOT NULL,
    order_total   DECIMAL(10, 2) NOT NULL
);

-- Insert sample data
INSERT INTO customers VALUES (1, 'Alice Johnson', 'New York');
INSERT INTO orders   VALUES (101, 1, 'Laptop', 1299.99);
INSERT INTO orders   VALUES (102, 1, 'Mouse',    29.99);

-- Join to reconstruct the full picture — this is the cost of normalization
-- (the join), but the benefit is zero data duplication
SELECT
    o.order_id,
    c.customer_name,
    c.customer_city,
    o.product_name,
    o.order_total
FROM   orders    o
JOIN   customers c ON o.customer_id = c.customer_id;

-- If Alice moves to Boston — ONE update, zero inconsistency
UPDATE customers SET customer_city = 'Boston' WHERE customer_id = 1;

-- Setup: large employees table to demonstrate index impact
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,          -- clustered index by default in most DBs
    department_id INT NOT NULL,
    last_name     VARCHAR(100) NOT NULL,
    first_name    VARCHAR(100) NOT NULL,
    email         VARCHAR(200) UNIQUE,      -- UNIQUE automatically creates an index
    hire_date     DATE NOT NULL,
    salary        DECIMAL(10, 2)
);

-- Without any extra indexes, this query does a full table scan (slow at scale)
-- EXPLAIN shows 'Seq Scan' or 'Full Table Scan' — red flag in production
EXPLAIN SELECT * FROM employees WHERE last_name = 'Johnson';

-- Create a non-clustered index on last_name
CREATE INDEX idx_employees_last_name
    ON employees (last_name);  -- B-Tree built; now searches on last_name are O(log n)

-- After the index: EXPLAIN shows 'Index Scan' — jumps directly to matching rows
EXPLAIN SELECT * FROM employees WHERE last_name = 'Johnson';

-- Composite index: useful when you frequently filter by department AND hire_date
CREATE INDEX idx_employees_dept_hire
    ON employees (department_id, hire_date);

-- This query USES the composite index (left-prefix rule satisfied)
SELECT * FROM employees
WHERE  department_id = 3
AND    hire_date > '2022-01-01';           -- Both columns in index, in order

-- This query CANNOT use the composite index efficiently
-- (skips department_id, starts at second column — violates left-prefix rule)
SELECT * FROM employees
WHERE  hire_date > '2022-01-01';           -- Falls back to full scan

-- Check index usage and fragmentation (PostgreSQL syntax)
SELECT
    indexname,
    idx_scan,       -- how many times this index was used
    idx_tup_read    -- rows read through this index
FROM   pg_stat_user_indexes
WHERE  tablename = 'employees';

-- Setup: customers and orders tables
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),  -- foreign key
    amount      DECIMAL(10, 2) NOT NULL,
    order_year  INT NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice Johnson'), (2, 'Bob Martinez'), (3, 'Carol Wu');
INSERT INTO orders VALUES
    (101, 1, 250.00, 2023),
    (102, 1, 450.00, 2023),
    (103, 2, 125.00, 2022),
    (104, 2, 800.00, 2023);
-- Carol Wu (customer_id=3) has NO orders — useful for LEFT JOIN demo

-- =============================================
-- INNER JOIN: only customers WITH orders
-- =============================================
SELECT c.customer_name, o.order_id, o.amount
FROM   customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Carol Wu is NOT in this result

-- =============================================
-- LEFT JOIN: ALL customers, even those without orders
-- =============================================
SELECT c.customer_name, o.order_id, o.amount
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Carol Wu appears with NULL for order_id and amount

-- =============================================
-- Find customers who have NEVER placed an order
-- =============================================
SELECT c.customer_name
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE  o.order_id IS NULL;  -- NULL means no matching order row was found

-- =============================================
-- WHERE vs HAVING — the classic interview trap
-- =============================================

-- WHERE filters individual rows BEFORE grouping
SELECT   customer_id, SUM(amount) AS total_spent
FROM     orders
WHERE    order_year = 2023           -- filter rows: only 2023 orders enter the group
GROUP BY customer_id;

-- HAVING filters groups AFTER aggregation
SELECT   customer_id, SUM(amount) AS total_spent
FROM     orders
GROUP BY customer_id
HAVING   SUM(amount) > 500;          -- filter groups: only big spenders

-- WRONG: This throws an error — you can't use aggregate in WHERE
-- SELECT customer_id FROM orders WHERE SUM(amount) > 500 GROUP BY customer_id;
-- ERROR: aggregate functions are not allowed in WHERE

-- =============================================
-- Example: Lost Update under Read Committed
-- =============================================
CREATE TABLE account_balance (
    account_id INT PRIMARY KEY,
    balance    DECIMAL(10, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO account_balance VALUES (1, 1000.00);

-- Transaction A
BEGIN TRANSACTION;
SELECT balance FROM account_balance WHERE account_id = 1;  -- reads 1000
-- ... some processing ...
UPDATE account_balance SET balance = balance - 200 WHERE account_id = 1;

-- Transaction B (concurrent)
BEGIN TRANSACTION;
SELECT balance FROM account_balance WHERE account_id = 1;  -- also reads 1000
UPDATE account_balance SET balance = balance - 300 WHERE account_id = 1;

-- If Transaction B commits after A, the final balance is 700 (only B's change)
-- Transaction A's deduction is lost. This is a lost update.

-- Fix: Use SELECT ... FOR UPDATE to lock the row
BEGIN TRANSACTION;
SELECT balance FROM account_balance WHERE account_id = 1 FOR UPDATE;  -- acquires row lock
-- Transaction B will block here until A commits
UPDATE account_balance SET balance = balance - 200 WHERE account_id = 1;
COMMIT;

-- =============================================
-- Deadlock scenario (avoid this)
-- =============================================
CREATE TABLE table_a (id INT PRIMARY KEY, val INT);
CREATE TABLE table_b (id INT PRIMARY KEY, val INT);
INSERT INTO table_a VALUES (1, 10);
INSERT INTO table_b VALUES (1, 20);

-- Transaction 1 (started first)
BEGIN;
UPDATE table_a SET val = val + 1 WHERE id = 1;
-- now try to update table_b
UPDATE table_b SET val = val + 1 WHERE id = 1;  -- blocked if Transaction 2 holds lock on table_b

-- Transaction 2 (concurrent)
BEGIN;
UPDATE table_b SET val = val + 1 WHERE id = 1;
-- now try to update table_a
UPDATE table_a SET val = val + 1 WHERE id = 1;  -- DEADLOCK!

-- One of them will be killed by the database; the other succeeds.
-- Prevention: always update table_a before table_b in both transactions.