PG死锁查询与解决

发表于 本文字数： 1.6k 阅读时长 ≈ 1 分钟 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select w1.pid as 等待进程,
  w1.mode as 等待锁模式,
  w2.usename as 等待用户,
  w2.query as 等待会话,
  b1.pid as 锁的进程,
  b1.mode 锁的锁模式,
  b2.usename as 锁的用户,
  b2.query as 锁的会话,
  b2.application_name 锁的应用,
  b2.client_addr 锁的IP地址,
  b2.query_start 锁的语句执行时间
from pg_locks w1
join pg_stat_activity w2 on w1.pid=w2.pid
join pg_locks b1 on w1.transactionid=b1.transactionid and w1.pid!=b1.pid
join pg_stat_activity b2 on b1.pid=b2.pid
where not w1.granted;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid='XXX';
SELECT pg_terminate_backend(PID);
SELECT pg_terminate_backend(6289);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SELECT
  pid,
  datname,
  usename,
  client_addr,
  application_name,
  STATE,
  backend_start,
  xact_start,
  xact_stay,
  query_start,
  query_stay,
  REPLACE ( query, chr( 10 ), ' ' ) AS query 
FROM
  (
  SELECT
    pgsa.pid AS pid,
    pgsa.datname AS datname,
    pgsa.usename AS usename,
    pgsa.client_addr client_addr,
    pgsa.application_name AS application_name,
    pgsa.STATE AS STATE,
    pgsa.backend_start AS backend_start,
    pgsa.xact_start AS xact_start,
    EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay,
    pgsa.query_start AS query_start,
    EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay,
    pgsa.query AS query 
  FROM
    pg_stat_activity AS pgsa 
  WHERE
    pgsa.STATE != 'idle' 
    AND pgsa.STATE != 'idle in transaction' 
    AND pgsa.STATE != 'idle in transaction (aborted)' 
  ) idleconnections 
ORDER BY
  query_stay DESC;