Locking, Deadlocks & High-Concurrency Patterns

-- PostgreSQL Lock Types (from weakest to strongest)

-- 1. ACCESS SHARE — acquired by SELECT
-- Doesn't block anything except ACCESS EXCLUSIVE
SELECT * FROM employees;  -- takes ACCESS SHARE lock

-- 2. ROW SHARE — acquired by SELECT ... FOR UPDATE/SHARE
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Locks this specific row so no one else can modify it

-- 3. ROW EXCLUSIVE — acquired by INSERT, UPDATE, DELETE
UPDATE employees SET salary = 90000 WHERE id = 5;
-- Locks only the affected rows,
...

-- Row-level locking strategies

-- FOR UPDATE: exclusive lock (I will modify this row)
BEGIN;
SELECT * FROM inventory
WHERE product_id = 100
FOR UPDATE;
-- Now this row is locked until COMMIT/ROLLBACK
-- Other sessions trying FOR UPDATE will WAIT
UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 100;
COMMIT;

-- FOR SHARE: shared lock (I'm reading, don't change it)
BEGIN;
SELECT * FROM products
WHERE id = 100
FOR SHARE;
-- Other FOR SHARE allowed, but FOR UPDATE blocked
COMMIT;

-
...

-- DEADLOCK: two transactions each waiting for the other

-- Session A:                    -- Session B:
-- BEGIN;                        -- BEGIN;
-- UPDATE accounts               -- UPDATE accounts
--   SET bal = bal - 100          --   SET bal = bal - 50
--   WHERE id = 1;  ← locks 1   --   WHERE id = 2;  ← locks 2
--
-- UPDATE accounts               -- UPDATE accounts
--   SET bal = bal + 100          --   SET bal = bal + 50
--   WHERE id = 2;  ← WAITS     --   WHERE id = 1;  ← WAITS
--     
...

-- Detecting and monitoring lock issues

-- 1. Find blocked queries right now
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       NOW() - blocked.query_start AS wait_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON blocked.pid = bl.pid AND NOT bl.granted
JOIN pg_locks gl ON bl.relation = gl.relation
    AND bl.locktype = gl.locktype AND gl.granted
JOIN pg_stat_activity blocking ON
...

-- Optimistic Locking: no database locks, use version numbers
-- Best for: web apps where lock duration is unpredictable

-- Table has a version column
CREATE TABLE documents (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    version INT DEFAULT 1,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Step 1: Read the document (no lock!)
SELECT id, title, content, version
FROM documents WHERE id = 42;
-- Returns: version = 5

-- Step 2: User edits in the browser (minutes pass...)

-
...