[SQL] 어떻게 포스트 그레스의 잠금을 보유 쿼리를 감지?

어떻게 포스트 그레스의 잠금을 보유 쿼리를 감지?

나는 끊임없이 포스트 그레스 상호 잠금을 추적합니다.

나는 기사를 모니터링 잠금을 가로 질러 와서 다음 쿼리를 실행하려고 :

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;

다음은 잠금을 획득하기 위해 대기중인 쿼리를 반환합니다. 그러나 나는 그것이 모두 차단 및 차단 쿼리를 반환 할 수 있도록 변경을 관리 할 수 ​​없습니다.

어떤 아이디어?

해결법

1. ==============================

   1.9.6 이후이는 () 다른 세션을 차단하는 세션을 찾을 수있는 기능 pg_blocking_pids 도입으로 훨씬 쉽습니다.

   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;
   

2. ==============================

   2.포스트 그레스 쿼리의 잠금 장치에이 우수한 기사에서, 하나는 쿼리 및 차단 쿼리 및 다음 쿼리에서 자신의 정보를 차단받을 수 있습니다.

   포스트 그레스 쿼리의 잠금 장치에이 우수한 기사에서, 하나는 쿼리 및 차단 쿼리 및 다음 쿼리에서 자신의 정보를 차단받을 수 있습니다.

   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;
   

   쿼리가 긴하지만 유용으로 그것의 사용을 단순화하기 위해, 기사 작성자는 뷰를 만들었습니다.

3. ==============================

   3.a_horse_with_no_name의 대답이 수정하면 바로 차단 세션에 추가하여 차단 쿼리를 줄 것이다 :

   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));
   

4. ==============================

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

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

   지금 당신은 단순히 쿼리 pg_stat_activity 수있는 차단 PID를 알아낼 수 있습니다.

   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)의 정수를 반환하기 때문에, 당신은 WHERE PID IN 절에서 사용하기 전에 당신은 unnest 필요하므로.

   느린 쿼리를 사냥하는 것은 지루한 때때로, 그래서 인내심을 가지고 할 수 있습니다. 즐거운 사냥.

5. ==============================

   5.종종 이러한에서 누락 내가 발견 한 것은 행 잠금을 조회 할 수있는 기능입니다. 나는에 일한 큰 데이터베이스에 적어도, 행 잠금이 (가 있다면, pg_locks 훨씬, 훨씬 더 큰 것이 그보기에 잠긴 행 제대로을 보여주기 위해 실제 데이터 형식이 아닌) pg_locks에 표시되지 않습니다.

   종종 이러한에서 누락 내가 발견 한 것은 행 잠금을 조회 할 수있는 기능입니다. 나는에 일한 큰 데이터베이스에 적어도, 행 잠금이 (가 있다면, pg_locks 훨씬, 훨씬 더 큰 것이 그보기에 잠긴 행 제대로을 보여주기 위해 실제 데이터 형식이 아닌) pg_locks에 표시되지 않습니다.

   나는이에 대한 간단한 해결책이 있다는 것을 알고 있지만 일반적으로 내가 뭘 잠금이 기다리고 테이블 봐하고 XMAX 덜이 트랜잭션 ID 존재하는 이상을 어디에 행을 검색하지 않습니다. 그것은 일반적으로 나에게 시작하는 장소를 제공하지만, 그것은 비트에 손이 아니라 친절은 자동화입니다.

   그 쇼 당신에게 해당 테이블에 행에 커밋되지 않은 쓰기를합니다. 최선을 다하고되면, 행은 현재 스냅 샷에 표시되지 않습니다. 그러나 큰 테이블, 그 고통이다.

from https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres by cc-by-sa and MIT license