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

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

 

▶ 예제 코드 (SQL)

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