#PostgreSQL Locking Mechanism

-- Lock entire table
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;

-- Different lock modes
LOCK TABLE users IN SHARE MODE;
LOCK TABLE users IN EXCLUSIVE MODE;
LOCK TABLE users IN ROW EXCLUSIVE MODE;

-- With NOWAIT option (error immediately if lock unavailable)
LOCK TABLE users IN ACCESS EXCLUSIVE MODE NOWAIT;

-- SELECT FOR UPDATE (exclusive lock)
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- SELECT FOR SHARE (shared lock)
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- NOWAIT option
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED (skip locked rows)
SELECT * FROM users WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- Acquire lock
SELECT pg_advisory_lock(12345);

-- Try to acquire lock (non-blocking)
SELECT pg_try_advisory_lock(12345);

-- Release lock
SELECT pg_advisory_unlock(12345);

-- Release all session locks
SELECT pg_advisory_unlock_all();

-- Acquire transaction-level lock (auto-released at transaction end)
SELECT pg_advisory_xact_lock(12345);

-- Try to acquire transaction-level lock
SELECT pg_try_advisory_xact_lock(12345);

-- Use advisory lock to prevent concurrent task processing
CREATE OR REPLACE FUNCTION process_task(task_id BIGINT)
RETURNS BOOLEAN AS $$
BEGIN
    -- Try to acquire lock
    IF NOT pg_try_advisory_xact_lock(task_id) THEN
        RETURN FALSE;  -- Task is being processed by another process
    END IF;

    -- Process task
    UPDATE tasks SET status = 'processing' WHERE id = task_id;
    -- ... other processing logic

    UPDATE tasks SET status = 'completed' WHERE id = task_id;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Waiting for Transaction 2 to release lock on id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction 2 (simultaneously)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- Waiting for Transaction 1 to release lock on id=1
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Deadlock!

-- Method 1: Access resources in fixed order
BEGIN;
-- Always lock lower ID account first
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Method 2: Use SELECT FOR UPDATE to pre-lock
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- View deadlock timeout setting
SHOW deadlock_timeout;

-- Set deadlock detection timeout (default 1 second)
SET deadlock_timeout = '2s';

-- View all locks
SELECT * FROM pg_locks;

-- View locked tables
SELECT
    l.locktype,
    l.mode,
    l.granted,
    c.relname as table_name,
    a.usename,
    a.query,
    a.state
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE c.relkind = 'r';

-- View processes waiting for locks
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked
    ON blocked_locks.pid = blocked.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking
    ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

-- Cancel query
SELECT pg_cancel_backend(pid);

-- Terminate connection
SELECT pg_terminate_backend(pid);

-- Set lock wait timeout
SET lock_timeout = '10s';

-- Set statement timeout
SET statement_timeout = '30s';

-- View current settings
SHOW lock_timeout;

BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Other transactions cannot modify this row
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- Use version number to implement optimistic locking
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;

-- Check if update succeeded
-- If affected rows = 0, version conflict, need to retry