-- 会话1：获取行级锁
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 会话2：尝试更新被锁定的行（会等待）
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

-- 查看当前锁状态
SELECT locktype, relation::regclass, mode, granted, pid 
FROM pg_locks 
WHERE relation = 'accounts'::regclass;

-- 查看当前所有锁
SELECT 
    pl.locktype,
    pl.pid,
    pl.mode,
    pl.granted,
    CASE 
        WHEN pl.locktype = 'relation' THEN rel.relname
        WHEN pl.locktype = 'transactionid' THEN pl.transactionid::text
        ELSE 'N/A'
    END AS object,
    us.usename,
    query_start,
    state_change,
    state,
    query
FROM pg_locks pl
LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid
LEFT JOIN pg_database pd ON (pl.database = pd.oid)
LEFT JOIN pg_class rel ON (pl.relation = rel.oid)
LEFT JOIN pg_user us ON (psa.usename = us.usename)
WHERE pl.pid <> pg_backend_pid()
ORDER BY pl.pid, pl.locktype;

-- 查看等待锁的查询
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 使用并发索引创建（避免长时间锁表）
CREATE INDEX CONCURRENTLY idx_accounts_balance ON accounts(balance);

-- 对于表结构变更，考虑在业务低峰期执行
-- 或者使用在线DDL工具（如pg_repack）

-- 优化事务逻辑，减少热点行持有锁的时间
BEGIN;
-- 快速获取需要的数据
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 在应用层处理业务逻辑
-- 快速提交更新
UPDATE accounts SET balance = new_balance WHERE id = 1;
COMMIT;

-- 或者考虑业务拆分，避免单一热点

-- 在引用表上创建索引，加速外键检查
CREATE INDEX CONCURRENTLY idx_orders_account_id ON orders(account_id);

-- 考虑延迟约束检查
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_accounts 
FOREIGN KEY (account_id) REFERENCES accounts(id) 
DEFERRABLE INITIALLY DEFERRED;

-- 会话1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 会话2  
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;

-- 会话1
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 等待会话2释放锁

-- 会话2
UPDATE accounts SET balance = balance + 200 WHERE id = 1; -- 检测到死锁，会话2被回滚