SELECT FOR UPDATE Without Index — Full Table Lock Trap

-- ============================================================
-- DEMO: Observe lock acquisition and compatibility in PostgreSQL
-- Run each block in a separate psql session as indicated
-- ============================================================

-- Setup: create a simple accounts table
CREATE TABLE accounts (
    account_id   SERIAL PRIMARY KEY,
    holder_name  TEXT        NOT NULL,
    balance      NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (holder_name, balance) VALUES
    ('Alice Nguyen',  5000.00),
    ('Bob Tremblay',  3200.00),
    ('Carol Okafor', 12000.00);

-- ============================================================
-- SESSION 1: Acquire a row-level Exclusive lock via FOR UPDATE
-- ============================================================
BEGIN;

-- FOR UPDATE places a RowExclusiveLock on the table and an
-- ExclusiveLock on the matching row's heap tuple.
-- No other transaction can UPDATE or DELETE this row until we COMMIT.
SELECT account_id, holder_name, balance
FROM   accounts
WHERE  account_id = 1
FOR UPDATE;  -- <-- this is the exclusive row lock

-- Simulate a slow business operation (do NOT commit yet)
-- Leave this transaction open and switch to Session 2

-- ============================================================
-- SESSION 2 (run while Session 1 is still open): Try to read
-- ============================================================

-- Plain SELECT — succeeds immediately because PostgreSQL MVCC
-- serves this from a consistent snapshot, no lock needed
SELECT account_id, holder_name, balance
FROM   accounts
WHERE  account_id = 1;  -- returns immediately with old value

-- FOR UPDATE on the same row — this BLOCKS because Session 1
-- already holds the exclusive row lock
SELECT account_id, holder_name, balance
FROM   accounts
WHERE  account_id = 1
FOR UPDATE;  -- <-- will hang until Session 1 commits or rolls back

-- ============================================================
-- SESSION 3: Inspect active locks while Session 2 is blocked
-- ============================================================
SELECT
    pid,                          -- process ID of the backend
    relation::regclass AS locked_object,
    mode,                         -- lock mode requested or held
    granted,                      -- FALSE means it's waiting
    wait_event_type,
    wait_event,
    query
FROM  pg_locks
JOIN  pg_stat_activity USING (pid)
WHERE relation = 'accounts'::regclass
ORDER BY granted DESC, pid;

-- ============================================================
-- SESSION 1: Now commit to release the lock
-- ============================================================
UPDATE accounts
SET    balance = balance - 200.00
WHERE  account_id = 1;  -- deduct from Alice's account

COMMIT;  -- Session 2's blocked FOR UPDATE will now proceed

-- ============================================================
-- DEMO: MVCC snapshot behavior across isolation levels
-- Demonstrates READ COMMITTED vs REPEATABLE READ in PostgreSQL
-- ============================================================

-- Setup: a product inventory table
CREATE TABLE product_inventory (
    product_id   INT          PRIMARY KEY,
    product_name TEXT         NOT NULL,
    stock_count  INT          NOT NULL,
    last_updated TIMESTAMPTZ  DEFAULT now()
);

INSERT INTO product_inventory VALUES
    (101, 'Wireless Keyboard', 50, now()),
    (102, 'USB-C Hub',         30, now()),
    (103, 'Laptop Stand',      75, now());

-- ============================================================
-- SCENARIO A: READ COMMITTED — snapshot refreshes each statement
-- ============================================================

-- SESSION 1: Start a READ COMMITTED transaction (PostgreSQL default)
BEGIN ISOLATION LEVEL READ COMMITTED;

-- First read: sees stock_count = 50 for keyboard
SELECT product_id, product_name, stock_count
FROM   product_inventory
WHERE  product_id = 101;

-- ---- SESSION 2: Commits an update while Session 1 is still open ----
-- BEGIN;
-- UPDATE product_inventory SET stock_count = 48 WHERE product_id = 101;
-- COMMIT;
-- --------------------------------------------------------------------

-- SESSION 1: Second read in SAME transaction — sees the NEW value 48
-- because READ COMMITTED re-snaps at each statement boundary.
-- This is a non-repeatable read — same query, different result.
SELECT product_id, product_name, stock_count
FROM   product_inventory
WHERE  product_id = 101;

COMMIT;

-- ============================================================
-- SCENARIO B: REPEATABLE READ — snapshot frozen at BEGIN
-- ============================================================

-- SESSION 1: Start a REPEATABLE READ transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- First read: sees stock_count = 48 (current committed value)
SELECT product_id, product_name, stock_count
FROM   product_inventory
WHERE  product_id = 101;

-- ---- SESSION 2: Commits another update ----
-- BEGIN;
-- UPDATE product_inventory SET stock_count = 45 WHERE product_id = 101;
-- COMMIT;
-- -------------------------------------------

-- SESSION 1: Second read — still sees 48, NOT 45
-- The snapshot was frozen at transaction start.
-- This guarantees repeatable reads within the transaction.
SELECT product_id, product_name, stock_count
FROM   product_inventory
WHERE  product_id = 101;

-- Attempting to UPDATE the row that Session 2 already changed:
-- PostgreSQL will detect the write-write conflict and raise:
-- ERROR: could not serialize access due to concurrent update
UPDATE product_inventory
SET    stock_count = stock_count - 1  -- trying to sell one unit
WHERE  product_id = 101;

ROLLBACK;  -- must retry the entire transaction from scratch

-- ============================================================
-- Check the xmin/xmax system columns to see MVCC versions
-- ============================================================
SELECT
    product_id,
    product_name,
    stock_count,
    xmin,   -- transaction ID that INSERTed/last UPDATEd this version
    xmax    -- transaction ID that DELETEd/superseded this version (0 = live)
FROM product_inventory;

-- ============================================================
-- DEMO: Classic deadlock scenario and its prevention
-- Run on PostgreSQL. Uses two accounts for a funds transfer.
-- ============================================================

-- Setup
CREATE TABLE bank_accounts (
    account_id     INT          PRIMARY KEY,
    owner_name     TEXT         NOT NULL,
    balance        NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO bank_accounts VALUES
    (1, 'Alice Nguyen',  10000.00),
    (2, 'Bob Tremblay',   5000.00);

-- ============================================================
-- THE DEADLOCK-PRONE PATTERN
-- Transaction 1: Transfer $100 from Alice (id=1) to Bob (id=2)
-- Transaction 2: Transfer $50  from Bob (id=2) to Alice (id=1)
-- Both start simultaneously — classic deadlock setup
-- ============================================================

-- Transaction 1 (Session 1)
BEGIN;
UPDATE bank_accounts SET balance = balance - 100 WHERE account_id = 1;  -- locks row 1
-- (now pause — Session 2 runs its first UPDATE below)
UPDATE bank_accounts SET balance = balance + 100 WHERE account_id = 2;  -- BLOCKS: waits for row 2
COMMIT;

-- Transaction 2 (Session 2 — run the first line WHILE Session 1 is paused)
BEGIN;
UPDATE bank_accounts SET balance = balance - 50  WHERE account_id = 2;  -- locks row 2
UPDATE bank_accounts SET balance = balance + 50  WHERE account_id = 1;  -- DEADLOCK: waits for row 1
COMMIT;
-- PostgreSQL detects the cycle and raises:
-- ERROR:  deadlock detected
-- DETAIL: Process 18433 waits for ShareLock on transaction 50401;
--         blocked by process 18432.
--         Process 18432 waits for ShareLock on transaction 50402;
--         blocked by process 18433.
-- HINT:   See server log for query details.

-- ============================================================
-- THE DEADLOCK-SAFE PATTERN
-- Always lock rows in ascending account_id order regardless of
-- which direction the transfer flows
-- ============================================================

CREATE OR REPLACE FUNCTION transfer_funds(
    p_from_account_id  INT,
    p_to_account_id    INT,
    p_amount           NUMERIC
) RETURNS VOID AS $$
DECLARE
    v_low_id   INT := LEAST(p_from_account_id, p_to_account_id);
    v_high_id  INT := GREATEST(p_from_account_id, p_to_account_id);
BEGIN
    -- Always lock the LOWER id first — consistent ordering prevents deadlock
    -- regardless of which direction the transfer flows
    PERFORM account_id, balance
    FROM    bank_accounts
    WHERE   account_id IN (v_low_id, v_high_id)
    ORDER BY account_id ASC   -- <-- the critical ordering
    FOR UPDATE;               -- acquires exclusive locks in a deterministic order

    -- Validate sufficient funds
    IF (SELECT balance FROM bank_accounts WHERE account_id = p_from_account_id) < p_amount THEN
        RAISE EXCEPTION 'Insufficient funds in account %', p_from_account_id;
    END IF;

    -- Perform the actual transfer
    UPDATE bank_accounts
    SET    balance = balance - p_amount
    WHERE  account_id = p_from_account_id;

    UPDATE bank_accounts
    SET    balance = balance + p_amount
    WHERE  account_id = p_to_account_id;
END;
$$ LANGUAGE plpgsql;

-- Both transfers now run safely in parallel — no deadlock
SELECT transfer_funds(1, 2, 100);  -- Alice -> Bob
SELECT transfer_funds(2, 1,  50);  -- Bob -> Alice (different session, same time)

-- Verify final balances
SELECT account_id, owner_name, balance FROM bank_accounts ORDER BY account_id;

-- ============================================================
-- DEMO: Optimistic Locking with a version column
-- No explicit locks acquired at read time.
-- Conflict detection happens at UPDATE using the version stamp.
-- ============================================================

CREATE TABLE product_catalog (
    product_id     SERIAL        PRIMARY KEY,
    product_name   TEXT          NOT NULL,
    unit_price     NUMERIC(10,2) NOT NULL,
    row_version    INT           NOT NULL DEFAULT 1,  -- optimistic lock sentinel
    last_modified  TIMESTAMPTZ   DEFAULT now()
);

INSERT INTO product_catalog (product_name, unit_price) VALUES
    ('Mechanical Keyboard', 129.99),
    ('27-inch Monitor',     349.99),
    ('Ergonomic Mouse',      59.99);

-- ============================================================
-- APPLICATION LAYER PATTERN
-- Step 1: Read the row AND capture its row_version
-- Step 2: Do business logic (could take several seconds)
-- Step 3: UPDATE only if row_version is still the same value
--         we read — if not, someone else changed it first
-- ============================================================

-- Step 1: Read the keyboard price (application caches row_version = 1)
SELECT product_id, product_name, unit_price, row_version
FROM   product_catalog
WHERE  product_id = 1;
-- Returns: product_id=1, unit_price=129.99, row_version=1

-- (Imagine a concurrent session updates the price to 119.99 and
--  increments row_version to 2 while our app is thinking)

-- Step 3: Our application tries to set price to 124.99
-- We include WHERE row_version = 1 (the version we read)
UPDATE product_catalog
SET
    unit_price    = 124.99,
    row_version   = row_version + 1,  -- increment the version
    last_modified = now()
WHERE product_id   = 1
  AND row_version  = 1;              -- <-- the optimistic check

-- Check how many rows were actually updated
GET DIAGNOSTICS -- (in PL/pgSQL) or check rowcount in application layer

-- If rowcount = 0, the optimistic lock failed — someone else
-- modified the row between our read and our write.
-- The application must re-read, re-apply its logic, and retry.

-- ============================================================
-- Stored procedure with retry logic for optimistic locking
-- ============================================================
CREATE OR REPLACE FUNCTION update_product_price(
    p_product_id   INT,
    p_new_price    NUMERIC,
    p_read_version INT       -- version the caller observed at read time
) RETURNS TEXT AS $$
DECLARE
    rows_affected INT;
BEGIN
    UPDATE product_catalog
    SET
        unit_price    = p_new_price,
        row_version   = row_version + 1,
        last_modified = now()
    WHERE product_id  = p_product_id
      AND row_version = p_read_version;  -- optimistic conflict check

    GET DIAGNOSTICS rows_affected = ROW_COUNT;

    IF rows_affected = 0 THEN
        -- Return a signal the caller can detect and retry
        RETURN 'CONFLICT: row was modified by another transaction — please re-read and retry';
    END IF;

    RETURN 'OK: price updated successfully';
END;
$$ LANGUAGE plpgsql;

-- Simulate a clean update (correct version)
SELECT update_product_price(2, 329.99, 1);  -- Monitor price drop, correct version

-- Simulate a stale update (wrong version — someone else already updated)
SELECT update_product_price(2, 319.99, 1);  -- version 1 is stale now, it's 2

-- Verify final state
SELECT product_id, product_name, unit_price, row_version FROM product_catalog;

-- ============================================================
-- PostgreSQL: Find blocking and blocked transactions
-- ============================================================

-- Query 1: Who is waiting and who holds the lock?
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON 
    blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database = blocked_locks.database
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.page = blocked_locks.page
    AND blocking_locks.tuple = blocked_locks.tuple
    AND blocking_locks.pid != blocked.pid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Query 2: Count of waiting processes by wait event
SELECT wait_event, wait_event_type, COUNT(*)
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
  AND state = 'active'
GROUP BY wait_event, wait_event_type
ORDER BY COUNT(*) DESC;

-- Query 3: Transactions that have been running the longest
SELECT pid, state, query, xact_start,
    EXTRACT(epoch FROM now() - xact_start)::int AS seconds_since_start
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY xact_start
LIMIT 10;

-- ============================================================
-- MySQL InnoDB: Lock monitoring queries
-- ============================================================

-- Query 1: Current lock waits (requires PERFORMANCE_SCHEMA)
-- Requires 'performance_schema' enabled
SELECT * FROM performance_schema.data_lock_waits;

-- Query 2: Show InnoDB status (deadlock and lock information)
SHOW ENGINE INNODB STATUS\G

-- Query 3: Long-running transactions
SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 10;

-- ============================================================
-- Setting lock timeouts to prevent indefinite waits
-- ============================================================

-- PostgreSQL: Set per-session
SET lock_timeout = '2s';

-- MySQL: Set globally (affects new sessions)
SET GLOBAL innodb_lock_wait_timeout = 2;  -- seconds

// io.thecodeforge — database tutorial

-- Use a versioned advisory lock for distributed workers
-- Each worker claims a slot and increments the fence_token

CREATE TABLE job_locks (
    job_name TEXT PRIMARY KEY,
    fence_token BIGINT NOT NULL DEFAULT 0,
    locked_by TEXT,
    locked_at TIMESTAMPTZ
);

-- Atomic claim using a fencing token
UPDATE job_locks
SET fence_token = fence_token + 1,
    locked_by = 'worker-001',
    locked_at = NOW()
WHERE job_name = 'inventory-sync'
  AND fence_token = (  -- only claim if token matches last known
      SELECT fence_token FROM job_locks WHERE job_name = 'inventory-sync'
  )
RETURNING fence_token;

-- Now acquire the PostgreSQL advisory lock to prevent concurrent local execution
SELECT pg_try_advisory_xact_lock(hashtext('inventory-sync'));
-- Returns true if lock acquired, false if another local session holds it

// io.thecodeforge — database tutorial

-- Bad: single UPDATE that triggers lock escalation after 5,000 row locks
UPDATE orders
SET status = 'archived'
WHERE created_at < '2023-01-01';
-- If this hits 5,000 rows, SQL Server escalates to a table lock

-- Good: batching with a loop, max 500 locks per transaction
DO $$
DECLARE
    batch_size INT := 500;
    affected INT;
BEGIN
    LOOP
        UPDATE orders
        SET status = 'archived'
        WHERE id IN (
            SELECT id
            FROM orders
            WHERE created_at < '2023-01-01'
              AND status != 'archived'
            LIMIT batch_size
            FOR UPDATE SKIP LOCKED  -- skip rows already locked by other txns
        );
        GET DIAGNOSTICS affected = ROW_COUNT;
        
        COMMIT;  -- release locks after each batch
        
        EXIT WHEN affected = 0;
    END LOOP;
END;
$$;

// io.thecodeforge — database tutorial

-- Check tuple-level version churn in PostgreSQL
SELECT relname,
       n_tup_ins,
       n_tup_upd,
       n_tup_hot_upd,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders'
ORDER BY n_dead_tup DESC;

-- SQL Server: find oldest active transaction pinning versions
SELECT DATEDIFF(minute, transaction_begin_time, GETDATE()) AS tenure_minutes,
       COUNT(*) AS version_count
FROM sys.dm_tran_active_snapshot_database_transactions
GROUP BY transaction_begin_time
ORDER BY tenure_minutes DESC;

-- Postgres: check snapshots and age
SELECT pid, xact_start, backend_start,
       age(backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;