[DATABASE/POSTGRESQL] 잠금 프로세스 조회하기

■ 잠금 프로세스를 조회하는 방법을 보여준다.

 

▶ 예제 코드 (SQL)

SELECT
    WAITING.LOCKTYPE           AS WAITING_LOCKTYPE
   ,WAITING.RELATION::REGCLASS AS WAITING_TABLE
   ,WAITING_STM.QUERY          AS WAITING_QUERY
   ,WAITING.MODE               AS WAITING_MODE
   ,WAITING.PID                AS WAITING_PID
   ,OTHER.LOCKTYPE             AS OTHER_LOCKTYPE
   ,OTHER.RELATION::REGCLASS   AS OTHER_TABLE
   ,OTHER_STM.QUERY            AS OTHER_QUERY
   ,OTHER.MODE                 AS OTHER_MODE
   ,OTHER.PID                  AS OTHER_PID
   ,OTHER.GRANTED              AS OTHER_GRANTED
FROM PG_CATALOG.PG_LOCKS         AS WAITING
JOIN PG_CATALOG.PG_STAT_ACTIVITY AS WAITING_STM ON  WAITING_STM.PID       = WAITING.PID
JOIN PG_CATALOG.PG_LOCKS         AS OTHER       ON  WAITING."database"    = OTHER."database"
                                                AND WAITING.RELATION      = OTHER.RELATION
                                                OR  WAITING.TRANSACTIONID = OTHER.TRANSACTIONID
JOIN PG_CATALOG.PG_STAT_ACTIVITY AS OTHER_STM   ON  OTHER_STM.PID         = OTHER.PID
WHERE NOT WAITING.GRANTED
AND   WAITING.PID <> OTHER.PID;