📋 Interview Question

📊 Setup: Booking Schema

CREATE TABLE shows (
    show_id SERIAL PRIMARY KEY,
    show_name VARCHAR(200),
    venue VARCHAR(200),
    show_date TIMESTAMP,
    total_seats INT,
    available_seats INT
);

CREATE TABLE seats (
    seat_id SERIAL PRIMARY KEY,
    show_id INT REFERENCES shows(show_id),
    seat_number VARCHAR(10),
    section VARCHAR(50),
    price DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'available',  -- available, reserved, sold
    reserved_by VARCHAR(100),
    reserved_until TIMESTAMP,
    version INT DEFAULT 1
);

CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    show_id INT,
    seat_id INT,
    user_id VARCHAR(100),
    booking_time TIMESTAMP DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'pending',
    payment_id VARCHAR(100)
);

CREATE TABLE booking_errors (
    error_id SERIAL PRIMARY KEY,
    booking_id INT,
    error_type VARCHAR(50),
    error_message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert sample data
INSERT INTO shows (show_name, venue, show_date, total_seats, available_seats) VALUES
('Rock Concert', 'Madison Square Garden', '2024-06-15 20:00:00', 20000, 20000),
('Broadway Show', 'Theater District', '2024-06-16 19:00:00', 1500, 1500);

INSERT INTO seats (show_id, seat_number, section, price)
SELECT
    1,
    'S' || i || '-' || (i % 20 + 1),
    CASE
        WHEN i <= 100 THEN 'VIP'
        WHEN i <= 500 THEN 'Premium'
        ELSE 'General'
    END,
    CASE
        WHEN i <= 100 THEN 200.00
        WHEN i <= 500 THEN 100.00
        ELSE 50.00
    END
FROM generate_series(1, 1000) i;

🔐 Part 1: Pessimistic Locking

SELECT FOR UPDATE

-- Reserve a seat with pessimistic locking
BEGIN;

-- Lock the seat row
SELECT *
FROM seats
WHERE seat_id = 1
FOR UPDATE;  -- Other transactions must wait

-- Check availability
SELECT status FROM seats WHERE seat_id = 1;
-- If status != 'available', ROLLBACK

-- Reserve the seat
UPDATE seats
SET status = 'reserved',
    reserved_by = 'user123',
    reserved_until = NOW() + INTERVAL '15 minutes'
WHERE seat_id = 1;

-- Create booking record
INSERT INTO bookings (show_id, seat_id, user_id, status)
VALUES (1, 1, 'user123', 'reserved');

COMMIT;

Lock Timeout

-- Set lock timeout to prevent indefinite waiting
SET lock_timeout = '5s';  -- Fail after 5 seconds

BEGIN;
-- This will fail if lock can't be acquired within 5 seconds
SELECT * FROM seats WHERE seat_id = 1 FOR UPDATE;
-- If timeout: ERROR: lock timeout
COMMIT;

NOWAIT and SKIP LOCKED

-- Fail immediately if lock can't be acquired
BEGIN;
SELECT * FROM seats
WHERE seat_id = 1
FOR UPDATE NOWAIT;
-- If locked: ERROR: could not obtain lock
COMMIT;

-- Skip locked rows (find next available seat)
BEGIN;
SELECT *
FROM seats
WHERE show_id = 1
AND status = 'available'
ORDER BY seat_id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Returns next available seat, skipping any locked ones
COMMIT;

🔄 Part 2: Optimistic Locking

Version Column

-- Add version column to seats table
ALTER TABLE seats ADD COLUMN version INT DEFAULT 1;

-- Optimistic lock reservation
BEGIN;

-- Read current state
SELECT seat_id, status, version
FROM seats
WHERE seat_id = 1;
-- Returns: seat_id=1, status='available', version=1

-- Attempt update with version check
UPDATE seats
SET status = 'reserved',
    reserved_by = 'user123',
    version = version + 1
WHERE seat_id = 1
AND version = 1  -- Check version hasn't changed
AND status = 'available';

-- Check if update succeeded
GET DIAGNOSTICS row_count = ROW_COUNT;
IF row_count = 0 THEN
    ROLLBACK;
    RAISE EXCEPTION 'Seat was reserved by another user';
END IF;

COMMIT;

Timestamp-Based Optimistic Lock

-- Alternative: Use timestamp for optimistic lock
SELECT seat_id, status, updated_at
FROM seats WHERE seat_id = 1;
-- Returns: updated_at='2024-01-15 10:30:00'

UPDATE seats
SET status = 'reserved',
    updated_at = NOW()
WHERE seat_id = 1
AND updated_at = '2024-01-15 10:30:00'::timestamp;

💀 Part 3: Deadlock Prevention

-- Deadlock scenario: Two users trying to reserve seats in opposite order

-- Session 1: Tries to reserve seat 1, then seat 2
BEGIN;
SELECT * FROM seats WHERE seat_id = 1 FOR UPDATE;
-- Wait...
SELECT * FROM seats WHERE seat_id = 2 FOR UPDATE;
-- DEADLOCK if Session 2 has seat 2 locked
COMMIT;

-- Session 2: Tries to reserve seat 2, then seat 1
BEGIN;
SELECT * FROM seats WHERE seat_id = 2 FOR UPDATE;
-- Wait...
SELECT * FROM seats WHERE seat_id = 1 FOR UPDATE;
-- DEADLOCK if Session 1 has seat 1 locked
COMMIT;

-- Prevention: Always lock in consistent order
-- Both sessions should lock seat 1 first, then seat 2

Deadlock Detection and Resolution

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

-- Monitor for deadlocks
SELECT * FROM pg_stat_database
WHERE datname = current_database()
AND deadlocks > 0;

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

-- Retry logic for deadlock handling
CREATE OR REPLACE PROCEDURE reserve_seat_with_retry(
    p_seat_id INT,
    p_user_id VARCHAR,
    p_max_retries INT DEFAULT 3
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_retry INT := 0;
    v_success BOOLEAN := false;
BEGIN
    WHILE v_retry < p_max_retries AND NOT v_success LOOP
        BEGIN
            PERFORM reserve_seat(p_seat_id, p_user_id);
            v_success := true;
        EXCEPTION
            WHEN deadlock_detected THEN
                v_retry := v_retry + 1;
                RAISE NOTICE 'Deadlock, retry % of %', v_retry, p_max_retries;
                PERFORM pg_sleep(0.1 * POWER(2, v_retry - 1));  -- Exponential backoff
            WHEN OTHERS THEN
                RAISE;
        END;
    END LOOP;

    IF NOT v_success THEN
        RAISE EXCEPTION 'Failed after % retries', p_max_retries;
    END IF;
END;
$$;

🎯 Part 4: Multi-Step Operations

Reservation with Payment

-- Atomic multi-step operation
CREATE OR REPLACE PROCEDURE reserve_and_pay(
    p_seat_id INT,
    p_user_id VARCHAR,
    p_payment_method VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_booking_id INT;
    v_seat_price DECIMAL;
BEGIN
    -- Step 1: Lock and reserve seat
    SELECT price INTO v_seat_price
    FROM seats
    WHERE seat_id = p_seat_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Seat not found';
    END IF;

    UPDATE seats
    SET status = 'reserved',
        reserved_by = p_user_id,
        reserved_until = NOW() + INTERVAL '15 minutes'
    WHERE seat_id = p_seat_id
    AND status = 'available';

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Seat already reserved';
    END IF;

    -- Step 2: Create booking
    INSERT INTO bookings (show_id, seat_id, user_id, status)
    SELECT show_id, seat_id, p_user_id, 'pending'
    FROM seats WHERE seat_id = p_seat_id
    RETURNING booking_id INTO v_booking_id;

    -- Step 3: Process payment (simulated)
    -- In real system: call payment API
    UPDATE bookings
    SET status = 'confirmed',
        payment_id = 'PAY-' || v_booking_id
    WHERE booking_id = v_booking_id;

    -- Step 4: Update seat to sold
    UPDATE seats
    SET status = 'sold'
    WHERE seat_id = p_seat_id;

    COMMIT;
    RAISE NOTICE 'Booking % confirmed', v_booking_id;
END;
$$;

Savepoints for Partial Rollback

BEGIN;

-- Step 1: Reserve seat
SAVEPOINT after_reservation;
UPDATE seats SET status = 'reserved' WHERE seat_id = 1;

-- Step 2: Create booking
SAVEPOINT after_booking;
INSERT INTO bookings (...) VALUES (...);

-- Step 3: Process payment (might fail)
BEGIN;
    -- Payment processing
EXCEPTION
    WHEN OTHERS THEN
        -- Rollback to after_booking, keep reservation
        ROLLBACK TO SAVEPOINT after_booking;
        -- Log error and continue with different approach
END;

COMMIT;

📊 Part 5: Advisory Locks

-- Application-level locks for coordination
-- Useful when you can't use data-level locks

-- Acquire advisory lock
SELECT pg_advisory_lock(hashtext('show_1_seats'));
-- Do work...
SELECT pg_advisory_unlock(hashtext('show_1_seats'));

-- Try without blocking
SELECT pg_try_advisory_lock(hashtext('show_1_seats'));
-- Returns true if acquired, false if already locked

-- Transaction-scoped advisory lock
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('critical_operation'));
-- Lock automatically released at COMMIT/ROLLBACK
COMMIT;

🎯 Quiz Section

🏆 Best Practices for Interviews

-- High conflict, data integrity critical → Pessimistic
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Low conflict, high concurrency → Optimistic
UPDATE accounts SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;

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

-- GOOD: Short transaction
BEGIN;
-- Quick operations only
COMMIT;

-- Always lock resources in same order to prevent deadlocks
-- Sort by ID before locking
SELECT * FROM seats WHERE seat_id IN (1, 2) ORDER BY seat_id FOR UPDATE;

-- Row-level (default) for most cases
SELECT * FROM seats WHERE id = 1 FOR UPDATE;

-- Table-level for bulk operations
LOCK TABLE seats IN EXCLUSIVE MODE;

-- Advisory for application coordination
SELECT pg_advisory_lock(key);

-- Always implement retry logic
EXCEPTION
    WHEN deadlock_detected THEN
        -- Retry with backoff
END;