Postgres에서 잠금을 유지하는 쿼리를 탐지하는 방법은 무엇입니까?

Postgres에서 잠금을 유지하는 쿼리를 탐지하는 방법은 무엇입니까?

저는 포스트그레스의 상호 잠금을 지속적으로 추적하고 싶습니다.

잠금 모니터링 문서를 발견하고 다음 쿼리를 실행하려고 했습니다.

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

유감스럽게도 비어 있지 않은 결과 집합은 반환되지 않습니다.지정된 쿼리를 다음 양식으로 단순화하면 다음과 같습니다.

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
WHERE NOT bl.granted;

그런 다음 잠금을 획득하기 위해 대기 중인 쿼리를 반환합니다.하지만 차단된 쿼리와 차단된 쿼리를 모두 반환할 수 있도록 변경할 수 없습니다.

아이디어 있어요?

9.6 이후로 이 기능이 도입되었기 때문에 훨씬 더 쉽습니다.pg_blocking_pids()다른 세션을 차단하는 세션을 찾습니다.

따라서 다음과 같은 것을 사용할 수 있습니다.

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Postgres의 쿼리 잠금에 대한 이 훌륭한 기사에서 다음 쿼리에서 차단된 쿼리 및 차단기 쿼리와 해당 정보를 얻을 수 있습니다.

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

SELECT * from lock_monitor;

쿼리가 길지만 유용하기 때문에 문서 작성자는 쿼리 사용을 단순화하기 위해 쿼리에 대한 보기를 만들었습니다.

a_horse_with_no_name의 응답을 수정하면 차단된 세션뿐만 아니라 차단된 세션의 마지막(또는 현재) 쿼리도 제공됩니다.

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

이를 통해 블록이 이전 쿼리에서 발생한 경우에도 어떤 작업이 서로 간섭하고 있는지 파악할 수 있으므로 세션 하나를 종료했을 때의 영향을 이해하고 향후 차단을 방지할 수 있는 방법을 파악할 수 있습니다.

차단된 모든 쿼리를 표시하는 방법입니다.

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

다음 명령을 사용하여 차단된 쿼리를 종료할 수 있습니다.

SELECT pg_cancel_backend(a.pid), pg_terminate_backend(a.pid);

이를 사용하여 차단된 모든 쿼리를 종료할 수 있습니다.

SELECT pg_cancel_backend(a.pid), pg_terminate_backend(a.pid)
FROM( select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0) a

Postgres에는 SQL 테이블을 통해 노출되는 매우 풍부한 시스템 카탈로그가 있습니다.PG의 통계 수집기는 서버 활동에 대한 정보 수집 및 보고를 지원하는 하위 시스템입니다.

" " " " " " " " " " " " " " " " " " " 를 .pg_stat_activity.

select pg_blocking_pids(pid) as blocked_by
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

차단 PID에 해당하는 쿼리를 가져오려면 자체 가입하거나 하위 쿼리의 where 절로 사용할 수 있습니다.

SELECT query
FROM pg_stat_activity
WHERE pid IN (select unnest(pg_blocking_pids(pid)) as blocked_by from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0);

고참 이후: 이후pg_blocking_pids(pid)하므로 []를 반환해야 .unnest당신이 그것을 사용하기 전에.WHERE pid IN절

느린 쿼리를 찾는 것은 때때로 지루할 수 있으므로 인내심을 가져야 합니다.즐거운 사냥 되세요.

9.6이 9pg_blocking_pidsfunction 다음 쿼리를 사용하여 차단된 쿼리와 차단된 쿼리를 찾을 수 있습니다.

SELECT w.query                          AS waiting_query,
       w.pid                            AS waiting_pid,
       w.usename                        AS waiting_user,
       now() - w.query_start            AS waiting_duration,
       l.query                          AS locking_query,
       l.pid                            AS locking_pid,
       l.usename                        AS locking_user,
       t.schemaname || '.' || t.relname AS tablename,
       now() - l.query_start            AS locking_duration
FROM pg_stat_activity w
         JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
         JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
         JOIN pg_stat_activity l ON l2.pid = l.pid
         JOIN pg_stat_user_tables t ON l1.relation = t.relid
WHERE w.waiting;

제가 여기서 종종 누락되는 한 가지는 행 잠금을 조회하는 기능입니다.적어도 내가 작업한 더 큰 데이터베이스에서는 행 잠금이 pg_locks에 표시되지 않습니다(만약 그렇다면, pg_locks는 훨씬 더 크고 해당 보기에서 잠긴 행을 제대로 표시할 실제 데이터 유형이 없습니다).

이에 대한 간단한 해결책이 있는지는 모르겠지만 보통 제가 하는 일은 잠금이 대기 중인 테이블을 보고 xmax가 거기에 있는 트랜잭션 ID보다 작은 행을 검색하는 것입니다.이를 통해 일반적으로 시작할 수 있는 기회가 주어지지만, 이는 약간 실제적이고 자동화에 친숙하지 않습니다.

는 해당 테이블의 행에 커밋되지 않은 쓰기를 보여줍니다.커밋된 행은 현재 스냅샷에 표시되지 않습니다.하지만 큰 테이블의 경우, 그것은 고통입니다.

다른 사람들은 이미 당신의 질문에 대답했지만, 저는 확실한 상황이 있었습니다.서로 차단하는 쿼리가 많아서 차단된 세션별로 메인 차단기 세션을 찾고자 했습니다.첫 번째 예제 세션 5는 세션 4에 의해 차단되었고 3과 2는 세션 1을 기다리는 동안 4는 3과 2를 기다립니다.

5->4->{3,2}->1

이 경우 세션 1이 진짜 문제입니다. 삭제되면 다른 사용자가 알아서 처리합니다.

그래서 저는 다음과 같은 결과를 보여줄 쿼리를 원했습니다.

차단된 pid	차단기
5	1
4	1
3	1
2	1

따라서 체인으로 잠긴 세션이 있는 경우 이 쿼리는 차단된 각 세션의 기본 차단기 세션을 표시합니다.

;with recursive 
    find_the_source_blocker as (
        select  pid
               ,pid as blocker_id
        from pg_stat_activity pa
        where pa.state<>'idle'
              and array_length(pg_blocking_pids(pa.pid), 1) is null

        union all

        select              
                t.pid  as  pid
               ,f.blocker_id as blocker_id
        from find_the_source_blocker f 
        join (  SELECT
                    act.pid,
                    blc.pid AS blocker_id
                FROM pg_stat_activity AS act
                LEFT JOIN pg_stat_activity AS blc ON blc.pid = ANY(pg_blocking_pids(act.pid))
                where act.state<>'idle') t on f.pid=t.blocker_id
        )
    
select distinct 
       s.pid
      ,s.blocker_id
      ,pb.usename       as blocker_user
      ,pb.query_start   as blocker_start
      ,pb.query         as blocker_query
      ,pt.query_start   as trans_start
      ,pt.query         as trans_query
from find_the_source_blocker s
join pg_stat_activity pb on s.blocker_id=pb.pid
join pg_stat_activity pt on s.pid=pt.pid
where s.pid<>s.blocker_id

언급URL : https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres