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

 

    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;