查看数据库锁

[Ch-msm]

SELECT
    blocking_activity.datname as "数据库",
    blocking_activity.application_name as "持锁会话程序名",
    blocking_activity.client_addr as "持锁会话地址",
    now()-blocking_activity.query_start as "阻塞时长(s)",
    blocked_locks.pid AS "阻塞会话ID",
    blocked_activity.usename AS "被阻塞用户",
    blocking_locks.pid AS "持锁会话ID",
    blocking_activity.usename AS "持锁用户",
    blocked_activity. QUERY AS "被锁SQL",
    blocking_activity. QUERY AS "持锁SQL"
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;

select pid,now()-query_start as "执行时间",wait_event_type,wait_event,query from pg_stat_activity;

select * from pg_locks;

select pg_terminate_backend(31365);