📋 Interview Question

📊 Setup: Banking Schema

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE,
    holder_name VARCHAR(100),
    balance DECIMAL(15, 2),
    currency VARCHAR(3) DEFAULT 'USD',
    is_frozen BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    version INT DEFAULT 1  -- For optimistic locking
);

CREATE TABLE transactions (
    transaction_id BIGSERIAL PRIMARY KEY,
    from_account VARCHAR(20),
    to_account VARCHAR(20),
    amount DECIMAL(15, 2),
    currency VARCHAR(3) DEFAULT 'USD',
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW(),
    completed_at TIMESTAMP
);

CREATE TABLE transaction_log (
    log_id BIGSERIAL PRIMARY KEY,
    transaction_id BIGINT REFERENCES transactions(transaction_id),
    action VARCHAR(50),
    details JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert sample accounts
INSERT INTO accounts (account_number, holder_name, balance, currency) VALUES
('ACC001', 'Alice Johnson', 10000.00, 'USD'),
('ACC002', 'Bob Smith', 5000.00, 'USD'),
('ACC003', 'Charlie Brown', 8000.00, 'USD'),
('ACC004', 'Diana Ross', 12000.00, 'EUR'),
('ACC005', 'Eve Davis', 3000.00, 'USD');

🔐 Part 1: ACID Properties

Atomicity Example

-- Transfer $500 from Alice to Bob
-- This MUST be atomic: both debit and credit succeed or both fail

BEGIN;

-- Debit Alice's account
UPDATE accounts
SET balance = balance - 500,
    updated_at = NOW()
WHERE account_number = 'ACC001'
AND balance >= 500;

-- Check if debit succeeded
IF NOT FOUND THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient funds or account not found';
END IF;

-- Credit Bob's account
UPDATE accounts
SET balance = balance + 500,
    updated_at = NOW()
WHERE account_number = 'ACC002';

-- Log the transaction
INSERT INTO transactions (from_account, to_account, amount, status)
VALUES ('ACC001', 'ACC002', 500, 'completed');

-- Update account versions for optimistic locking
UPDATE accounts SET version = version + 1
WHERE account_number IN ('ACC001', 'ACC002');

COMMIT;

-- If ANY step fails, ROLLBACK undoes ALL previous steps

Consistency Example

-- Consistency rule: Total money in system must remain constant
-- Before transfer: Alice=10000, Bob=5000 → Total=15000
-- After transfer: Alice=9500, Bob=5500 → Total=15000

-- Constraint ensuring consistency
ALTER TABLE accounts ADD CONSTRAINT positive_balance
CHECK (balance >= 0);

-- Trigger to maintain consistency
CREATE OR REPLACE FUNCTION check_total_balance()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT SUM(balance) FROM accounts) < 0 THEN
        RAISE EXCEPTION 'Total balance would become negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ensure_balance_consistency
AFTER UPDATE OF balance ON accounts
FOR EACH STATEMENT
EXECUTE FUNCTION check_total_balance();

🔄 Part 2: Isolation Levels

Dirty Read Demonstration

-- Session 1: Begin transaction
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
UPDATE accounts SET balance = 99999 WHERE account_number = 'ACC001';
-- Don't commit yet!

-- Session 2: Read uncommitted data (dirty read)
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE account_number = 'ACC001';
-- Returns 99999 (uncommitted!)

-- Session 1: Rollback
ROLLBACK;

-- Session 2: Balance shows incorrect value!
-- The 99999 was never actually committed

Non-Repeatable Read Demonstration

-- Session 1: Repeatable Read
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_number = 'ACC001';
-- Returns 10000

-- Session 2: Update and commit
BEGIN;
UPDATE accounts SET balance = 9500 WHERE account_number = 'ACC001';
COMMIT;

-- Session 1: Read again
SELECT balance FROM accounts WHERE account_number = 'ACC001';
-- Still returns 10000 (MVCC snapshot)
-- Without REPEATABLE READ, would return 9500

