-- 引用[2][3]優(yōu)化版：查詢被鎖表及對應(yīng)進(jìn)程
SELECT 
  c.relname AS 表名,
  l.mode AS 鎖模式,
  l.pid AS 進(jìn)程ID,
  a.query AS 阻塞語句,
  a.state AS 狀態(tài)
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted 
  AND c.relkind = 'r' 
  AND c.relname = 'your_table';  -- 替換具體表名

表名 | 鎖模式          | 進(jìn)程ID | 阻塞語句              | 狀態(tài)
-----+-----------------+--------+----------------------+---------
t    | AccessShareLock | 12345  | alter table t add... | idle in trans

-- 引用[4]增強(qiáng)版：查看阻塞關(guān)系鏈
SELECT 
  blocked.pid AS 被阻塞進(jìn)程,
  blocked.query AS 被阻塞語句,
  blocking.pid AS 阻塞源進(jìn)程,
  blocking.query AS 阻塞源語句
FROM pg_stat_activity blocked
JOIN pg_locks l1 ON l1.pid = blocked.pid
JOIN pg_locks l2 ON l2.locktype = l1.locktype 
  AND l2.DATABASE IS NOT DISTINCT FROM l1.DATABASE 
  AND l2.relation IS NOT DISTINCT FROM l1.relation 
  AND l2.page IS NOT DISTINCT FROM l1.page 
  AND l2.tuple IS NOT DISTINCT FROM l1.tuple 
  AND l2.virtualxid IS NOT DISTINCT FROM l1.virtualxid 
  AND l2.transactionid IS NOT DISTINCT FROM l1.transactionid 
  AND l2.classid IS NOT DISTINCT FROM l1.classid 
  AND l2.objid IS NOT DISTINCT FROM l1.objid 
  AND l2.objsubid IS NOT DISTINCT FROM l1.objsubid 
  AND l2.pid != l1.pid
JOIN pg_stat_activity blocking ON blocking.pid = l2.pid;

-- 終止特定進(jìn)程（需superuser權(quán)限）
SELECT pg_terminate_backend(pid);  -- 替換實(shí)際進(jìn)程ID

-- 批量終止所有鎖等待進(jìn)程
WITH deadlock_pids AS (
  SELECT pid FROM pg_stat_activity 
  WHERE wait_event_type = 'Lock' 
    AND state = 'active'
)
SELECT pg_terminate_backend(pid) FROM deadlock_pids;

-- 會話級設(shè)置（引用[4]延伸）
SET lock_timeout = '5s';  -- 單個查詢最長等待時間

-- 事務(wù)級設(shè)置
BEGIN;
SET LOCAL lock_timeout = '3s';
UPDATE table SET ...;
COMMIT;

-- 生成鎖兼容性矩陣
SELECT 
  l1.mode AS held_mode,
  l2.mode AS requested_mode,
  NOT pg_lock_conflicts(l1.mode, l2.mode) AS compatible
FROM (VALUES ('AccessShareLock'),('RowShareLock'),...) l1(mode)
CROSS JOIN (VALUES ('AccessShareLock'),('RowShareLock'),...) l2(mode);

SELECT 
  query,
  calls,
  total_time,
  rows
FROM pg_stat_statements 
WHERE query LIKE '%FOR UPDATE%' 
ORDER BY total_time DESC 
LIMIT 10;

-- 優(yōu)先使用行級鎖
SELECT * FROM table WHERE id = 1 FOR UPDATE;

-- 大范圍更新時使用SKIP LOCKED
UPDATE table SET status = 'processed' 
WHERE status = 'pending' 
LIMIT 100 
FOR UPDATE SKIP LOCKED;

# 監(jiān)控配置文件postgresql.conf
deadlock_timeout = 1s          # 死鎖檢測間隔
log_lock_waits = on            # 記錄長鎖等待
log_min_duration_statement = 1s # 記錄慢查詢

SELECT * FROM pg_locks;

SELECT 
    blocking.pid AS blocking_pid,
    blocked.pid AS blocked_pid,
    blocking.usename AS blocking_user,
    blocked.usename AS blocked_user,
    blocking.query AS blocking_query,
    blocked.query AS blocked_query
FROM 
    pg_locks blocked
JOIN 
    pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN 
    pg_locks blocking ON blocked.locktype = blocking.locktype
    AND blocked.database = blocking.database
    AND blocked.relation = blocking.relation
    AND blocked.page = blocking.page
    AND blocked.tuple = blocking.tuple
    AND blocked.virtualxid = blocking.virtualxid
    AND blocked.transactionid = blocking.transactionid
    AND blocked.classid = blocking.classid
    AND blocked.objid = blocking.objid
    AND blocked.objsubid = blocking.objsubid
    AND blocked.pid != blocking.pid
JOIN 
    pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE 
    NOT blocked.granted;

SELECT 
    pid,
    usename,
    query_start,
    now() - query_start AS duration,
    query
FROM 
    pg_stat_activity
WHERE 
    state != 'idle'
ORDER BY 
    query_start;

SELECT pg_cancel_backend(pid);

SELECT pg_terminate_backend(pid);

SET statement_timeout TO 5000; -- 設(shè)置語句超時為5秒
SET lock_timeout TO 1000; -- 設(shè)置鎖超時為1秒

SHOW log_lock_waits; -- 查看是否啟用了鎖等待日志
SET log_lock_waits = on; -- 啟用鎖等待日志