不灭的焱

查询PostgreSQL中导致锁表的SQL记录，可以通过以下方法实现：

1. 查看当前所有锁信息

SELECT 
    pg_stat_activity.pid,
    pg_stat_activity.usename,
    pg_stat_activity.application_name,
    pg_stat_activity.client_addr,
    pg_stat_activity.query_start,
    pg_stat_activity.state,
    pg_stat_activity.query,
    pg_locks.mode,
    pg_locks.locktype,
    pg_locks.granted
FROM pg_stat_activity
JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
WHERE pg_stat_activity.pid <> pg_backend_pid()
  AND pg_locks.mode LIKE '%ExclusiveLock%'
ORDER BY pg_stat_activity.query_start;

2. 查看阻塞的锁（重点查看锁等待）

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,
    blocked_activity.application_name AS blocked_application,
    blocking_activity.application_name AS blocking_application
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;

3. 查看表级别的锁

SELECT 
    pg_stat_activity.pid,
    pg_stat_activity.usename,
    pg_class.relname AS table_name,
    pg_locks.mode,
    pg_locks.granted,
    pg_stat_activity.query,
    pg_stat_activity.query_start,
    age(now(), pg_stat_activity.query_start) AS query_age
FROM pg_stat_activity
JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_locks.mode IN ('AccessExclusiveLock', 'ExclusiveLock', 'ShareRowExclusiveLock')
  AND pg_stat_activity.pid <> pg_backend_pid()
ORDER BY query_age DESC;

4. 使用pg_stat_activity视图查看长时间运行的事务

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    query_start,
    age(now(), query_start) AS duration,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state <> 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

5. 快速诊断锁问题的查询

-- 查看所有持有锁的会话
SELECT 
    locktype,
    relation::regclass,
    mode,
    transactionid,
    virtualtransaction,
    pid,
    granted
FROM pg_locks
WHERE NOT granted
ORDER BY pid;

6. 使用pg_activity工具（命令行）

# 安装pg_activity
pip install pg_activity

# 运行（类似top命令）
pg_activity --help

7. 【重点】实用脚本：生成kill语句

-- 生成杀死阻塞进程的SQL语句
SELECT 
    'SELECT pg_terminate_backend(' || blocking_pid || ');' AS kill_command,
    blocking_pid,
    blocking_query
FROM (
    -- 使用上面第2个查询
    SELECT DISTINCT blocking_locks.pid AS blocking_pid,
           blocking_activity.query AS blocking_query
    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
) AS blocking_processes;

注意事项：

1. 需要连接权限（通常需要超级用户或pg_read_all_stats权限）
2. 在查询时避免对系统表加锁，可以在副本上执行
3. 使用pg_terminate_backend()终止进程要谨慎
4. 长时间的事务通常更容易导致锁问题

常见锁类型：

• AccessExclusiveLock：最严格的锁（如ALTER TABLE, DROP TABLE, VACUUM FULL）
• ExclusiveLock：排他锁
• ShareLock：共享锁
• AccessShareLock：访问共享锁（如SELECT）

建议先使用第2个查询找出阻塞关系，再针对性解决问题。