PostgreSQL 查看是否存在死锁

素颜马尾好姑娘i 2022-09-12 08:53 373阅读 0赞

## 查看死锁的sql ##

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;

或者使用这个sql

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;

## 按建立连接的时间排序的正在运行查询的列表 ##

SELECT * FROM pg_stat_activity ORDER BY backend_start;

## 统计idle，active，null的运行的sql的数量 ##

select state, wait_event, wait_event_type, count(*) from pg_stat_activity group by 1,2,3 order by wait_event;

## 终止pid会话 ##

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_20_color_FFFFFF_t_70_g_se_x_16]  
要停止正在执行查询的进程，请从另一个会话中调用以下查询

select pg_terminate_backend(25756)

## 确认当前的连接用户和对应的连接机器 ##

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_12_color_FFFFFF_t_70_g_se_x_16]

## 查看sql使用情况 ##

SELECT datname,usename,query FROM pg_stat_activity ;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_19_color_FFFFFF_t_70_g_se_x_16]

## 只查看当前正在运行的sql ##

SELECT datname,usename,query
       FROM pg_stat_activity
       WHERE state != 'idle'

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_18_color_FFFFFF_t_70_g_se_x_16]

## 查看耗时较长的sql ##

select current_timestamp - query_start as runtime, datname, usename, query
        from pg_stat_activity
        where state != 'idle'
        order by 1 desc;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]

## 查看数据库的最大链接数 ##

show max_connections;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_9_color_FFFFFF_t_70_g_se_x_16]

## 查看当前使用的连接数 ##

SELECT COUNT(*) from pg_stat_activity;

![在这里插入图片描述][52c8a60584a549deaa86a8a26081230e.png]

## 查看prepostsql的最大链接数 ##

select min_val, max_val from pg_settings where name='max_connections';

![在这里插入图片描述][2e070bce023a4daba3b1f6a19ab1292f.png]

## 查看正在运行的sql，并且带上运行时长 ##

SELECT 
        procpid, 
        start, 
        now() - start AS lap, 
        current_query 
    FROM 
        (SELECT 
            backendid, 
            pg_stat_get_backend_pid(S.backendid) AS procpid, 
            pg_stat_get_backend_activity_start(S.backendid) AS start, 
           pg_stat_get_backend_activity(S.backendid) AS current_query 
        FROM 
            (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
        ) AS S 
    WHERE 
       current_query <> '<IDLE>' 
    ORDER BY 
       lap DESC;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/654e9082787247d1af9028c5564b323f.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_12_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/c85cc2caad324178b92619dd1e82d5e1.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_19_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/68b06b21f53b47908024e29bda5b6613.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/2bb0550cd8514fda8a7f89b7b5a9fb91.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/8c06d2bd4baf4338b7787cb6ef0bc467.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_9_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/46c7021f65874b13bd2a04033a59ee39.png
[52c8a60584a549deaa86a8a26081230e.png]: /images/20220828/c96555c9ecd140a3bdad91d9cf4bd54d.png
[2e070bce023a4daba3b1f6a19ab1292f.png]: /images/20220828/8940966933bb4d4d950eea371a74e56e.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATW9ua2V5S2luZ19zdW55dWh1YQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/4e477a385f484892b66a08a4cb465006.png