Database Locking Mechanisms Explained — Internals, Deadlocks & Production Gotchas

-- ============================================================
-- 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;