Phantom Read Demonstration

-- Session 1: Repeatable Read
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM accounts WHERE balance > 5000;
-- Returns 3

-- Session 2: Insert new account
BEGIN;
INSERT INTO accounts (account_number, holder_name, balance)
VALUES ('ACC006', 'New User', 8000);
COMMIT;

-- Session 1: Count again
SELECT COUNT(*) FROM accounts WHERE balance > 5000;
-- Still returns 3 (REPEATABLE READ prevents phantom in PostgreSQL)

-- Session 1: But this DOES show phantom:
SELECT * FROM accounts WHERE balance > 5000;
-- May include ACC006 depending on implementation

Setting Isolation Levels

-- Set for entire session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set for next transaction only
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Set default for database
ALTER DATABASE mydb SET default_transaction_isolation = 'repeatable read';

-- Check current setting
SHOW transaction_isolation;

🏗️ Part 3: MVCC (Multi-Version Concurrency Control)

-- MVCC demonstration
-- Create a table with explicit versioning
CREATE TABLE mvcc_demo (
    id SERIAL PRIMARY KEY,
    value TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO mvcc_demo (value) VALUES ('initial');

-- Session 1: Start long-running read
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM mvcc_demo WHERE id = 1;
-- Returns: value='initial', updated_at=T1

-- Session 2: Update the row
BEGIN;
UPDATE mvcc_demo
SET value = 'updated',
    updated_at = NOW()
WHERE id = 1;
COMMIT;
-- PostgreSQL creates a new tuple version

-- Session 1: Read again
SELECT * FROM mvcc_demo WHERE id = 1;
-- Still returns 'initial' (sees snapshot from T1)
-- The new version is invisible to this transaction

-- Session 1: Commit
COMMIT;

-- New Session 3: Now sees the update
BEGIN;
SELECT * FROM mvcc_demo WHERE id = 1;
-- Returns: value='updated'
COMMIT;

MVCC and Transaction ID

-- Check transaction IDs
SELECT
    transaction_id,
    xmin,  -- Transaction ID that created this version
    xmax,  -- Transaction ID that deleted/updated this version (0 if current)
    *
FROM mvcc_demo;

-- Manually manage MVCC (advanced)
SELECT
    pg_current_xact_id() AS current_xid,
    txid_snapshot_xmin(txid_current_snapshot()) AS xmin,
    txid_snapshot_xmax(txid_current_snapshot()) AS xmax;

💀 Part 4: Deadlocks

Creating a Deadlock

-- Session 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';
-- Locks ACC001
-- Wait or do other work...
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC002';
-- Waits for ACC002 (locked by Session 2)

-- Session 2
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_number = 'ACC002';
-- Locks ACC002
-- Wait or do other work...
UPDATE accounts SET balance = balance + 50 WHERE account_number = 'ACC001';
-- Waits for ACC001 (locked by Session 1) → DEADLOCK!

Detecting and Resolving Deadlocks

-- PostgreSQL automatically detects deadlocks
-- and rolls back one transaction

-- Check for deadlocks in logs
SELECT * FROM pg_stat_database WHERE datname = current_database();

-- Monitor locks
SELECT
    l.pid,
    l.mode,
    l.granted,
    a.query,
    a.state
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'accounts'::regclass;

-- Kill a specific process
SELECT pg_terminate_backend(pid);

-- Set deadlock timeout
SET deadlock_timeout = '1s';

Preventing Deadlocks

-- Strategy 1: Consistent lock order
-- Always lock accounts in alphabetical order
BEGIN;
-- Sort account numbers
SELECT * FROM accounts
WHERE account_number IN ('ACC001', 'ACC002')
ORDER BY account_number
FOR UPDATE;
-- Now perform operations in this order

-- Strategy 2: Use NOWAIT to fail immediately
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_number = 'ACC001'
NOWAIT;  -- Fails if can't lock immediately

-- Strategy 3: Use SKIP LOCKED (PostgreSQL 9.5+)
BEGIN;
SELECT * FROM accounts
WHERE is_frozen = false
ORDER BY account_number
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Skips locked rows, avoids waiting

🔒 Part 5: Locking Strategies

Pessimistic Locking

-- SELECT FOR UPDATE: Lock rows for update
BEGIN;
SELECT balance
FROM accounts
WHERE account_number = 'ACC001'
FOR UPDATE;  -- Other transactions must wait

-- Perform check and update
UPDATE accounts
SET balance = balance - 500
WHERE account_number = 'ACC001'
AND balance >= 500;

COMMIT;

-- Variants
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;  -- Read lock
SELECT * FROM accounts WHERE id = 1 FOR KEY SHARE;  -- Minimal lock

Optimistic Locking

-- Use version column to detect conflicts
BEGIN;

-- Read current version
SELECT balance, version
FROM accounts
WHERE account_number = 'ACC001';
-- Returns: balance=10000, version=5

-- Update with version check
UPDATE accounts
SET balance = balance - 500,
    version = version + 1,
    updated_at = NOW()
WHERE account_number = 'ACC001'
AND version = 5;  -- Check version hasn't changed

-- Check if update succeeded
IF NOT FOUND THEN
    ROLLBACK;
    RAISE EXCEPTION 'Concurrent modification detected';
END IF;

COMMIT;

-- No explicit lock held during the operation
-- Conflict detected at UPDATE time

Advisory Locks

-- Application-level locks (not tied to data)
SELECT pg_advisory_lock(12345);  -- Acquire lock with ID
-- Do critical work
SELECT pg_advisory_unlock(12345);  -- Release lock

-- Try to acquire without waiting
SELECT pg_try_advisory_lock(12345);  -- Returns true/false

-- Transaction-scoped advisory lock
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- Lock released automatically on COMMIT/ROLLBACK
COMMIT;

📊 Part 6: Transaction Patterns

Savepoints

-- Use savepoints for partial rollback
BEGIN;

INSERT INTO transactions (from_account, to_account, amount)
VALUES ('ACC001', 'ACC002', 500);

SAVEPOINT after_transaction_insert;

-- This might fail
INSERT INTO transaction_log (transaction_id, action)
VALUES (currval('transactions_transaction_id'), 'created');

-- If log insert fails, rollback to savepoint
-- Transaction record is preserved
ROLLBACK TO SAVEPOINT after_transaction_insert;

-- Try again with different approach
INSERT INTO transaction_log (transaction_id, action, details)
VALUES (
    currval('transactions_transaction_id'),
    'created',
    '{"source": "transfer"}'::jsonb
);

COMMIT;

Deferred Constraints

-- Deferred constraints check at COMMIT, not at statement time
ALTER TABLE accounts
ADD CONSTRAINT sufficient_funds CHECK (balance >= 0)
DEFERRABLE INITIALLY DEFERRED;

BEGIN;
-- This would normally fail (balance goes negative temporarily)
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC002';

-- Constraint checked at COMMIT
-- If ACC001's balance is still >= 0, succeeds
COMMIT;

⏱️ Complexity Analysis

🎯 Quiz Section

🏆 Best Practices for Interviews

-- BAD: Long transaction
BEGIN;
-- User interaction happens here...
-- User thinks for 5 minutes...
-- Database holds locks!
COMMIT;

-- GOOD: Short transaction
BEGIN;
-- Quick database operations only
COMMIT;
-- User interaction outside transaction

-- Use READ COMMITTED for most operations
-- Use REPEATABLE READ for consistent reads
-- Use SERIALIZABLE only when needed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Application should handle deadlocks with retries
-- PostgreSQL deadlock error code: 40P01
BEGIN;
-- Operation
EXCEPTION
    WHEN deadlock_detected THEN
        -- Retry logic
        RAISE NOTICE 'Deadlock detected, retrying...';
END;

-- When you need to coordinate outside of data locks
SELECT pg_try_advisory_lock(hashtext('my_critical_section'));

SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;