Activity 11: Database Transactions and Concurrency Control

# Ensure Docker PostgreSQL is running
docker exec -it web4_postgres psql -U student -d web4_dev

# Open 3 terminal windows for concurrent testing
# Terminal 1: Main connection
# Terminal 2: Concurrent connection #1  
# Terminal 3: Concurrent connection #2

-- Banking system with proper constraints
CREATE TABLE banking_accounts (
    account_id SERIAL PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    account_holder VARCHAR(100) NOT NULL,
    balance DECIMAL(15,2) NOT NULL CHECK (balance >= 0),
    version INTEGER DEFAULT 0,  -- For optimistic locking
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Transaction audit trail
CREATE TABLE transfer_log (
    transfer_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    from_account INTEGER REFERENCES banking_accounts(account_id),
    to_account INTEGER REFERENCES banking_accounts(account_id),
    amount DECIMAL(15,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    initiated_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    session_id INTEGER DEFAULT pg_backend_pid()
);

-- Create test accounts
INSERT INTO banking_accounts (account_number, account_holder, balance)
VALUES 
    ('ACC001', 'Alice Johnson', 5000.00),
    ('ACC002', 'Bob Smith', 3000.00),
    ('ACC003', 'Carol Davis', 2000.00);

CREATE OR REPLACE FUNCTION unsafe_transfer(
    from_id INTEGER,
    to_id INTEGER,
    amount DECIMAL
) RETURNS TEXT AS $
DECLARE
    current_balance DECIMAL;
BEGIN
    -- DANGER: Race condition waiting to happen!
    SELECT balance INTO current_balance 
    FROM banking_accounts WHERE account_id = from_id;
    
    -- Simulate processing delay
    PERFORM pg_sleep(0.1);
    
    IF current_balance >= amount THEN
        -- These updates are NOT atomic!
        UPDATE banking_accounts SET balance = balance - amount 
        WHERE account_id = from_id;
        
        UPDATE banking_accounts SET balance = balance + amount 
        WHERE account_id = to_id;
        
        RETURN 'Transfer completed';
    ELSE
        RETURN 'Insufficient funds';
    END IF;
END;
$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION safe_transfer(
    from_id INTEGER,
    to_id INTEGER,
    amount DECIMAL
) RETURNS TEXT AS $
DECLARE
    current_balance DECIMAL;
BEGIN
    -- Lock accounts in consistent order (prevent deadlocks)
    IF from_id < to_id THEN
        SELECT balance INTO current_balance 
        FROM banking_accounts WHERE account_id = from_id FOR UPDATE;
        
        PERFORM balance FROM banking_accounts 
        WHERE account_id = to_id FOR UPDATE;
    ELSE
        PERFORM balance FROM banking_accounts 
        WHERE account_id = to_id FOR UPDATE;
        
        SELECT balance INTO current_balance 
        FROM banking_accounts WHERE account_id = from_id FOR UPDATE;
    END IF;
    
    -- Check funds with locked balance
    IF current_balance >= amount THEN
        UPDATE banking_accounts 
        SET balance = balance - amount
        WHERE account_id = from_id;
        
        UPDATE banking_accounts 
        SET balance = balance + amount
        WHERE account_id = to_id;
        
        RETURN 'Transfer completed safely';
    ELSE
        RETURN 'Insufficient funds';
    END IF;
END;
$ LANGUAGE plpgsql;

SELECT account_id, account_holder, balance 
FROM banking_accounts ORDER BY account_id;

-- Record total system money (should never change!)
SELECT SUM(balance) as total_money FROM banking_accounts;

BEGIN;
SELECT unsafe_transfer(1, 2, 500.00);
-- DON'T COMMIT YET! Switch to Terminal 3

BEGIN;
SELECT unsafe_transfer(1, 3, 700.00);
-- DON'T COMMIT YET! Go back to Terminal 2

-- Terminal 2: COMMIT;
-- Terminal 3: COMMIT;

-- Check the damage - money has disappeared!
SELECT account_id, balance FROM banking_accounts ORDER BY account_id;
SELECT SUM(balance) as total_money FROM banking_accounts;
-- Record how much money was lost: $______

-- Reset accounts
UPDATE banking_accounts SET balance = 5000.00 WHERE account_id = 1;
UPDATE banking_accounts SET balance = 3000.00 WHERE account_id = 2;
UPDATE banking_accounts SET balance = 2000.00 WHERE account_id = 3;

-- Test concurrent safe transfers
-- Terminal 2: SELECT safe_transfer(1, 2, 500.00);
-- Terminal 3: SELECT safe_transfer(1, 3, 700.00);

-- Verify no money was lost!
SELECT SUM(balance) as total_money FROM banking_accounts;

-- INTENTIONALLY create a deadlock!
-- Terminal 2: BEGIN; UPDATE banking_accounts SET balance = balance + 100 WHERE account_id = 1;
-- Terminal 3: BEGIN; UPDATE banking_accounts SET balance = balance + 100 WHERE account_id = 2;
-- Terminal 2: UPDATE banking_accounts SET balance = balance + 100 WHERE account_id = 2; (hangs)
-- Terminal 3: UPDATE banking_accounts SET balance = balance + 100 WHERE account_id = 1; (DEADLOCK!)

-- PostgreSQL automatically resolves deadlock by killing one transaction

-- 1. No money lost or created
SELECT 
    SUM(balance) as current_total,
    10000.00 as expected_total,
    CASE WHEN SUM(balance) = 10000.00 THEN '✅ PASS' ELSE '❌ FAIL' END as result
FROM banking_accounts;

-- 2. No negative balances
SELECT COUNT(*) as negative_count FROM banking_accounts WHERE balance < 0;

-- 3. All transfers logged
SELECT status, COUNT(*) FROM transfer_log GROUP BY status;