postgresql事务管理

1、查看数据库当前的进程，看一下有无正在执行的慢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;

2、杀事务

SELECT pg_cancel_backend( pid );

3、查看具体表的sql执行情况

--查询具体表的执行情况
SELECT * FROM pg_stat_activity where query ~ 'dev_pro_data_t212_reading';

4、查询慢查询

--查询慢查询
SELECT  query, calls, total_time, (total_time/calls) as average ,rows, 
        100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent 
FROM    pg_stat_statements 
ORDER   BY average DESC LIMIT 10;

5、查询正在执行的语句

SELECT
    tt.pid,  -- pid
    'select sys_cancel_backend('||tt.pid||');' kill,
    client_addr,
    datname,
    usename user_name, -- 执行的用户
    backend_start,  -- 会话开始时间
    query_start, -- 查询开始时间
    state,
    now() - query_start AS current_query_time, -- 累计执行时间
    now() - backend_start AS current_session_time,
    query
FROM
    PG_STAT_ACTIVITY tt
 WHERE state = 'active' 
  and query_start is not null
ORDER BY current_query_time DESC;

锁表情况

1 查找锁表的pid

select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'lockedtable';

2 查找锁表的语句

select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname =  'lockedtable');

3 查找所有活动的被锁的表

select pid, state, usename, query, query_start 
from pg_stat_activity 
where pid in (
  select pid from pg_locks l 
  join pg_class t on l.relation = t.oid 
  and t.relkind = 'r' 
);

4 解锁

SELECT pg_cancel_backend(pid);

5 批量(未排除自己)

select pg_cancel_backend(pid)
from pg_stat_activity 
where pid in (
  select pid from pg_locks l 
  join pg_class t on l.relation = t.oid 
  and t.relkind = 'r' 
);

6、查看锁冲突信息

-- session 2中查看锁冲突信息
SELECT locktype,pg_locks.pid,virtualtransaction,transactionid,nspname,relname,mode,granted,
case 
WHEN granted='false' then 'get_lock'
WHEN granted='true' then 'wait_lock' END lock_satus,
cast(date_trunc('second',query_start) AS timestamp) AS query_start,query
FROM pg_locks LEFT OUTER
JOIN pg_class ON (pg_locks.relation = pg_class.oid) LEFT OUTER
JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity
WHERE NOT pg_locks.pid=pg_backend_pid() AND pg_locks.pid=pg_stat_activity.pid AND transactionid is NOT null
ORDER BY  query_start;