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

 

    BLOCKED_LOCKS.PID         AS BLOCKED_PID

   ,BLOCKED_ACTIVITY.USENAME  AS BLOCKED_USER

   ,BLOCKING_LOCKS.PID        AS BLOCKING_PID

   ,BLOCKING_ACTIVITY.USENAME AS BLOCKING_USER

   ,BLOCKED_ACTIVITY.QUERY    AS BLOCKED_STATEMENT

   ,BLOCKING_ACTIVITY.QUERY   AS CURRENT_STATEMENT_IN_BLOCKING_PROCESS

FROM PG_CATALOG.PG_LOCKS          BLOCKED_LOCKS

JOIN PG_CATALOG.PG_STAT_ACTIVITY  BLOCKED_ACTIVITY ON  BLOCKED_ACTIVITY.PID    = BLOCKED_LOCKS.PID

JOIN PG_CATALOG.PG_LOCKS          BLOCKING_LOCKS   ON  BLOCKING_LOCKS.LOCKTYPE = BLOCKED_LOCKS.LOCKTYPE

                                                   AND BLOCKING_LOCKS.DATABASE      IS NOT DISTINCT FROM BLOCKED_LOCKS.DATABASE

                                                   AND BLOCKING_LOCKS.RELATION      IS NOT DISTINCT FROM BLOCKED_LOCKS.RELATION

                                                   AND BLOCKING_LOCKS.PAGE          IS NOT DISTINCT FROM BLOCKED_LOCKS.PAGE

                                                   AND BLOCKING_LOCKS.TUPLE         IS NOT DISTINCT FROM BLOCKED_LOCKS.TUPLE

                                                   AND BLOCKING_LOCKS.VIRTUALXID    IS NOT DISTINCT FROM BLOCKED_LOCKS.VIRTUALXID

                                                   AND BLOCKING_LOCKS.TRANSACTIONID IS NOT DISTINCT FROM BLOCKED_LOCKS.TRANSACTIONID

                                                   AND BLOCKING_LOCKS.CLASSID       IS NOT DISTINCT FROM BLOCKED_LOCKS.CLASSID

                                                   AND BLOCKING_LOCKS.OBJID         IS NOT DISTINCT FROM BLOCKED_LOCKS.OBJID

                                                   AND BLOCKING_LOCKS.OBJSUBID      IS NOT DISTINCT FROM BLOCKED_LOCKS.OBJSUBID

                                                   AND BLOCKING_LOCKS.PID    != BLOCKED_LOCKS.PID

JOIN PG_CATALOG.PG_STAT_ACTIVITY BLOCKING_ACTIVITY ON  BLOCKING_ACTIVITY.PID =  BLOCKING_LOCKS.PID

WHERE NOT BLOCKED_LOCKS.GRANTED;